Find last observation for record set (VBA)

[fboehlandt]

Hi everyone,
I have two table named 'Information' and 'Performance'. The first one contains three fields: the name of an investment fund, a unique code, and the investment strategy of said fund:

[Fundname] [Fund_ID] [Strategy]

The latter table is a normalized table containing the performance (monthly returns) for each fund contained in table 'Information'. The primary and foreign key would be [Fund_ID] in 'Information' and 'Performance', respectively. Not all funds have the same start or end date for performance observations. Empty records for specific dates are not included in the normalized table:

[Fund_ID] [Date1] [Return1]
[Fund_ID] [Date2] [Return2]
[Fund_ID] [Date3] [Return3]
...

I need the date of the last observation for each fund in the database. Then I would like to recreate the table 'Information' (or create a table 'Information2') as follows:

[Fundname] [Fund_ID] [Strategy] [Last_Date]

I understand that I need to rotate through the observations for each fund using VBA but I don't know how to do it. Can anyone help please?

[orange]

Your Performance Table is not normalized if, as you said, FundId is the primary key. You would have duplicates if you had a number of observations for each Fund.

I mocked up you situation to show the SQL to get the data you need from your tables.

My Tables are as in attached jpg.

The query to select the data is


SELECT VBAXInformation.FundName
, VBAXInformation.FundId
, Max(VBAXPerformance.Datex) AS MaxOfDatex
, VBAXInformation.Strategy
FROM VBAXInformation
INNER JOIN VBAXPerformance
ON VBAXInformation.FundId = VBAXPerformance.FundId
GROUP BY
VBAXInformation.FundName
, VBAXInformation.FundId
, VBAXInformation.Strategy;

[fboehlandt]

It was my understanding that if the data was stacked and Fund_ID is the foreign key in table 'Performance', the table would be normalized. This is how I received the data. I don't know what changes would have to be made to display the performance optimally with respect to database design. The table looks as follows:

[Fund1] [Code1] [Date1] [Return1]
[Fund1] [Code1] [Date2] [Return2]
[Fund1] [Code1] [Date3] [Return3]
...
[Fund1] [Code1] [DateN1] [ReturnN1]
[Fund2] [Code1] [Date1] [Return1]
[Fund2] [Code2] [Date2] [Return2]
[Fund2] [Code2] [Date3] [Return3]
...
[Fund2] [Code2] [DateN2] [ReturnN2]
...
[FundM] [CodeM] [Date1] [Return1]
[FundM] [CodeM] [Date2] [Return2]
[FundM] [CodeM] [Date3] [Return3]
...
[FundM] [CodeM] [DateNM] [ReturnNM]

where M is the total number of funds and Nk denotes the number of observations per fund. I have the following VBA script:

Sub LastDate()

Dim db As DAO.Database, hfrdold As DAO.Recordset, hfrdnew As DAO.Recordset, hfrdret As DAO.Recordset, i As Integer
Dim MyCode As Integer, Mydate As Variant

Set db = CurrentDb
Set hfrdold = db.OpenRecordset("Information")
Set hfrdnew = db.OpenRecordset("Information2")
Set hfrdret = db.OpenRecordset("Performance")

With hfrdret
.MoveFirst
MyCode = ![Code]
Do While Not .EOF
If ![Code] <> MyCode Then
hfrdnew.AddNew
hfrdnew![Fund] = MyFund
hfrdnew![Code] = MyCode
hfrdnew![End_Date] = Mydate
hfrdnew.Update
End If
MyFund = ![Fund]
MyCode = ![Code]
Mydate = ![Date]
.MoveNext
Loop
End With

hfrdold.Close
hfrdnew.Close
hfrdret.Close
Set hfrdold = Nothing
Set hfrdnew = Nothing
Set hfrdret = Nothing

End Sub

Unfortunatley, I doesn't quite seem to work. It yields some duplicates (i.e. doesn't recognize the last date), albeit for very few funds. This really stumps me! I will try your SQL suggestion and get back to you when I have had a chance to run it. Thanks for the response

[fboehlandt]

Never mind,
I find that the Inner Join query works fine for my purposes, so no worry. Many thanks again for your input. It is greatly appreciated...

[orange]

Glad you got it working. Just curious though, when you look at your Access tables, what fields are indexed. Do you really have primary keys?

[fboehlandt]

The way I see it is that any field providing a unique value for each record is suitable to be a primary key. In this case, you may assume that the FundID in table 'Information' works like an autonumber (it is a randomly created, distinct number). I confirmed that by running a query to identify duplicates based on FundID (none). Different story entirely when it comes to the table 'Performance'. Here, FundID is clearly not unique (multiple observations per fund or 1 : n) and acts as a foreign key to relate the return observations contained here to the fund details in 'Information'. The table has no designated primary key as there was no need. A joint primary key could be achieved via FundID&Date (or simply use an autonumber). I'm not sure that answers your question? Thanks for your help. I will post snapshots here if there is still confusion...

[orange]

Thanks for the update.
When I saw your "The latter table is a normalized table containing the performance (monthly returns) for each fund contained in table 'Information'. The primary and foreign key would be [Fund_ID] in 'Information' and 'Performance', respectively." in you earlier post, I knew you could not have Fund_Id as primary key, and thus Performance was not normalized.

In my sample, I did make FundId and Datex a composite Primary key on Table VBAXPerformance.

Glad you have it working.