total groups in query

  • Thread starter Thread starter Caleb
  • Start date Start date
C

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
 
Caleb said:
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
Click to expand...


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.
 
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...
 
Caleb said:
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...
Click to expand...


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