Nested Query and sums are driving me nuts, help please! 1

[Spyridon]

I have an option group on a form that has 16 different selections, and the values are 1-16. They are stored in a field Called Reg in a table named Sales.

What I'm needing to do is extract these values as something meaningful in a report by way of query. I need the query to find a particular number in Reg, say 7, and sum them all up. Basically 7 is the value for a sale called Standard, and 5 would be the value of Classic, etc. This is what my table looks like :


WorkOrder SalesID Reg Date
5500 13312 7 01/01/04
6600 13312 5 01/01/04
9999 55551 1 01/01/04
8800 13312 7 01/01/04


I need the query to take the 7's, and total them up in a field called SumOfStandard, and then the 5's, etc for each individual SalesID. Right now what I have is a Query with 16 nested queries in it, one to sum up each field. That's a problem itself, as far as the clutter of queries, but also I'm imagining for speed. If it worked I might be able to deal with it though. But.... it doesn't. This is because there might be a query (one of the 16) that doesn't pull up a value. Let's say that salesid 13312 didn't have any Classic Plus sales, which has a value of 6 in the Reg field. I need the query, or ultimately the report, to add the values that are there, and display a 0 for anything that isn't there. whew.

So it would look like:

Standard Classic ClassicPlus
14 5 0

What can I do to get this done?

 

[Onyxpurr]

Sounds like a crosstab to me.

You can do
SalesID - Row Heading - Group By
Reg - Column Heading - Group By
WorkOrder - Value - Count

You can take it one step further by putting this as the column header instead of just Reg:

SWITCH(7, "Standard", 5, "Classic", etc...)

Make sure to put the column header names in the Column Headings property under properties for Column Heading.

Hope that helps.

 

[SteveR77]

Try the following -

Create a new query in design view.
Place the SalesID then the Reg field in your in your query twice.

Next look at the row below your menu options for what looks like a E click on it. The sigma character is for grouping in a query.

Now you will have a new line in the query that should say Group By for all your fields.

For your last field click on the Group By line and change it to sum.


Hopefully, this will give you the grouped and summed output that you are after.


For the part where you want the 16 options to be associated with their numbers.

Create a new table. (I would call this table sales options)

Set it up so it is

ID Description
1 Corporate

(And so on)

16 Web Order



Now what you will be able to do is add this table to your query and join the SalesID from the first table with the ID that you set up in the other table.


Finally, you'll be able to have the description field added to your query which if all goes well will give you output like this

ID Sale Type CountofSaleType Description
13312 1 15 Corporate

 

[Spyridon]

hey thanks Steve, and Onyxpurr too (although I haven't tried your solution just yet).

this almost works to a T, and given the information you had, it should have. My OTHER problem is that in this same table I have another field for sales of another type. Any certain order number can have both types of sales, so I have to keep them distinct. The Sales Options table can still hold the description (there are only 2 types, and they are actually the 15th and 16th value). Now the query does it's job (perfectly on your example), it counts the number of both Reg sales and Promo sales, but it duplicates the values.... anyway, back to the drawing board for me.. Although, it's a lot easier now that you gave me your example, I have something to work with. Thank you! Oh, and sorry for the long winded thanks. Here's a star!


Michael