CODEWITHSUNDEEP
sqlms-access
Fernando Gallego Fernández
Fernando Gallego Fernández

Reputation: 456

Last entry of a history table with MS-Access

I developed a database with Access with the next architecture:

First table:

  1. Identifier
  2. Serial number
  3. Revision (Yes/No)

And the second table, linked to the first one by their identifiers, that it's being used like a history, keeping some events with his responsible:

  1. Identifier
  2. Date event
  3. Responsible event
  4. Event description

So, I'm trying to make a SQL query that brings me the last event in the history table for a determined identifier by this way:

SELECT M.ID, M.SN, Last(H.Date) AS MaxDate, Last(H.Responsible) AS LastResponsible, 
Last(H.Description) AS LastDescription
FROM TABLE1 AS M INNER JOIN History AS H ON M.ID=H.ID
WHERE M.Disponible=No
GROUP BY M.ID, M.SN;

But it's bringing to me sometimes values in history that are not the last ones, why could be the reason?

Upvotes: 0

Views: 103

Answers (1)

Andre
Andre

Reputation: 27634

You need to first group History to find the Max entries. Make a subquery of this, and then JOIN the subquery with TABLE1.

See this question for a good example:
Is it better / more efficient to use sub queries or SELECT statements within the WHERE clause (in MS Access)
(you want the "combined as a single query" variant).

If you can't get it to work, add a comment.

Upvotes: 1

Related Questions