total groups in query

[Caleb]

I have a customer sales database, in the db there are multiple records for
each customer. Im trying to create a query that will pull out the total spent
by each customer. I need the query to find all records for each customer, and
then total what they've spent and place that in a new field, i need this
field to display the total spent for each individual customer, any help would
be appreciated. thanks

 

[Marshall Barton]

I have a customer sales database, in the db there are multiple records for
each customer. Im trying to create a query that will pull out the total spent
by each customer. I need the query to find all records for each customer, and
then total what they've spent and place that in a new field, i need this
field to display the total spent for each individual customer, any help would
be appreciated. thanks

Try this:

SELECT Customer. Sum(spent) As Total
FROM CustomersTable
GROUP BY Customer

Replace the names I used with your real table and field
names.

THen post back with more details and what about it that does
not do whatever else you need.

 

[Caleb]

Okay that got the totals and grouping right, now I need that totals field to
subtract refunds. The refunds field has strings in it so what I need is
something like
IIf [Refunds] Is Not Null, then subtract [Price] from [spent].

I got it to do this in a Report with
="$" & Sum(IIf([Refunds] Is Null,[Spent],0))
however I cannot sort by this value, soI need the query to do the
calculation then I can use it in the Report...

 

[Marshall Barton]

Okay that got the totals and grouping right, now I need that totals field to
subtract refunds. The refunds field has strings in it so what I need is
something like
IIf [Refunds] Is Not Null, then subtract [Price] from [spent].

I got it to do this in a Report with
="$" & Sum(IIf([Refunds] Is Null,[Spent],0))
however I cannot sort by this value, soI need the query to do the
calculation then I can use it in the Report...

Don't try to do any formatting in the query (i.e. get rid of
the $). If you want to display a formatted value in the
report, then apply the formatting to the report text box
bound to the Total field.

I don't have a clear picture of what you want, but I guess
the calculated field in the query could be something more
like:

SELECT Customer, Sum(IIf(Refunds Is Null,Price,0)) As Total
FROM CustomersTable
GROUP BY Customer

Then the report can sort by the Total field.