Getting Latest Entered Record ID using a Query

S

Steve

Hi, Hope someone can help!!!!

I have a table with some sample data in ...outlined below

Table1
RecID diTrID Inf1 Inf2 sPort
50 100050 something1 something2 sxport1
50 100050 ano_inf1 anoinf1 sxport2
50 100050 ano_inf2 anoinf2 sxport3
100 100060 ano_inf3 anoinf3 sxport1
100 100060 ano_inf4 anoinf4 sxport3
150 100050 some_1 some_2 sxport1
150 100050 ano_inf1 anoinf1 sxport2
150 100050 ano_inf2 anoinf2 sxport3
170 100060 ano_inf77 anoinf3 sxport1

I am trying to write a query which would retrieve the
records that have the latest RecID for each diTrID for
each sPort

So from the example I would like the output to be
OUTPUT
RecID diTrID Inf1 Inf2 sPort
100 100060 ano_inf4 anoinf4 sxport3
150 100050 some_1 some_2 sxport1
150 100050 ano_inf1 anoinf1 sxport2
150 100050 ano_inf2 anoinf2 sxport3
170 100060 ano_inf77 anoinf3 sxport1

Can someone please help as Ive been trying to get this
working on and off for the last week!!

Any help would be much appreciated.

S
 
M

Michel Walsh

Hi,

have you tried one of the four methods illustrated at
http://www.mvps.org/access/queries/qry0020.htm. Your "groups" are actually
made of two fields, rather than just one (as in the examples, where the only
field is "BookID"), and you use RecID rather than a "dateOut", so, if I
translate the method number 3:


SELECT Q.diTrID, Q.sPort, Q.RecID, FIRST(Inf1), FIRST(inf2)
FROM tableName As Q INNER JOIN TableName As T
ON Q.diTrID=T.diTrID AND Q.sPort=T.sPort
GROUP BY Q.diTrID, Q.sPort, Q.RecID
HAVING Q.RecID=MAX(T.RecID)

Hoping it may help,
Vanderghast, Access MVP
 
Top