By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

SQL help

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a

"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

P: n/a
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.