I need total sales by customer....

[Kelvin]

I have sales data for customers that I want to send a suvey to, but I want
to sent it to customers that have spent over $500 in a given period.

I know that in a report I can do a subtotal and get this information.
Can this be done in a query?

Would someone please help me get started on this?

Thanks in advance!

Kelvin

 

[scubadiver]

Do you have a one-to-many relationship between customers and their purchases?

Create a totals query that incorporates the customer and money spent and
filter it for customers who have spent more than $500

 

[Dirk Goldgar]

I have sales data for customers that I want to send a suvey to, but I want
to sent it to customers that have spent over $500 in a given period.

I know that in a report I can do a subtotal and get this information.
Can this be done in a query?

Would someone please help me get started on this?

You'd need to post your table structure before anyone could give you a
specific query, but the SQL would look something like this:

SELECT Customer, Sum(SalesAmount) AS TotalSales
FROM SalesTable
WHERE SaleDate Between #5/1/2008# And #5/31/2008#
GROUP BY Customer
HAVING Sum(SalesAmount) >= 500

That's assuming a table like this:

SalesTable
----------------
Customer
SaleDate (date/time)
SalesAmount (currency)

Obviously, you'd need to customize the SQL to match your table and fields.

 

[Kelvin]

Perfect, Thanks for the help!

Kelvin
++++++++++++++++++++++++++++++++++++++++++++++++
SELECT dbo_PTSLS.CusId AS Expr1, Sum(dbo_PTSLS.AmtCost) AS TotalSales
FROM dbo_PTSLS
WHERE ((([dbo_PTSLS.DateInvoice]) Between #5/1/2008# And #5/31/2008#))
GROUP BY dbo_PTSLS.CusId
HAVING (((Sum([dbo_PTSLS.AmtCost]))>=500));
++++++++++++++++++++++++++++++++++++++++++++++++

 

[Kelvin]

How about this?
The Service Sales table shows both labor and parts.
There is an Invoice Date and Customer ID also.
Now I need to know the Sum of both parts and labor, and customers who have
spent over $500 in a given period.

Thanks for your help!!!!

Kelvin

 

[Kelvin]

does this look correct?

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT dbo_SVSLS.CusId AS CustomerID, Sum(dbo_SVSLS.AmtParts) AS TotalParts,
Sum(dbo_SVSLS.AmtLabor) AS TotalLabor
FROM dbo_SVSLS
GROUP BY dbo_SVSLS.CusId
HAVING (((Sum([AmtParts]+[AmtLabor]))>1000));
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[John Spencer]

Then the having clause would need to read

HAVING Nz(Sum(Labor),0) + Nz(Sum(Parts),0) > 500

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Dirk Goldgar]

does this look correct?

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT dbo_SVSLS.CusId AS CustomerID, Sum(dbo_SVSLS.AmtParts) AS
TotalParts, Sum(dbo_SVSLS.AmtLabor) AS TotalLabor
FROM dbo_SVSLS
GROUP BY dbo_SVSLS.CusId
HAVING (((Sum([AmtParts]+[AmtLabor]))>1000));
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

It looks okay to me, so long as neither AmtParts nor AmtLabor will ever be
Null, except that (a) you've dropped the WHERE clause that specified the
date criterion, and (b) your total amount criterion doesn't conform to your
description of what you wanted to return:

 

[Kelvin]

Thanks for your help!

Then the having clause would need to read

HAVING Nz(Sum(Labor),0) + Nz(Sum(Parts),0) > 500

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Kelvin]

thanks, I'll keep that in mind

Kelvin

does this look correct?

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT dbo_SVSLS.CusId AS CustomerID, Sum(dbo_SVSLS.AmtParts) AS
TotalParts, Sum(dbo_SVSLS.AmtLabor) AS TotalLabor
FROM dbo_SVSLS
GROUP BY dbo_SVSLS.CusId
HAVING (((Sum([AmtParts]+[AmtLabor]))>1000));
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

It looks okay to me, so long as neither AmtParts nor AmtLabor will ever be
Null, except that (a) you've dropped the WHERE clause that specified the
date criterion, and (b) your total amount criterion doesn't conform to
your description of what you wanted to return:


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)