Most recent update query   

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.

date: Thu, 04 Sep 2008 07:52:02 -0700   author:   marshall remmele

RE: Most recent update query   

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.



"marshall remmele" wrote:

> 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.
>

date: Thu, 4 Sep 2008 08:12:01 -0700   author:   Dale Fye