SQL Question

[dhstein]

I'm not looking for the exact syntax here - just an idea of what the SQL
statement might look like. The situation is we have a table of invoices.
Each invoice has a customer name, a list of products purchased, and a date of
the invoice. We want to find the customer names who have purchased a
specific item - for example Black Sneakers - but the purchase was not the
first time the customer made a purchase - maybe last time they purchased blue
sandals. So the item has to appear on a customer invoice but the customer
had purchased some other items on a previous invoice. So the query would
provide the list of RETURNING customers who are purchasing black sneakers.
Thanks for any help on this.

 

[John Spencer]

SELECT A.*
FROM Invoices as A
WHERE Exists
(SELECT *
FROM Invoices As B
WHERE B.CustomerID = A.CustomerID
and B.InvoiceDate < A.InvoiceDate)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[dhstein]

John,

Thanks for your reply. Are "A" and "B" variables - or do I need to put my
field names in there? I tried the following but got a syntax error.


SELECT A.*
FROM tblInvoice INNER JOIN tblLines ON tblInvoice.InvoiceNum =
tblLines.InvoiceNumber As A
WHERE Exists
(SELECT *
FROM tblInvoice INNER JOIN tblLines ON tblInvoice.InvoiceNum =
tblLines.InvoiceNumber As B
WHERE B.CustomerName = A.CustomerName
and B.InvoiceDate < A.InvoiceDate);

 

[John Spencer]

A and b are alias names for the table tblinvoice.

Why have you added in the tbllines table?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[dhstein]

Thanks John. I posted this question twice - the heading that reads "SQL
Question restated" is a better explanation of the problem - and shows why I
added the tblLines table. Basically there are 2 tables - the invoice table
with the invoice # and customer name and the tblLines table which can have
multiple records per invoice - since an invoice can have more than one item
that was sold. I'm starting to put 2 and 2 together here with your help and
John Vinson who's responding in the other post - and trying to figure out the
subquery syntax that I need.