Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Los Angeles
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    crosstab query vs. subqueries (2000)

    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.

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query vs. subqueries (2000)

    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

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query vs. subqueries (2000)

    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
    Francois

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: crosstab query vs. subqueries (2000)

    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.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Los Angeles
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query vs. subqueries (2000)

    That did the trick! Thanks again Charlotte.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •