Return all data for the correct Row

[Guest]

Hey folks,
Hope to recieve a little help Here. I need to return all the fields of a
record of data where the selection criteria and the aggregation is
questionable. Example:
IDN YRMODAY PCT REG DIST
1 527479999 20021218 71 08 4
2 527479999 20060418 70 09 6
3 527479999 20040318 70 08 4
4 527479999 20041218 70 08 4
5 527479999 20060418 71 07 9
6 527479999 20041218 71 08 4
7 527479999 20040318 71 03 4
8 527479999 20060418 71 08 4

In the above set of records , I need to return the record with the most
recent date (yyyymmdd) <Max> with the highest PCT <Max> and all columns in
that row. In this case it would be Row 8 as the last value of the date
grouping. There are at 30 other columns that may have variant data. I am
currently using a Select query to find the Max date group by the IDN:
Select IDN, MAX(YR&MM&DD) as TDATE FROM Table1 Group By IDN
Use that result as join criteria for pulling MAX PCT:
Select IDN, TDATE, MAX(PCT) as TPCT, Last(REG) AS LREG, Last(DIST) as LDIST
FROM Table1 GroupBy IDN, TDATE

Notice rows 2,5,8 are same date and row 2 has different PCT so I need the
MAX(PCT). LREG and LDIST do not seem to always return the correct Row data.
Sometimes row 5 sometimes row 8. How can I be sure to pull all and only row
8. There are too many fields that are variant to Group by or Key on.
Long but hope this is enough information that I may recieve more
understanding of how the First, Last, and Max work and how to return the
correct row.

Tnx all

 

[James A. Fortune]

Hey folks,
Hope to recieve a little help Here. I need to return all the fields of a
record of data where the selection criteria and the aggregation is
questionable. Example:
IDN YRMODAY PCT REG DIST
1 527479999 20021218 71 08 4
2 527479999 20060418 70 09 6
3 527479999 20040318 70 08 4
4 527479999 20041218 70 08 4
5 527479999 20060418 71 07 9
6 527479999 20041218 71 08 4
7 527479999 20040318 71 03 4
8 527479999 20060418 71 08 4

In the above set of records , I need to return the record with the most
recent date (yyyymmdd) <Max> with the highest PCT <Max> and all columns in
that row. In this case it would be Row 8 as the last value of the date
grouping. There are at 30 other columns that may have variant data. I am
currently using a Select query to find the Max date group by the IDN:
Select IDN, MAX(YR&MM&DD) as TDATE FROM Table1 Group By IDN
Use that result as join criteria for pulling MAX PCT:
Select IDN, TDATE, MAX(PCT) as TPCT, Last(REG) AS LREG, Last(DIST) as LDIST
FROM Table1 GroupBy IDN, TDATE

Notice rows 2,5,8 are same date and row 2 has different PCT so I need the
MAX(PCT). LREG and LDIST do not seem to always return the correct Row data.
Sometimes row 5 sometimes row 8. How can I be sure to pull all and only row
8. There are too many fields that are variant to Group by or Key on.
Long but hope this is enough information that I may recieve more
understanding of how the First, Last, and Max work and how to return the
correct row.

Tnx all

It seems that much of the confusion can be resolved by using a subquery
rather than a join to obtain the maximum value. This also makes it
easier to include other fields in the query. Note that with the
formatting you have for YRMODAY, inequalities in YRMODAY values are in
the same direction as the inequalities for the corresponding Date/Time
values.

tblIDN
IID AutoNumber
IDN Text
YRMODAY Long
PCT Long
REG Long, Format:00
DIST Long

IID IDN YRMODAY PCT REG DIST
1 527479999 20021218 71 08 4
2 527479999 20060418 70 09 6
3 527479999 20040318 70 08 4
4 527479999 20041218 70 08 4
5 527479999 20060418 71 07 9
6 527479999 20041218 71 08 4
7 527479999 20040318 71 03 4
8 527479999 20060418 71 08 4

qryIDN:
SELECT TOP 1 IID, IDN, YRMODAY, PCT, REG, DIST FROM tblIDN WHERE YRMODAY
= (SELECT Max(A.YRMODAY) FROM tblIDN AS A) ORDER BY PCT DESC, IID DESC;

!qryIDN:
IID IDN YRMODAY PCT REG DIST
8 527479999 20060418 71 08 4

qryIDN2:
SELECT TOP 1 IID, IDN, YRMODAY, PCT, REG, DIST FROM tblIDN WHERE YRMODAY
= (SELECT Max(A.YRMODAY) FROM tblIDN AS A) ORDER BY PCT DESC, IID;

!qryIDN2:
IID IDN YRMODAY PCT REG DIST
5 527479999 20060418 71 07 9

Without using TOP:

qryIDN3:
SELECT First(tblIDN.IID) AS IID, First(tblIDN.IDN) AS IDN,
tblIDN.YRMODAY, tblIDN.PCT, First(tblIDN.REG) AS REG, First(tblIDN.DIST)
AS DIST
FROM tblIDN
GROUP BY YRMODAY, PCT
HAVING tblIDN.YRMODAY=(SELECT Max(A.YRMODAY) FROM tblIDN AS A) AND
PCT=(SELECT Max(A.PCT) FROM tblIDN AS A WHERE A.YRMODAY = (SELECT
Max(B.YRMODAY) FROM tblIDN AS B));

!qryIDN3:
IID IDN YRMODAY PCT REG DIST
5 527479999 20060418 71 07 9

qryIDN4:
SELECT Last(tblIDN.IID) AS IID, Last(tblIDN.IDN) AS IDN, tblIDN.YRMODAY,
tblIDN.PCT, Last(tblIDN.REG) AS REG, Last(tblIDN.DIST) AS DIST
FROM tblIDN
GROUP BY YRMODAY, PCT
HAVING tblIDN.YRMODAY=(SELECT Max(A.YRMODAY) FROM tblIDN AS A) AND
PCT=(SELECT Max(A.PCT) FROM tblIDN AS A WHERE A.YRMODAY = (SELECT
Max(B.YRMODAY) FROM tblIDN AS B));

!qryIDN4:
IID IDN YRMODAY PCT REG DIST
8 527479999 20060418 71 08 4

The queries using TOP use the ORDER BY fields to impose an order on the
table, which is then used by TOP. For the other queries the inclusion
of the Primary Key, IID, forces Last() to use that sorting.

On my machine, without the IID,

SELECT IDN, YRMODAY, PCT, REG, DIST
FROM tblIDN
WHERE YRMODAY=(SELECT Max(A.YRMODAY) FROM tblIDN AS A) AND PCT=(SELECT
Max(A.PCT) FROM tblIDN AS A WHERE A.YRMODAY = (SELECT Max(B.YRMODAY)
FROM tblIDN AS B)) ORDER BY YRMODAY DESC, PCT;

returns:

IDN YRMODAY PCT REG DIST
527479999 20060418 71 08 4
527479999 20060418 71 07 9

That is, IID 8 gets shown before IID 5 and would cause the addition of
'TOP 1' to display the latter record (IID = 8) first without
distinguishing and eliminating the first record (IID = 5, which would be
the second record shown).

James A. Fortune
(e-mail address removed)

 

[Guest]

James, excellent post. Thanks for the info. Had not thought to use the TOP
designator. Although, the confusion on how the Last operates is still
prevelent the subquery method solves my problem.

Thanks again,

CJ_DB

 

[James A. Fortune]

James, excellent post. Thanks for the info. Had not thought to use the TOP
designator. Although, the confusion on how the Last operates is still
prevelent the subquery method solves my problem.

Thanks again,

CJ_DB

The word 'Last' implies some kind of order. Tables, by definition, do
not have an order. That doesn't mean that Access doesn't give them a
default order since Access has to show "something" first. You can't
really depend too much on Access' default order for a table. If a table
has a primary key, Access will usually use the values of the primary key
to order the records in the table, but you can't depend on that too much
either. Including the primary key usually works and allows for avoiding
using TOP, but the most reliable way to impose an order on records is to
use ORDER BY within a query. Thus, the examples using TOP are not only
cleaner, but more robust than the other queries. However, keep in mind
that TOP is not universally supported and can produce more rows than you
want if a distinguishing field is not used to differentiate ties.

I'm glad TOP with a subquery worked for you.

James A. Fortune
(e-mail address removed)