Getting Latest Entered Record ID using a Query

  • Thread starter Thread starter Steve
  • Start date Start date
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
 
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
 
Hi,

Thanks for the reply, and yes I did try that and had some
success. But some important information was left out in
the post since I didn't think it relevant (stupid stupid
stupid!).

So,

Table 1 (lets call it Q_1)is a Query which produces
information as per Table 1

Real Table 2 (lets call it CCS_T) has some other fields
which we want to match against.

So info in Q_1 is...(although many more columns, about 30)
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

Table CCS_1

RecID sPortfol sComment
50 sxport1 something comment wise
50 sxport2 something comment wise
50 sxport3 something comment wise
100 sxport1 something comment wise
100 sxport3 something comment wise
100 sxport1 something comment wise
150 sxport1 something comment wise
150 sxport1 something comment wise
170 sxport1 something comment wise

Workign syntaxt for query is I just pull off the info from
Q_1 is....

SELECT * from Q_1 where RecID in (select max( CT1.RecID)
from Q_1 CT1
group by CT1.diTrID)

This works a treat but when I try to incorporate the
innner joins for CCS_1 Access hangs (at least 15 mins so
far so I just End Tasked it)

New statement is.....

Select * from Q_1 LEFT JOIN CCS_1 ON
(Q_1.sPort=CCS_1.sPortf) AND (Q_1.RecId=CCS_1.RecId)
WHERE Q_1.RecID in (select max( CT1.RecID) from Q_1 CT1
group by CT1.diTrID);

Does this make sense ?

If so can you help ?
 
Hi,


I would try, if possible,

Select * from (
SELECT *
from Q_1
where RecID in (select max( CT1.RecID)
from Q_1 CT1
group by CT1.diTrID)
) As x LEFT JOIN CCS_1
ON (x.sPort=CCS_1.sPortf) AND (x.RecId=CCS_1.RecId)


which is nothing more than

SELECT * FROM (oldQuery) As x LEFT JOIN ...



Hoping it may help,
Vanderghast, Access MVP
 
Hi,

Many thanks for your help. I have now got it working using
your solution. Problem was when I introduced an IIF
statement for the CCS_1 table and incorrectly placed in
the wrong part of the query.

Thanks again

Steve
 
Back
Top