Group By First problem

[neptune]

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?

[fredg]

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.

[Salad]

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?

[Steve Jorgensen]

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#]);

[Salad]

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.

[David W. Fenton]

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

[neptune]

> 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.