SQL Question - restated

[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 record has a customer name, an invoice number, and a date of
the invoice. We also have a table of line items - each record has an
invoice number and a line item that was purchased - so this is a one to many
relationship (one invoice to many lines). 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 W. Vinson]

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 record has a customer name, an invoice number, and a date of
the invoice. We also have a table of line items - each record has an
invoice number and a line item that was purchased - so this is a one to many
relationship (one invoice to many lines). 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.

You need two criteria, one in a subquery.

First you'ld create a query joining the customer table to the invoice table to
the invoicedetails table, with a criterion of "black sneakers" on the item
field. This will give you whatever customers purchased the item.

Then you would put a subquery, in parentheses, *in an Exists clause a vacant
Field cell*:

Exists (SELECT CustomerID FROM Invoices AS X WHERE X.CustomerID =
Customers.CustomerID AND X.InvoiceDate < Invoices.InvoiceDate)

The subquery will find previous invoices for this customer (the invoice data
of the subquery being earlier than the invoice date for the black sneakers
item in the main query).

 

[dhstein]

John,

Thanks for your help. I'm getting a syntax error on the statement below.

SELECT tblInvoice.CustomerName, tblLines.Item, tblInvoice.InvoiceDate
FROM tblInvoice INNER JOIN tblLines ON tblInvoice.InvoiceNumber =
tblLines.InvoiceNumber
WHERE (((tblLines.Item)="sneakers"))
Exists (SELECT CustomerID FROM tblInvoice AS X WHERE X.CustomerID =
tblInvoice.CustomerID AND X.InvoiceDate < tblInvoices.InvoiceDate);

 

[John W. Vinson]

Thanks for your help. I'm getting a syntax error on the statement below.

You need an AND:

SELECT tblInvoice.CustomerName, tblLines.Item, tblInvoice.InvoiceDate
FROM tblInvoice INNER JOIN tblLines ON tblInvoice.InvoiceNumber =
tblLines.InvoiceNumber
WHERE (((tblLines.Item)="sneakers"))
AND Exists (SELECT CustomerID FROM tblInvoice AS X WHERE X.CustomerID =
tblInvoice.CustomerID AND X.InvoiceDate < tblInvoices.InvoiceDate);

I usually build such queries in the SQL window, not the grid, and my grid
advice was evidently wrong; Access may change the SQL if you enter this and
then go into the grid and back.

I hope you're not storing customer names in the invoice table!! They should be
in a separate Customers table (unless you never expect to get repeat
business).