Sum on an invoice

[ainwood]

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

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!

 

[ajetrumpet]

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

 

[Rich]

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])

 

[ainwood]

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