SQL Question - restated

  • Thread starter Thread starter dhstein
  • Start date Start date
D

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

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).
 
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);
 
Thanks for your help. I'm getting a syntax error on the statement below.
Click to expand...

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).
 
Back
Top