Query Help/Ideas

[cstimart]

I have a table with thousands of records that will have multiple entries for each item. I want to query the table to display the most current record for each item. Is that possible?

for example, among all 65,000 entries, I have 13 entries for ItemID 12334, and each record has a different date/time. I'd want to display/extract the most recent record for each item....including ItemID 12334 with DateID of 1/13/2014 12:50 PM

 

[xenou]

Yes but you need a way to define "most current" in terms of something in the data. In absence of anything else, a timestamp of some kind perhaps.
ξ

 

[cstimart]

Yes but you need a way to define "most current" in terms of something in the data. In absence of anything else, a timestamp of some kind perhaps.
ξ

I had just updated my post. Does that help/clarify?

 

[xenou]

Hi,
Okay I see what you mean (sorry didn't realize at first we were almost real time).

For this I would (by preference) write two queries:

Query 1:

SELECT Table1.ItemID, Max(Table1.DateID) AS MaxOfDateID
FROM Table1
GROUP BY Table1.ItemID;

Query 2:

SELECT Table1.ItemID, Table1.DateID, Table1.OtherField
FROM Table1 INNER JOIN Query1 
ON (Table1.DateID = Query1.MaxOfDateID) AND (Table1.ItemID = Query1.ItemID);

They can be mixed into one query if you prefer (I just ordinarily "stack" my queries with MSAccess so I'm used to the design structure of query1 queries query 2 queries query 3 and so on (I would probably name them qry_10000a_xxxxxxx, qry10000b_yyyyyyy, qry_10000c_zzzzzzz so that the relationship is also indicated in the naming scheme). So, as an alternative, using an all-in-one query instead:

SELECT t1.ItemID, t1.DateID, t1.OtherField
FROM Table1 t1 INNER JOIN
(SELECT ItemID, Max(DateID) As MaxOfDateID FROM Table1 Group By ItemID) As t2
ON t1.ItemID = t2.ItemID
AND t1.DateID = t2.MaxOfDateID

The query on query design sometimes performs a little better with the default desktop engine (Jet or ACE), while the all-in-one query with subquery design is fine if you have other database engines backing up the database (i.e., SQL Server).

 

[cstimart]

Thanks! This helps a lot.