Office - messages #15891

Office

Messages

Prospero Blocks

Hot Searches

NASA

Chat Center

Board Folders

	MS Office - Ge... 4363 msgs in 880 dscns Latest: Nov-24
	Outlook 10271 msgs in 1964 dscns Latest: 11:05 AM
	Word & Word VBA 9371 msgs in 1768 dscns Latest: 7:55 AM
	Excel & Excel VBA 12863 msgs in 2269 dscns Latest: 11:14 AM
	Powerpoint 750 msgs in 196 dscns Latest: Sep-18
	Access & Acces... 5436 msgs in 1214 dscns Latest: Nov-6
	Graphic Tools 130 msgs in 34 dscns Latest: Aug-25
	Publisher 254 msgs in 47 dscns Latest: Oct-18
	Works Suite 360 msgs in 73 dscns Latest: 5/26/09
	Money & MSTaxS... 97 msgs in 27 dscns Latest: 12/25/08
	MS Maps & Trips 78 msgs in 15 dscns Latest: Oct-23
	FrontPage 1259 msgs in 224 dscns Latest: Oct-15
	Other Office P... 325 msgs in 82 dscns Latest: Oct-29
	OneNote 144 msgs in 19 dscns Latest: Nov-11

Message Area

To list

New

Access & Access VBA

Select as category

Print Discussion

#1 of 4 Posted Oct-31 3:47 PM

From Carl Seiler/TX Posts 109 Last Nov-23
To All [Msg # 15891.1 ]

Using Access 2007 (and 2002) as front-end for MS SQL Server database. This should be simple, but for some reason, I can't quite get it right. We have a stored procedure that returns a table of 99 columns. One of the columns has 12 different possible values that fit into 4 categories. I need to do a report that groups them by these 4 categories, but these four categories don't appear in the results table. I can't edit the stored procedure, so need to categorize the rows into each of the four categories based on the field. Originally, I was going to do this using a CASE, but I find that Access apparently doesn't have a CASE in its SQL, so then I think I'll do a union, and have come up with:

SELECT *, "Type 1" AS category_type
FROM dbo_spresults
WHERE category_id = "CHS00001 USA1" or category_id ="I0213" or category_id ="MASI CHS 0001 US 09"
UNION
SELECT *, "Type 2" AS category_type
FROM dbo_spresults
WHERE category_id = "CHS00003 USA1" or category_id = "82688" or category_id ="MASI CHS 0003 US 09"
UNION ALL
SELECT *, "Type 3" AS category_type
FROM dbo_spresults
WHERE category_id ="CHS00008 USA1" or category_id = "MASI CHS 0008 US 09" or category_id ="I0213-01"
UNION ALL
SELECT *, "Type 4" AS category_type
FROM dbo_spresults
WHERE category_id ="CHS00004 USA1" or category_id = "MASI CHS 0004 US 09" or category_id ="87470";

But when I run that query, I get a "Too many fields" error. Grr. What I find is that tables are limited to 256 fields, and even though mine only has 99, as soon as I add more than two of the categories above, I run into the error. That is, I can do Type 1 UNION Type 2, but as soon as I add the UNION with Type 3, I get the error. I'm guessing that there's a temporary table created that exceeds 256, and even though my end result shouldn't exceed it, it doesn't like it. So, I'm going to try and trim out some of the columns I don't need, but that's going to be difficult. Is there an easier way. Do I need to do this with nested Iif's in the SELECT? What about other ways?

Carl

Options

#2 of 4 Posted Oct-31 5:13 PM

From Carl Seiler/TX Posts 109 Last Nov-23
To All [Msg # 15891.2 15891.1 ]

OK, I did it with nested Iif's and ORs ala:

SELECT *, (Iif(category_id = "CHS00001 USA1" or category_id ="I0213" or category_id ="MASI CHS 0001 US 09","Type1",
               Iif(category_id = "CHS00003 USA1" or category_id = "82688" or category_id ="MASI CHS 0003 US 09","Type 2",
                   Iif(category_id ="CHS00008 USA1" or category_id = "MASI CHS 0008 US 09" or category_id ="I0213-01","Type 3",
                     Iif(category_id ="CHS00004 USA1" or category_id = "MASI CHS 0004 US 09" or category_id ="87470","Type 4","Unknown")))))
AS category_type
FROM dbo_spresults;

Seems to be working so far. A little tough to read though. My indentation here is an attempt to clear that up a bit. However, if there was some other recommended or more "obvious" way of doing this, I'd appreciate it.

Options

#3 of 4 Posted Nov-1 3:40 AM

From Bill Manville Posts 4156 Last 5:35 AM
To Carl Seiler/TX [Msg # 15891.3 15891.2 ]

Where is this query being executed? Since you are using IIf successfully I guess it's at the client.
I would use a local translation table MyCat containing Category_ID and Category_Type:

SELECT dbo_spresults.*, MyCat.Category_Type FROM dbo_spresults INNER JOIN MyCat ON dbo_spResults.Category_ID=MyCat.Category_Type.

Bill Manville
Excel MVP/TA
Oxford, England

Options