Xmas card query

  • Thread starter Thread starter Jeff D'Amelio
  • Start date Start date
J

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
Click to expand...
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?
 
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.
 
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
 
Back
Top