DLookup/Inner Join to update table with most recent records?

[davidb88]

Hello.
I have 2 tables, tblLHFIedit and tblconchargeoff. tblLHFIedit is a cumulative table that has multiple months of data stored in it and tblconchargeoff is a temporary table that only stores the most current month's data.

I am looking to create an update query to update tblconchargeoff with a field from tblLHFIedit, UPB, with the most recent date. There may be multiple months where the same record will appear in tblLHFIedit, but I want to pull in the value that comes from the most recent month. Is there any way to do this? I tried creating a query but I am not sure if it is pulling in the most recent value or which date it is pulling from.

Any help with edits to the following query would be appreciated! Thanks so much!

'Add UPB to tblconchargeoff
strSQL = "UPDATE tblconchargeoff INNER JOIN tblLHFIedit ON (tblconchargeoff.[Loan Number] = tblLHFIedit.[Loan Number]) " _
        & "AND (tblconchargeoff.Month = tblLHFIedit.[Month]) SET tblconchargeoff.[UPB] = [tblLHFIedit].[Total Current];"
DoCmd.RunSQL (strSQL)

 

[xenou]

If conchargeoff is the current month and you join month to month you'll get an update to the value of the current month in LHFIedit.

Is there only one record for a loan number in a month? I.e.,

SELECT [Loan Number], [Month] FROM tblLHFIedit

The above is one record, right?

 

[davidb88]

Hi Xenou,

Thanks for pointing out that I was pulling in only the value of the current month. There should only be one record number per loan number per month. Is there any way to edit my current query so it pulls in the most recent month where that loan number appears?

Thanks for your help!

 

[xenou]

Hi,
Sorry I got a little busy for a few hours.

This is a little tricky since we need to get the Max() of the month date in the source table, but once we use an aggregate query we can't run an update query. So we have to step it out into a little "procedure".

*FIRST, we need a temp table. So create a table called XTMP00100 (or something else if you like, but makes the same changes in the SQL too). The table should have an autonumber field called ID, a field called [Loan Number], and a field called MaxOfMonth. If there are many loans (thousands), it would not be a bad idea to index the Loan Number column. If you prefer not to use my arbitrary ID column as a primary key, you may use [Loan Number] as a primary key (which indexes the column automatically).

Then we run a query to truncate the table (delete all rows):

DELETE * FROM XTMP00100

Then we run a query to populate it with the loan numbers and max months:

INSERT INTO XTMP00100
SELECT t.[Loan Number], 
        Max(t.[Month]) AS MaxOfMonth
FROM tblLHFIedit t
GROUP BY t.[Loan Number]

Now we are set to run the update we need, including this temp table in the join. Access is a stickler about parentheses with multiple inner joins, so be careful to use the parentheses as shown:

UPDATE 
    ((tblconchargeoff t1
INNER JOIN XTMP00100 t2 ON 
        t1.[Loan Number] = t2.[Loan Number])
INNER JOIN tblLHFIedit t3 ON
        t2.[Loan Number] = t3.[Loan Number]
        AND t2.[MaxOfMonth] = t3.[Month])
SET
    t1.[UPB] = t3.[Total Current]

Naturally it is convenient to kick this off as a script:

[COLOR="Navy"]Sub[/COLOR] foo()
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]


[COLOR="SeaGreen"]'//Truncate Temp Table[/COLOR]
s = s & " DELETE * FROM XTMP00100"
DoCmd.SetWarnings False
DoCmd.RunSQL s
DoCmd.SetWarnings True

[COLOR="SeaGreen"]'//Store Max month for loans in temp table[/COLOR]
s = ""
s = s & " INSERT INTO XTMP00100"
s = s & " SELECT t.[Loan Number], "
s = s & "         Max(t.[Month]) AS MaxOfMonth"
s = s & " FROM tblLHFIedit t"
s = s & " GROUP BY t.[Loan Number]"
DoCmd.SetWarnings False
DoCmd.RunSQL s
DoCmd.SetWarnings True

[COLOR="SeaGreen"]'//Update UPB in tblconchargeoff[/COLOR]
s = ""
s = s & " UPDATE "
s = s & "     ((tblconchargeoff t1"
s = s & " INNER JOIN XTMP00100 t2 ON "
s = s & "         t1.[Loan Number] = t2.[Loan Number])"
s = s & " INNER JOIN tblLHFIedit t3 ON"
s = s & "         t2.[Loan Number] = t3.[Loan Number]"
s = s & "         AND t2.[MaxOfMonth] = t3.[Month])"
s = s & " SET"
s = s & "     t1.[UPB] = t3.[Total Current]"
DoCmd.SetWarnings False
DoCmd.RunSQL s
DoCmd.SetWarnings True


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Note that I like to use XTMP###### as my temp table names since that way they are all grouped together at the end of my table list (unless of course I create a table name that starts with Y or Z). Also BTW some people might prefer to use a make table query and not really keep a temp table. My preference is to have the temp table, with one advantage being that you can set your indexes for optimal performance - fields that are included in your joins and where clauses are good candidates for indexing.

 

[davidb88]

Thanks Xenou! This worked!