469,623 Members | 1,422 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

SQL help

Hi,
I'm stuck on the following query. Any help would be greatly appreciated!

table1: Customers
uid, name
1, bob
2, jane
3, john

table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer

I want a query to show what customers' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer

Does anyone know how to do this?

/r
Nov 13 '05 #1
3 1690
Try the following query:

SELECT Customers.name, P1.datepurchase, P1.item
FROM Customers INNER JOIN Purchases AS P1 ON Customers.uid = P1.custID
WHERE P1.datepurchase = (
SELECT Max(P2.datepurchase)
FROM Purchases AS P2
WHERE P2.custID=P1.custID;
);

"R.Chan" <ra**********@yahoo.com> wrote in message
news:54**************************@posting.google.c om...
Hi,
I'm stuck on the following query. Any help would be greatly appreciated!

table1: Customers
uid, name
1, bob
2, jane
3, john

table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer

I want a query to show what customers' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer

Does anyone know how to do this?

/r

Nov 13 '05 #2

"R.Chan" <ra**********@yahoo.com> wrote in message
news:54**************************@posting.google.c om...
Hi,
I'm stuck on the following query. Any help would be greatly appreciated!

table1: Customers
uid, name
1, bob
2, jane
3, john

table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer

I want a query to show what customers' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer

Does anyone know how to do this?

/r


SELECT tblGroup.GroupID, tblGroup.GroupName, tblRegion.RegID,
tblRegion.RegName, tblLocation.LID, tblLocation.LocName,
tblIncident.IncdntDate, IIf((Not IsNull([RemAct1]) And IsNull([Done1])),1,0)
AS Open1, IIf((Not IsNull([RemAct1]) And Not IsNull([Done1])),1,0) AS
Closed1, IIf((Not IsNull([RemAct2]) And IsNull([Done2])),1,0) AS Open2,
IIf((Not IsNull([RemAct2]) And Not IsNull([Done2])),1,0) AS Closed2,
IIf((Not IsNull([RemAct3]) And IsNull([Done3])),1,0) AS Open3, IIf((Not
IsNull([RemAct3]) And Not IsNull([Done3])),1,0) AS Closed3, IIf((Not
IsNull([RemAct4]) And IsNull([Done4])),1,0) AS Open4, IIf((Not
IsNull([RemAct4]) And Not IsNull([Done4])),1,0) AS Closed4, IIf((Not
IsNull([RemAct5]) And IsNull([Done5])),1,0) AS Open5, IIf((Not
IsNull([RemAct5]) And Not IsNull([Done5])),1,0) AS Closed5
FROM ((tblRegion INNER JOIN tblLocation ON tblRegion.RegName =
tblLocation.RegName) INNER JOIN (tblIncident INNER JOIN tblFormal ON
tblIncident.GUIncdntID = tblFormal.GUIncdntID) ON tblLocation.LID =
tblIncident.LID) INNER JOIN tblGroup ON tblRegion.GroupID = tblGroup.GroupID
WHERE (((tblFormal.Who1EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who2EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who3EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who4EID)=[Forms]![frmRptDesign]![cmbEID])) OR
(((tblFormal.Who5EID)=[Forms]![frmRptDesign]![cmbEID]));

*Sherwood Wang MVP*
Nov 13 '05 #3
Things must be real slow in Ennis, MT these days for you to spend all the
effort to create that silly SQL statement that doesn't apply to the
question. Don, you aren't fooling anybody -- nobody believes such a
ridiculous post would be from a Microsoft Access MVP.

"Sherwood Wang" <sh****@waynes.net> wrote in message
news:9Q*******************@animal.nntpserver.com.. .

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Tom | last post: by
6 posts views Thread by wukexin | last post: by
3 posts views Thread by Colin J. Williams | last post: by
7 posts views Thread by Corepaul | last post: by
5 posts views Thread by Steve | last post: by
8 posts views Thread by Mark | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.