I am an access n00b.... so please bear with me! :D

I have a table that has some sales data: for a given invoice, there are a number of units sold, and a cost per unit. One invoice can have a number of different items on it. Eg:


Invoice UnitID Quantity_sold UnitPrice
1001 100 3 100
1001 101 1 200
1001 102 1 10



Now: What I want to do is have a query return the TOTAL cost for the invoice.

I have an expression that calculates total price (quantity_sold * unit price). Running this, I get:


Invoice TotalCost
1001 300
1001 200
1001 10





What I now want is to sum the total for the invoice, such that the output is just:

Invoice TotalCost
1001 510


Can someone please explain how to do this? In the query builder, I have tried to output Invoice as "group by" and TotalCost as "sum", but that doesn't work.


Thanks!

What I now want is to sum the total for the invoice, such that the output is just:

Invoice TotalCost
1001 510
It's been a long time since I've been this low (ha ha :)), but would this possibly work I wonder:SELECT invoice, sum([quantity]*[unit price]) as [Total of Invoice]
FROM table
GROUP BY [invoice]If perhaps that doesn't work, just use a couple of stacked queries to build on what you already have (with the Total Cost already queried). Group your invoice totals using one more query...

Just repeat the calculation either in an unbound textbox in the form footer or even in the query, for the form it's just =Sum([QuantitySold]*[UnitPrice])

Thanks very much - I created another query to sum the totals as suggested, and it worked great.