Calculate money spent =<200 for customers during 2008

[KevinSlater]

Hi,

I'm trying to do a query in access to show any customers that have spent £200 or less in total during 2008. We have an access database
that has records of all customers, customer branch and invoice details.

so far ive created a query that looks at the related fields, and some example data is shown below:

Customer, Customer branch, invoice date, Item price

Customer1 branch1 07/05/2008 50
Customer1 branch2 10/06/2008 100
Customer1 branch3 18/08/2008 40
Customer2 branch1 07/05/2008 400
Customer2 branch2 10/07/2008 800
Customer2 branch3 18/11/2008 600


So in the example data the querys results should show the following
Customer Money spent
Customer 1, 180

Because the item price of invoices for customer1 was under 200, but as customer2 was over 200 its not shown in the query
results.


SQL i have at present is below:
SELECT customers.Customer, Invoices.[Invoice Date], Invoices.[Item Price]
FROM customers INNER JOIN Invoices ON customers.Customer = Invoices.Customer
WHERE (((Invoices.[Invoice Date])>#12/31/2007#) AND ((Invoices.[Item Price])<200));


Ive filtered the invoice date to only show records for 2008 and made the item price field <200 but from here im not sure what needs to be done. guess i need to create a new field for money spent? and do a calculation but not sure what to do

Any advise on how to do this would be great

 

[DCrake]

You need to create the following logic

Select Customer, [Item price] From Group by Customer Sum([Item Price]) As Spent Where Sum([Item Price]) < 200 And [Invoice Date] Between #01/01/2008# And #31/12/2008# ;

in essense create a join between the customers and the invoices group by customer summing the amount on each invoice and filtering out all customers who have spent less than 200 between the selected date range.

David

 

[KevinSlater]

hm Ok attached is a screenshot of how the quey looks at present, but when i run the query the customer is shown multiple times.

ive done the item price as a sum and set customer & invoice date to group by
but im not sure what else needs to be done?

 

[KevinSlater]

If i do a Total: "sum" on customer and run the query a message says "data type mismatch, in criteria expression"

 

[DCrake]

On you date range column you need Where Not Group By The way you have it you will get a sub total each time the date changes.

 

[KevinSlater]

done that change, but when i run the query it says "Data type mismatch in criteria expression"

 

[DCrake]

Are you summing on a numeric value and grouping by the customer? Post ca copy of the query sql.

 

[KevinSlater]

Hi i didnt group the query by customer which ive now done and its working fine.

Thanks for your help.