Need help/ Most Recent showing Distinct records based on 1 other field 1

[dkmansion]

Hello all,

I hope I got all the detail necessary in this...

I am tracking cellular devices in our Org. There are multiple records in some cases for an individual, because of upgrades, lost devices, service changes, etc.

I need to see only the most recent record (either by choosing the highest pKey or by Date, doesn't matter to me at this time) for each Cellular# or User in the table.

I have queries filtering my data for other fields but when I export the data so send to others I have to delete the duplicate lines for the recurring names.

Details:
db = Access 2000
Main Table = [green]tblCellularRec[/green]
pKey = [blue]cellRecID[/blue]
fKey User = [blue]UserID[/blue]
Cellular# = [blue]Cellular10Digit[/blue] = stores the cellular phone number
Date1 = [blue]OrderDate[/blue] = date order placed with vendor
date2 = [blue]ActivationDate[/blue] = date device actually went into service (though maybe not the date the phone number actually started service)
fkey2 - 50 = foreign keys for storing ID links to department, equipmenttype, etc.
Support Tables = tons...

Table Data
tblcellularrec
cellrecid  userid  cellular10digit   orderdate  activationdate
1          1       1111111111        1/1/2001   1/4/2001
2          1       1111111112        1/1/2001   1/3/2001
3          2       2222222221        2/1/2001   2/3/2001
4          1       1111111112        2/2/2001   2/6/2001
5          3       3333333331        3/1/2001   3/5/2001
6          3       3333333331        4/1/2001   4/2/2001
7          4       4444444441        4/4/2001   4/6/2001
8          1       1111111111        4/4/2001   4/6/2001
9          2       2222222221        4/6/2001   4/8/2001
10         2       2222222222        4/6/2001   4/8/2001

Getting the output of all records and merging with the Descriptions from the related support tables for tblCellularRec.fKey to tblForeign.pKey is great.

I'm pretty sure the combination will be something [highlight]nested using Top and Distinct in the SQL?[/highlight], (I have been browsing other posts asking this kind of thing with no solution yet)


[green]
qryDistinctMostRecent

SELECT tblCellularRec.cellrecid, tblCellularRec.userid, tblCellularRec.cellular10digit, tblCellularRec.orderdate, tblCellularRec.activationdate
FROM tblCellularRec;

...Returns All Records

The query i need would return
cellrecid userid cellular10digit orderdate activationdate
4 1 1111111112 2/2/2001 2/6/2001
6 3 3333333331 4/1/2001 4/2/2001
7 4 4444444441 4/4/2001 4/6/2001
8 1 1111111111 4/4/2001 4/6/2001
9 2 2222222221 4/6/2001 4/8/2001
10 2 2222222222 4/6/2001 4/8/2001
[/green]

Any help that could be provided would be greatly appreciated.



Donald M

 

[PHV]

SELECT A.cellrecid, A.userid, A.cellular10digit, A.orderdate, A.activationdate
FROM tblCellularRec A INNER JOIN (
SELECT cellular10digit, Max(activationdate) AS LastDate
FROM tblCellularRec GROUP BY cellular10digit
) B ON A.cellular10digit=B.cellular10digit AND A.activationdate=B.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dkmansion]

PHV, Thanks this a perfect example to work with. Much appreciated. dm

Donald M