Most recent update query

[marshall remmele]

I have a database where table A stores issues, and table B stores all the updates to those issues. Table B also has the pk from table A to maintain the connection. Table A and B have autonumbers for their pk.

I am trying to build a query that will only pull the most recent update from table B for every issue in table A.

Hopefully I have provided enough details, thank in advance.

 

[Dale Fye]

Assuming that you have a date/time (EntryDate) field associated with each
record in table B, you could do something like:

Select A.*, B.Issue
FROM tableA as A
INNER JOIN tableB as B
ON A.ID = B.A_ID
WHERE B.EntryDate = DMAX("EntryDate", "tableB", "[A_ID] = " & A.ID)

Basically, this says select all the records from A, matched against their
associated records in B where the EntryDate field in B matches the maximum
EntryDate in B, for any given Issue in Table A.

If you don't have a date field in Table B, you could probably use the
autonumber field from B in the Where clause, although I don't think there is
any guarantee that an Autonumber field is unique and ever increasing.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.