Total of multiple records in subform

[jmhicsupt]

I have a query based off of a subform. The subform will have multiple entries for the client's name. Then they will also have the amount that we invoiced them. Is there a way to have a total column that would sum up all of the invoices? I need this field so that I can sort the records by the highest amount by customer. In other words, line 1 and 2 would show:

ABC Company $1000 $2500
ABC Company $1500 $2500
XYZ Company $500 $7000
XYZ Company $500 $7000
XYZ Company $6000 $7000

Thanks in advance.

 

[dhookom]

You could use DSum() to calculate the total by Customer. If you try a subquery, you could see the total but I'm not sure you would be able to sort by the total in your report. You could also create a totals query that groups by Customer and sums Invoice. Then join this query into the record source of your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

A starting point:
SELECT A.ClientName, A.Amount, T.Total
FROM yourTable INNER JOIN (
SELECT ClientName, Sum(Amount) AS Total FROM yourTable GROUP BY ClientName
) AS T ON A.ClientName = T.ClientName
ORDER BY 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886