I'm using MS-Access 2007 with linked MS-SQL db with 2 tables.
One table is the inventory details, the other is the inventory's track, tracking each inventory's status (by ID) on a specific date.
I need to retrieve the TrackID
for each InvenotryID
by the max Date value, so I can get the latest status of each inventory item.
Inventory table: ID, Details, etc.
Track table: ID, Inventory (ID), TDate, Status, Branch.
Track table example:
ID | Inv. | TDate | Status
332| 4 | 02/03 | free
342| 6 | 02/12 | working
346| 4 | 02/09 | working
347| 7 | 02/11 | repairs
349| 5 | 02/05 | repairs
352| 6 | 02/13 | free
355| 5 | 01/28 | working
356| 7 | 02/14 | free
the query should fetch:
TrackID | Inv. | TDate | Status
346 | 4 | 02/09 | working
355 | 6 | 02/13 | free
356 | 7 | 02/14 | free
349 | 5 | 02/05 | repairs
note: TrackID
is not necessarily the maximum value of the field due to non linear status registrations.
Due to my lack on SQL, I couldn't create a query that can manifest that idea.
Perhaps:
SELECT test.ID, test.Inv, test.TDate, test.Status
FROM test INNER JOIN
(SELECT test.Inv, Max(test.TDate) AS MaxOfTDate
FROM test
GROUP BY test.Inv) AS q
ON (test.TDate = q.MaxOfTDate) AND (test.Inv = q.Inv)
GROUP BY test.ID, test.Inv, test.TDate, test.Status;
Where test is the name of your table.