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

All Accounts with Their Latest Request

P: n/a
Posted this once before but the only reply was not the answer to the problem.

I have a table with these fields:

RequestId
Account
RequestDate
RequestDescription

I want to get a list of all accounts in the table along with ONLY their lastest request, ie something along the lines of:

select Account, RequestDate, RequestDescription where RequestDate = max(Request Date for that account)

IE - if the table looked like this:

1 Account1 1/1/2001 Request1
2 Account2 1/3/2001 Request2
3 Account1 2/5/2001 Request3
4 Account1 3/1/2001 Request4
5 Account2 4/5/2001 Request5

What I would like to get back is:

4 Account1 3/1/2001 Request4
5 Account2 4/5/2001 Request5

I can't seem to get the syntax down.

Any help is appreciated.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
HateSpam <Ha******@nospam.com> wrote in
news:35***************@fe2.columbus.rr.com:
Posted this once before but the only reply was not the answer
to the problem.

I have a table with these fields:

RequestId
Account
RequestDate
RequestDescription

I want to get a list of all accounts in the table along with
ONLY their lastest request, ie something along the lines of:

select Account, RequestDate, RequestDescription where
RequestDate = max(Request Date for that account)

IE - if the table looked like this:

1 Account1 1/1/2001 Request1
2 Account2 1/3/2001 Request2
3 Account1 2/5/2001 Request3
4 Account1 3/1/2001 Request4
5 Account2 4/5/2001 Request5

What I would like to get back is:

4 Account1 3/1/2001 Request4
5 Account2 4/5/2001 Request5

I can't seem to get the syntax down.

Any help is appreciated.

Look up the use of DMax(), not max(), It's in the help file

Your where clause will look somewhat similar to this:

WHERE RequestDate = Dmax("RequestRate","requesttable","Account =
'" & [Account] & "'")

Assuming Account is a text field in the table.

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #2

P: n/a
----- Original Message -----
From: "HateSpam" <Ha******@nospam.com>
Newsgroups: comp.databases.ms-access
Sent: Saturday, February 26, 2005 1:44 PM
Subject: All Accounts with Their Latest Request

Posted this once before but the only reply was not the answer to the problem.
I have a table with these fields:

RequestId
Account
RequestDate
RequestDescription

I want to get a list of all accounts in the table along with ONLY their lastest request, ie something along the lines of:
select Account, RequestDate, RequestDescription where RequestDate = max(Request Date for that account)
IE - if the table looked like this:

1 Account1 1/1/2001 Request1
2 Account2 1/3/2001 Request2
3 Account1 2/5/2001 Request3
4 Account1 3/1/2001 Request4
5 Account2 4/5/2001 Request5

What I would like to get back is:

4 Account1 3/1/2001 Request4
5 Account2 4/5/2001 Request5

I can't seem to get the syntax down.

Any help is appreciated.


Assuming you have a table named "Table1" with the field names and data as
above, try this Query:
SELECT T1.*
FROM Table1 AS T1
WHERE T1.RequestDate=
(SELECT Max(T2.RequestDate)
FROM Table1 AS T2
WHERE T2.Account=T1.Account;);
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.