using DSUM with a query name as your domain, and having that query nested

[the_louv]

The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.

For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.

I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.

HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.

I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")

I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.

PLEASE HELP!

 

[edtab]

Suggest you store the result of your first query into a temp table, then
use that temp table as the source of your second query.

 

[the_louv]

Thanks for the info, that's how I did it the first time, but I found the message boxes that popped up telling the user that a table was about to be created, or appended was cumbersome. I should probably put it back to that way.

 

[edtab]

You can turn off the annoying message:

DoCmd.SetWarnings False