crosstab query vs. subqueries (2000)

Topic

New Reply

I have successfully created a cross tab query. My problem is that I’d like the results to include column headings for all caterories, even if the cross tab query finds no items for that category.

Table 1 – ORDERS (orderID, quantity, itemID)
Table 2 – ITEMS (itemID, itemName, itemCategory)

The cross tab query I’ve created gives me total number of items BY CATEGORY, but if there are no items for a particular category, I don’t get a column heading. This makes sense to me, but the end users want the same look to the query results each time it is run.

My LONG and AWFUL workaround was to create a query for EACH category, and then one MASTER query that pulls in each of these little queries. It works.

I would prefer ONE query, that returns the sum of quanity for each itemCATEGORY. I’ve tried to use a subquery on the criteria line for each category (I repeat quantity in the query for each category, and then under criteria write a SELECT… WHERE type statement), but I either get repeated lines (Cartesian) or nothing. Or, I get an error message – “This subquery will only return ONE RECORD” (which is FINE WITH ME, but it then doesn’t display the results…)

I hope I have explained the problem clearly. THANK YOU.

Reply | Quote

Replies

David,

In your query’s design view, double-click the join line between the ORDERS table and the ITEMS table. Then, select either 2 or 3 and click OK. The idea is to have the arrow point from the ITEMS table to the ORDERS table. Run the query. You should see each itemCategory now (if it’s in the output section of the query grid).

HTH,

Tom

Reply | Quote

You could do this with a simple query and grouping.
Make a column for each category.
lets say you have two categories, AAA and BBB
As field enter the following expression:
AAA : iif([ItemCategory] = “AAA”, quantity,0)
BBB : iif([ItemCategory] = “BBB”, quantity,0)

You can then add ordersID as first column and eventually use Group By to have sum or count

Reply | Quote

What you need is columnheadings for your crosstab query. If you right click the gray area between the tables in the upper part of the query grid, you’ll get the properties dialog for the query itself. The second item in that dialog is Column Headings. You can type in a list of column headings there in the order you want to see them, separated by commas. After that, those column headings will always appear even if there is no data for that column. Unfortunately, the column headings won’t expand automatically, so if new categories get added, they won’t show up in your crosstab query.

Reply | Quote

That did the trick! Thanks again Charlotte.

Reply | Quote