Query of customers who spent $x last year

[ZenMasta]

I think I am close to nailing this I just need help on the last part.

I am trying to create a list of customers that have ordered from us since
last year. And also if their annual spending is greater than a certain
amount.

I created a query of my order tables , contactid (Group By), orderdate(Max -

#12/31/08#), ordertotal (Sum)

I created a query of my customer table
contactid (<> 5779), business name, address, city, state, zip,
qrymaxofrderdate

5779 is a dummy customer so I am excluding it from the list.

Anyway, when I run my ordermax query it works fine. I created a couple of
orders on my dummy account and manually calculated the order sum and it
works. However if I try to enter criterea on the orrderamount as lets say >
"$100" it gives me an error: Data type mismatch in criteria or expression

Then finally just one more minor thing. There is another dummy customer I
want to filter out.
I tried changing the criteria for contact id to <>5779 or <>1883 but when I
ran the query neither one was filtered.

Since its just 2 customers I can just manually delete those after I export
my list but it would be nice to know for future reference.

 

[John Spencer]

Lots of assumptions here since you failed to tell us your actual field names
and table names. Plus I don't know if ContactID is a number field or a text
field containing numbers.

SELECT [ContactID], Max([OrderDate]), Sum([OrderAmount])
FROM [SomeTable]
WHERE [OrderDate] >#12/31/2008#
AND [ContactID] NOT IN (5779,1883)
HAVING Sum([OrderAmount]) > 100

If you don't use the Not In operators to eliminate some of the contacts then
you need to use an expression like the following to eliminate 5779 and 1883
... AND [ContactID] <> 5779 AND [ContactID] <> 1883 ...


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

I created a query of my order tables , contactid (Group By), orderdate(Max
- >#12/31/08#), ordertotal (Sum)
Use this --
contactid (Group By), orderdate (WHERE >#12/31/08#), ordertotal (Sum)

Use this as criteria --
100

Use <>5779 AND <>1883

 

[ZenMasta]

Ah ha, I forgot I wasn't supposed to use the Dollar sign in my criteria.

Thanks you guys.

 

[De Jager]

I think I am close to nailing this I just need help on the last part.

I am trying to create a list of customers that have ordered from us since
last year. And also if their annual spending is greater than a certain
amount.

I created a query of my order tables , contactid (Group By),
orderdate(Max -
I created a query of my customer table
contactid (<> 5779), business name, address, city, state, zip,
qrymaxofrderdate

5779 is a dummy customer so I am excluding it from the list.

Anyway, when I run my ordermax query it works fine. I created a couple of
orders on my dummy account and manually calculated the order sum and it
works. However if I try to enter criterea on the orrderamount as lets say
expression

Then finally just one more minor thing. There is another dummy customer I
want to filter out.
I tried changing the criteria for contact id to <>5779 or <>1883 but when
I ran the query neither one was filtered.

Since its just 2 customers I can just manually delete those after I export
my list but it would be nice to know for future reference.