List customers who bought product last year but not this year

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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#
 
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
 
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
Click to expand...

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]
 
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's query with subqueries accomplishes the same end, but i don't know
which would run faster - his solution or mine.
Click to expand...

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]
 
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>
 
Where is your "frustrated outer join"?

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