List customers who bought product last year but not this year

List customers who bought product last year but not this year

Post by QmlsbHlSb2 » Wed, 13 Jul 2005 04:01:05


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
 
 
 

List customers who bought product last year but not this year

Post by T2Zlc » Wed, 13 Jul 2005 04:27:03

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#

 
 
 

List customers who bought product last year but not this year

Post by tina » Wed, 13 Jul 2005 05:00:50

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





certain
but
and
this
 
 
 

List customers who bought product last year but not this year

Post by John Vinso » Wed, 13 Jul 2005 05:53:16

On Mon, 11 Jul 2005 12:01:05 -0700, "BillyRogers"



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]
 
 
 

List customers who bought product last year but not this year

Post by QmlsbHlSb2 » Wed, 13 Jul 2005 06:19:01

Thanks Tina that worked perfectly. I owe you one.

Billy
 
 
 

List customers who bought product last year but not this year

Post by tina » Wed, 13 Jul 2005 07:04:09

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.





that



a
1/1/4,
year
in
 
 
 

List customers who bought product last year but not this year

Post by John Vinso » Wed, 13 Jul 2005 07:40:22


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]
 
 
 

List customers who bought product last year but not this year

Post by tina » Wed, 13 Jul 2005 08:11:11

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>
 
 
 

List customers who bought product last year but not this year

Post by Van T. Din » Wed, 13 Jul 2005 09:01:35

Where is your "frustrated outer join"?

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

--
Cheers
Van T. Dinh
MVP (Access)