Query

S

Sul@MS

AccessXP

I have 3 fields;

Customer, Sales,Discount

How to I create a query to find those customers who sales exceed 1000?

tia
 
F

fredg

AccessXP

I have 3 fields;

Customer, Sales,Discount

How to I create a query to find those customers who sales exceed 1000?

tia

Here is a simple query SQL.
Change the table and fiels names as needed.

Select YourTable.[Customer],YourTable.[Sales] Where
YourCustomer.[Sales] < 1000
 
S

Sul@MS

thanks
will try it out

just one more question;
how to I query the total amount of Sales inclusive of all Customers?

DStegon via AccessMonster.com said:
WHAT??? Completely wrong!!

First off you use LESS THAN and second you are not summing the sales per
contact (customer). What if the customer had more than one sales invoice.

Replace the field names and table with your own, but just in case you are
storing Subtotal and Taxes in different fields and calculating a "total"
(which is what you should do.. rarely store calculated amounts) here is
the
query that will give you ...

SELECT tbl_Sales_Invoice.Contact_ID, Sum([Subtotal]+[Taxes]) AS Total
FROM tbl_Sales_Invoice
GROUP BY tbl_Sales_Invoice.Contact_ID
HAVING (((Sum([Subtotal]+[Taxes]))>1000))
ORDER BY Sum([Subtotal]+[Taxes]) DESC;

This will sum all the sales group by Contact, thus giving you the total
sales
per customer over not just single invoices but over all the invoices.
This
also orders them by highest to lowest. You can sort any way you want.
This
will only list the contactid and the sum total of sales. Add fields to the
query like customer name and other things as needed.

You can then always add a WHERE clause to the query to have the data date
restricted. Of course put in your own dates or have the program allow the
user to pick a date range, which you then put into the query

SELECT tbl_Sales_Invoice.Contact_ID, Sum([Subtotal]+[Taxes]) AS Total
FROM tbl_Sales_Invoice
WHERE (((tbl_Sales_Invoice.Create_DT) Between #1/1/2008# And
#12/31/2008#))
GROUP BY tbl_Sales_Invoice.Contact_ID
HAVING (((Sum([Subtotal]+[Taxes]))>1000));

[quoted text clipped - 5 lines]

Here is a simple query SQL.
Change the table and fiels names as needed.

Select YourTable.[Customer],YourTable.[Sales] Where
YourCustomer.[Sales] < 1000
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top