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
|
|||
|
|||
Subquery Question - continued
In trying to do a somewhat complicated query which it turns out will require a
subquery, I'm trying to create a simple example and see if I can get this simplified version to work. So look at this example: ID InvoiceNumber CustomerName InvoiceDate 1 12345 Steve 03-Jul-09 2 23456 John 01-Jul-09 3 34567 Steve 07-Jul-09 4 45678 John 08-Jul-09 Based on an example from the web and some excellent advice from John Vinson and John Spencer, I've created a query that will display records that have a predecessor - in other words an invoice which is not the first invoice for that customer. I have this: SELECT tblInvoice.CustomerName, tblInvoice.InvoiceNumber, tblInvoice.InvoiceDate, (SELECT TOP 1 Dupe.InvoiceNumber FROM tblInvoice AS Dupe WHERE Dupe.CustomerName = tblInvoice.CustomerName AND Dupe.InvoiceDate tblInvoice.InvoiceDate ORDER BY Dupe.InvoiceNumber DESC, Dupe.InvoiceDate) AS PriorValue FROM tblInvoice WHERE ((((SELECT TOP 1 Dupe.InvoiceNumber FROM tblInvoice AS Dupe WHERE Dupe.CustomerName = tblInvoice.CustomerName AND Dupe.InvoiceDate tblInvoice.InvoiceDate ORDER BY Dupe.InvoiceNumber DESC, Dupe.InvoiceDate)) Is Not Null)); This works and gives me this (columns may not line up in this post - but there are 4 fields): CustomerName InvoiceNumber InvoiceDate PriorValue Steve 34567 07-Jul-09 12345 John 45678 08-Jul-09 23456 Now I want to modify this. There is another table called tblLines tblLines: ID InvoiceNumber Item 1 12345 peppers 2 23456 grapes 3 23456 bananas 4 12345 peaches 5 34567 grapes 6 12345 blueberries 7 45678 oranges 8 23456 bananas 9 34567 cherries 10 23456 cherries 11 45678 cherries I want to create a query that will find customers and invoices that have cherries as a line item AND the customer purchased something previously - but not cherries. Thanks for any help on this. |
Thread Tools | |
Display Modes | |
|
|