Xmas card query

[Jeff D'Amelio]

Need to id the customers who did not get last year's card.
The current query is to id all who should get cards, sales

= 100, this year. Now I want additionally to find the

people who didn't get cards last year, of the ones who do
this year. " date like */*/02 and sum of sales < $100 AND
date like */*/03 and sum of sales >= $100 "

Customer db with all pertinent data. Sales db with link
to customer and sale date and $ amount.

I think I want to have a two line AND operation and I'm
not sure I can do that. Do I need two queries to get the
final result?

 

[Douglas J. Steele]

Dates are stored as numbers, and therefore you can't use wildcards with
them.

Create a query, and add a calculated field to it: WhatYear:
Year([MyDateField]) (replace MyDateField with whatever you've called it).
You'll now put 2002 or 2003 as the condition (WHERE WhatYear = 2002....)

You are probably going to need two queries though. Either create a summary
for 2002 and another for 2003, and join the two together, looking for those
rows where the sum was < 100 in 2002 and >= 100 in 2003, or create a summary
by year and customer, and do a query against that using IN predicates.

 

[John Spencer (MVP)]

It would help if you posted your query, but here is a sample using an EXISTS
clause that MIGHT work.

SELECT A.CustID,
EXISTS (SELECT B.CustID
FROM tableSALES as B
WHERE [Date] Between #1/1/02# AND #12/31/02#
GROUP BY B.ID
HAVING Sum(B.Sales) < 100) as MissedLastYear
FROM tableSALES as A
WHERE A.[Date] Between #1/1/03# and #12/31/03#
GROUP BY A.[CustID]
HAVING Sum(A.Sales) >= 100