A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Subquery Question - continued



 
 
Thread Tools Display Modes
  #1  
Old July 13th, 2009, 12:22 AM posted to microsoft.public.access
dhstein
external usenet poster
 
Posts: 665
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.