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

Group By First problem

P: n/a
I must be missing something obvious. I have 3 fields and sample data.

[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992

I want to select only the most recent record. It is sorted descending
by [ContractStart] in the query and subquery. In the query Total, I
select Group By [Account#], First [Contract#] and First
[ContractStart]. For some reason I am getting record 2, not 1. If I
select Last instead of First, I still get record 2. What am I not
thinking about?
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 30 Apr 2004 12:26:13 -0700, neptune wrote:
I must be missing something obvious. I have 3 fields and sample data.

[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992

I want to select only the most recent record. It is sorted descending
by [ContractStart] in the query and subquery. In the query Total, I
select Group By [Account#], First [Contract#] and First
[ContractStart]. For some reason I am getting record 2, not 1. If I
select Last instead of First, I still get record 2. What am I not
thinking about?


Try:
Select YourTable.* from YourTable
Where [ContractStart] = DMax("[ContractStart]","YourTable");

You should get only the record(s) with the last date entered.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 12 '05 #2

P: n/a
neptune wrote:
I must be missing something obvious. I have 3 fields and sample data.

[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992

I want to select only the most recent record. It is sorted descending
by [ContractStart] in the query and subquery. In the query Total, I
select Group By [Account#], First [Contract#] and First
[ContractStart]. For some reason I am getting record 2, not 1. If I
select Last instead of First, I still get record 2. What am I not
thinking about?

Nov 12 '05 #3

P: n/a
On Fri, 30 Apr 2004 21:45:24 GMT, fredg <fg******@example.invalid> wrote:
On 30 Apr 2004 12:26:13 -0700, neptune wrote:
I must be missing something obvious. I have 3 fields and sample data.

[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992

I want to select only the most recent record. It is sorted descending
by [ContractStart] in the query and subquery. In the query Total, I
select Group By [Account#], First [Contract#] and First
[ContractStart]. For some reason I am getting record 2, not 1. If I
select Last instead of First, I still get record 2. What am I not
thinking about?


Try:
Select YourTable.* from YourTable
Where [ContractStart] = DMax("[ContractStart]","YourTable");

You should get only the record(s) with the last date entered.


Technically, that's almost a valid answer, but calling domain lookup functions
is very inefficient, particularly from within a query. I'd use a subquery
instead.

Select YourTable.* from YourTable
Where [ContractStart] = (SELECT MAX(LastRec.ContractStart) FROM YourTable As
LastRec WHERE LastRec.[Account#] = YourTable.[Account#]);
Nov 12 '05 #4

P: n/a
neptune wrote:
I must be missing something obvious. I have 3 fields and sample data.

[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992

I want to select only the most recent record. It is sorted descending
by [ContractStart] in the query and subquery. In the query Total, I
select Group By [Account#], First [Contract#] and First
[ContractStart]. For some reason I am getting record 2, not 1. If I
select Last instead of First, I still get record 2. What am I not
thinking about?


I did the same thing as you. I had to enter the records in a different
order than the order presented above to get the results you received.

Since I assume you have more than three records, this is what I did. I
created a Totals query on account number and contract start date.
Grouped on Account, Max on Start (no need to sort desc). I called it
query1.

I then created a new query, Query2, where I added Query1 and the table.
Then I drew relation lines between the AccountNumber and Date fields
for both. Now I get the correct results.

Your sort descending in the first query is worth squawt.
First/Last/Group don't help.
Nov 12 '05 #5

P: n/a
bs**********@hotmail.com (neptune) wrote in
news:c5**************************@posting.google.c om:
I must be missing something obvious. I have 3 fields and sample
data.

[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992

I want to select only the most recent record. It is sorted
descending by [ContractStart] in the query and subquery. In the
query Total, I select Group By [Account#], First [Contract#] and
First [ContractStart]. For some reason I am getting record 2, not
1. If I select Last instead of First, I still get record 2. What
am I not thinking about?


"FIRST" does not mean what you seem to think it means. FIRST means
the first record in the set of records, and is dependent on sort
order. Indeed, I've given up using it because the results seem
semi-random. Using a TOP 1 query is more reliable, but has its own
limitations.

The reason you're getting record 2 might be because of the contract
number, which is the lowest of them all. As I said, I don't fully
understand the purpose or behavior of FIRST, and so, I avoid using
it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #6

P: n/a
> Since I assume you have more than three records, this is what I did. I
created a Totals query on account number and contract start date.
Grouped on Account, Max on Start (no need to sort desc). I called it
query1.

I then created a new query, Query2, where I added Query1 and the table.
Then I drew relation lines between the AccountNumber and Date fields
for both. Now I get the correct results.


I do have more than 3 records with many account#s, so this was the
only way that worked. Thanks.
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.