Summing a field in one table based on dates in another table (1 Viewer)

[Sanjay_S]

I have two tables, PASTMONTH, which has all dates of the last month (in this case, July 2017) and ALLFUNDS_NAV, which has the net asset values (NAV) of two funds, since their inception.

I have attached a database (Access 2013) with these two tables, and the query described below, for reference.

I need to find the cumulative NAV for each fund, for each date; normally this could be done with a

sum(iif([NAV Date]<=Date,[NAV],0))

formula, but the problem here is that one fund, FIFCAPDP, has only 21 days of NAV for the month. The series in the ALLFUNDS_NAV table starts from 3rd July, whereas the month starts from July 1.

For July 1 and 2, we would need to capture the NAV of the closest earlier date, which would be June 30th. Once this is done, we can then do a cumulative sum date-wise.

I have not been able to do this; I experimented with pulling in the closest earlier date from the ALLFUNDS_NAV table through a DMAX query, with a condition that the set of dates considered from the ALLFUNDS_NAV table be less than or equal to the current date field value. This gives nonsensical results for some dates, and correct results for other dates.

The query is given below:

SELECT DISTINCT PastMonth.Date, AllFunds_NAV.Code, DMax("[NAV Date]","[AllFunds_NAV]","[AllFunds_NAV].

='" & [Code] & "' and [AllFunds_NAV].[NAV Date]<=#" & [Date] & "#") AS CNAV
FROM PastMonth, AllFunds_NAV
GROUP BY PastMonth.Date, AllFunds_NAV.Code, AllFunds_NAV.Code, PastMonth.Date
ORDER BY AllFunds_NAV.Code, PastMonth.Date;[CODE]

This is not even the end result; I need to have a date wise cumulative NAV for each product code (e.g. For FIFCAPDP, the NAV for July 1 and 2 would be the June 30 NAV (17.2006), as these dates are missing in the ALLFUNDS_NAV file. So, cumulative NAV for July 1 would be 17.2006 and for July 2 it would be 34.4012.

Any help in this problem would be appreciated.

 

[isladogs]

I experimented with pulling in the closest earlier date from the ALLFUNDS_NAV table through a DMAX query, with a condition that the set of dates considered from the ALLFUNDS_NAV table be less than or equal to the current date field value. This gives nonsensical results for some dates, and correct results for other dates.

The dates used in DMax section of query need to be modified to use mm/dd/yyyy format
Change your query to the following:

SELECT DISTINCT PastMonth.Date, AllFunds_NAV.Code, DMax("[NAV Date]","[AllFunds_NAV]","[AllFunds_NAV].

='" & [Code] & "' AND [AllFunds_NAV].[NAV Date]<=#" & Format([Date],"mm/dd/yyyy") & "#") AS CNAV
FROM PastMonth, AllFunds_NAV
GROUP BY PastMonth.Date, AllFunds_NAV.Code, AllFunds_NAV.Code, PastMonth.Date
ORDER BY AllFunds_NAV.Code, PastMonth.Date;[/QUOTE]

NOTE: Using fields called Code isn't a good idea.
I had to enclose the code in quote marks to deal with that.

Hopefully you can use this to do the rest of your task to get the 'end result'

 

[Sanjay_S]

Thank you, Colin. This seems to work. I will run it on the full table, and then start groping forward to the 'end result'!

Sanjay