List customers who bought product last year but not this year

[Guest]

I'm trying to build a query that list all the customers who bought a certain
product last year but not this year.

I know how to pull all the customers who bought the product after 1/1/4, but
I don't know how to weed out customers who bought the product last year and
this year.

If i just set the date range for 2004 there will still be customers in this
list who bought the product this year.

Any ideas would be greatly appreciated,
Thank,
Billy

 

[Guest]

I would use

Where Year(DateFieldName) = Year(date())-1
That way you can use it every following year
================================
Or you can use the between

Where DateFieldName Between #1/1/2004# And #31/12/2004#

 

[tina]

well, you could write a query to pull all customers who purchased the
product "last year". then write another query to pull all customers who
purchased the product "this year". use the query wizard to create an
Unmatched query that returns all records from the "last year" query, that
have no matching customers in the the "this year" query.

it's a clunky solution, but should work.

hth

 

[John Vinson]

I'm trying to build a query that list all the customers who bought a certain
product last year but not this year.

I know how to pull all the customers who bought the product after 1/1/4, but
I don't know how to weed out customers who bought the product last year and
this year.

If i just set the date range for 2004 there will still be customers in this
list who bought the product this year.

Any ideas would be greatly appreciated,
Thank,
Billy

It may be a bit slow, but a query with two Subqueries should work:

SELECT Customers.* <or whatever you want to see>
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID FROM Sales WHERE SalesDate BETWEEN
DateSerial(Year(Date()) - 1, 1, 1) AND DateSerial(Year(Date()), 1, 0))
AND CustomerID NOT IN
(SELECT CustomerID FROM Sales WHERE SalesDate BETWEEN
DateSerial(Year(Date()), 1, 1) AND Date())


John W. Vinson[MVP]

 

[Guest]

Thanks Tina that worked perfectly. I owe you one.

Billy

 

[tina]

you're welcome.
John's query with subqueries accomplishes the same end, but i don't know
which would run faster - his solution or mine.

 

[John Vinson]

John's query with subqueries accomplishes the same end, but i don't know
which would run faster - his solution or mine.

Almost certainly yours, Tina - joins are almost always faster than
subqueries, particularly NOT IN subqueries. I'd missed your followup
when I posted!

John W. Vinson[MVP]

 

[tina]

a rare instance when "clunky" is better, after all! <looks around in
surprise, thinking "i guess i passed 'Go' after all (but how did i miss
collecting $200?)"> <bg>

 

[Van T. Dinh]

Where is your "frustrated outer join"?

You are the foremost authority on this and I would expect the frustrated
outer join is useful here.