If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL Question - restated
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. |
#2
|
|||
|
|||
SQL Question - restated
On Thu, 9 Jul 2009 17:25:01 -0700, dhstein
wrote: 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). -- John W. Vinson [MVP] |
#3
|
|||
|
|||
SQL Question - restated
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" wrote: On Thu, 9 Jul 2009 17:25:01 -0700, dhstein wrote: 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). -- John W. Vinson [MVP] |
#4
|
|||
|
|||
SQL Question - restated
On Thu, 9 Jul 2009 19:19:01 -0700, dhstein
wrote: 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). -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|