How to create a sum in a query where the sum is based on a sub form

[feed the inferno]

i think im confusing myself here but ill try my best to explain

i have a form where cds can be purchased, within it is a subform which allows a user to choose from a combo box of all the cds

Now i got a textbox to display the sum of the prices using =sum([prices])
but i need to put that sum into a query, i understand you can do the total: [price1]+[price2] in order to add prices up but because my prices are based within a sub form i only have 1 price field and therefore can only add them up using the unbound textbox =sum([prices])

is there anyway of doing somthing similar in a query? oh and i have tried putting total:sum([prices])

thanks

[Jerry Maiapu]

Why do you have to put sum([prices]) into a query? if the unbound textbox =sum([prices]) is on the parent form form then what is the problem..

Give the table detaisl from which you forms (Parent +child) is based on and tell us what you are trying to achieve rather than mentioneing wha you'would like to do..nad maybe we maight help..
I screen shot would be a good idea as well..

JM

[feed the inferno]

Hey sorry I forgot to mention what i'm trying to get out of it, I'm creating a query so that I can make a report based on a transaction number that will display what items the customer has chosen and a final total price. I know it's bad practice to store the total price so I'm trying to put the sum in the query

so basically I need a recipt in the form of a report with a total price on it

I'm not at my computer at the moment so I cannot get a screenshot but if one is required I will get one later

thanks

[NeoPa]

Assuming the correct names the following should work in a query :

Total:=Sum([Prices])

Welcome to Bytes!

[feed the inferno]

Nope i put that I tried that in the first post, it errors and doesn't work I'm afraid

[NeoPa]

I guess you want the query to show both detail and aggregate data then. I misunderstood your question as most realise that is not how queries work (except by fiddling a fair bit).

If you are using a form with a subform, then your easiest route to showing a sum of the items on your subform, on your main form, is to create an unbound control (TextBox) in the Footer section of your subform, which can either be visible or hidden depending on your choice, and refer to that item (See Referring to Items on a Sub-Form) in another TextBox control on your main form.

[Jerry Maiapu]

I don’t know if I am on the right track but I think you need to have a nested (sub query) to do that. But Ill show you how to use two different queries like what I do as am not good at nested sub queries..

First run query wizard for the two tables (assuming they have 1-many relationship: also assuming that the many side contains the CD names and its prizes).
Select the necessary fields and save the query.

Now run query wizard and create another query based on the last query you created.
Go to design view and click the Totals button.

On the sort row leave the GROUP BY clause and select SUM for the prices.

When you run the query you’ll see a single row for each item with their tolal price.
Create your report based on this summary query.

Tell me if I am wrong somewhere.

Regards

Jerry