In the attached ACCESS Db, I have a price table (NAV) with fields [Product Code], that indicates the product, [NAV Date] that indicates the price date, and [NAV] that indicates the price per unit for that product for that day.
This table has a few gaps, largely because in many products (I have used product with code "120MSGP" as an illustration), there are no prices declared for Saturdays, Sundays and market holidays.
So, for 120MSGP, there are only prices declared for 19 out of 28 days in February.
My problem is to ensure that there is a price available for every day in February; this is necessary to calculate a full-month average. So, for example, if [NAV] is missing for dates 03 Feb and 04 Feb, then we just take the value of the immediate preceding business day, 02 Feb, and put that in.
While this is easy to do in excel, it has defeated my MS ACCESS abilities.
I tried this with a SELECT TOP 1 subquery, but it is giving me an "At most one record can be returned by this subquery" message, as can be seen by running the query.
What I want is the following result, when I run this query for any code, like 120MSGP:
Date Product_Code NAV
01-Feb-18 120MSGP 51.78
02-Feb-18 120MSGP 50.13
03-Feb-18 120MSGP 50.13
04-Feb-18 120MSGP 50.13
05-Feb-18 120MSGP 49.87
06-Feb-18 120MSGP 49.23
07-Feb-18 120MSGP 49.5
08-Feb-18 120MSGP 49.99
09-Feb-18 120MSGP 49.83
10-Feb-18 120MSGP 49.83
11-Feb-18 120MSGP 49.83
12-Feb-18 120MSGP 50.56
13-Feb-18 120MSGP 50.56
14-Feb-18 120MSGP 50.55
15-Feb-18 120MSGP 50.1
16-Feb-18 120MSGP 49.3
17-Feb-18 120MSGP 49.3
18-Feb-18 120MSGP 49.3
19-Feb-18 120MSGP 48.89
20-Feb-18 120MSGP 48.83
21-Feb-18 120MSGP 48.68
22-Feb-18 120MSGP 48.49
23-Feb-18 120MSGP 49.14
24-Feb-18 120MSGP 49.14
25-Feb-18 120MSGP 49.14
26-Feb-18 120MSGP 49.4
27-Feb-18 120MSGP 49.44
28-Feb-18 120MSGP 49.52
Beyond this, I would also request input on how I can dynamically calculate the sum of NAVs between any two dates within this month; these dates would be in another transaction table.
Thanks a ton,
Sanjay
This table has a few gaps, largely because in many products (I have used product with code "120MSGP" as an illustration), there are no prices declared for Saturdays, Sundays and market holidays.
So, for 120MSGP, there are only prices declared for 19 out of 28 days in February.
My problem is to ensure that there is a price available for every day in February; this is necessary to calculate a full-month average. So, for example, if [NAV] is missing for dates 03 Feb and 04 Feb, then we just take the value of the immediate preceding business day, 02 Feb, and put that in.
While this is easy to do in excel, it has defeated my MS ACCESS abilities.
I tried this with a SELECT TOP 1 subquery, but it is giving me an "At most one record can be returned by this subquery" message, as can be seen by running the query.
What I want is the following result, when I run this query for any code, like 120MSGP:
Date Product_Code NAV
01-Feb-18 120MSGP 51.78
02-Feb-18 120MSGP 50.13
03-Feb-18 120MSGP 50.13
04-Feb-18 120MSGP 50.13
05-Feb-18 120MSGP 49.87
06-Feb-18 120MSGP 49.23
07-Feb-18 120MSGP 49.5
08-Feb-18 120MSGP 49.99
09-Feb-18 120MSGP 49.83
10-Feb-18 120MSGP 49.83
11-Feb-18 120MSGP 49.83
12-Feb-18 120MSGP 50.56
13-Feb-18 120MSGP 50.56
14-Feb-18 120MSGP 50.55
15-Feb-18 120MSGP 50.1
16-Feb-18 120MSGP 49.3
17-Feb-18 120MSGP 49.3
18-Feb-18 120MSGP 49.3
19-Feb-18 120MSGP 48.89
20-Feb-18 120MSGP 48.83
21-Feb-18 120MSGP 48.68
22-Feb-18 120MSGP 48.49
23-Feb-18 120MSGP 49.14
24-Feb-18 120MSGP 49.14
25-Feb-18 120MSGP 49.14
26-Feb-18 120MSGP 49.4
27-Feb-18 120MSGP 49.44
28-Feb-18 120MSGP 49.52
Beyond this, I would also request input on how I can dynamically calculate the sum of NAVs between any two dates within this month; these dates would be in another transaction table.
Thanks a ton,
Sanjay