Filling in date gaps in Price table (1 Viewer)

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

Sanjay_S said:

In the attached ACCESS Db, ...

Click to expand...

There is no database attached.
When you attached it, tell the name of the query you use.

JHB said:

There is no database attached.
When you attached it, tell the name of the query you use.

Click to expand...

The database is attached. The query is NAV_Top_1

Sanjay

Run query3 in the attached database, (the problem is the Product_code, therefore you first need a query to pick them out).

Cross posted http://www.utteraccess.com/forum/index.php?showtopic=2048147&hl=

Please read

Take a look ar qryFinal

Sanjay_S said:

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.

Click to expand...

This type of arrangement works well in a spread sheet, but is not the best for use in a database. You would normally save when the price is effective as of, rather than having a "Daily price". This means that your data would look like this:

01-Feb-18 120MSGP 51.78
02-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
12-Feb-18 120MSGP 50.56
14-Feb-18 120MSGP 50.55
15-Feb-18 120MSGP 50.1
16-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
26-Feb-18 120MSGP 49.4
27-Feb-18 120MSGP 49.44
28-Feb-18 120MSGP 49.52

You could then join your table to an alias of itself to determine the difference in dates to figure out how many days each price was good for. When looking for "Todays" price you simply look for the entry with the latest date. This also avoids having redundant data as you only save changes.