Dsun function to sum the sales of each customer

[falahsalih]

Hi everybody.


Can Dsum function sums the sales of each customer. For example, let say we have the following query

QueryCustomerSales 
Customer Name 
Order ID
Item 
Amount 
Price

So, can i get output something like :

John    200.540 $
Mark    300.350 $

I tried the following code "

Dsum ("[Amount] * [Price]", "QueryCustomerSales", "[Customer Name]= [Customer Name]")

However, it returns the sales of all customers (all together ):

John    500.890 $
Mark    500.890 $

[rpeare]

don't do this with a dsum function. Use an aggregate query


SELECT [Customer Name], Sum([Amount] * [Price]) as TotalSpent FROM <SALESTABLENAME> GROUP BY [Customer Name]

[falahsalih]

don't do this with a dsum function. Use an aggregate query


SELECT [Customer Name], Sum([Amount] * [Price]) as TotalSpent FROM <SALESTABLENAME> GROUP BY [Customer Name]

Can I involve another sum function (example: sum of payments for the same customer ) in the sql code ?

[rpeare]

yes you can, just add the field you want to sum and select SUM in the TOTALS line of the query design window.

[falahsalih]

yes you can, just add the field you want to sum and select SUM in the TOTALS line of the query design window.

I got error "circular reference caused by QueryCustomerSales"

[rpeare]

what is your table structure?
what is your formula?
what's the SQL code you're using?

Need more information than you supplied?