Show and populate full date range

[CSBLUE]

Hi – hoping someone can help..


I have a monthly system data report I wish to manipulate in Access.


I have created a table to match the 3 fields in the data report; the date, widget ID and number of widgets made.


Widgets are made every day (including weekends and bank holidays) but there will only be a data entry on days where the number of widgets made changes from the previous day ie if Widget ID 33 was made 5 times on 05 Oct, 06 Oct, 07 Oct but on 8 Oct there were 7 made, the data would look like…


Date.................Widget ID..................Number Made
05 Oct ..................33...............................5
08 Oct ..................33...............................7


I would like to write a query that shows the data for every day, duplicating the data for the previous day in the instance the same number is made. The output should look like..

Date.................Widget ID..................Number Made
05 Oct ..................33...............................5
06 Oct ..................33...............................5
07 Oct ..................33...............................5
08 Oct ..................33...............................7

The data is monthly. There are so many different widgets on the report that it’s pretty much guaranteed there will be at least one entry on the first date and the last date in order for a query to find the full date range – as opposed to a user having to manually enter the date range.


Using the example above where the first entry is on 05 Oct it should not be assumed that 01-04 Oct was zero, the query should then look for last entry in the previous month’s data stored in the database (I realise the first time I do this some form of manual adjustment would have to be made as no previous data would exist).


I hope I have explained myself well. It seems a fairly straightforward request in my head – but after several frustrating days I am more lost than ever!

Any advice is greatly appreciated - thank you

 

[plog]

First, 'Date' is a bad name for a field because it is a reserved word in Access and will cause you issues when running queries and code. I would rename it by prefixing what date it represents (i.e. ProductionDate). Also, spaces in table and field names are a pain to work with ([Number Made] = [NumberMade]).

For your issue, the biggest hurdle you have is that queries can't create data, they can only show what exists in their underlying data sources. So, you need a data source that shows every day you want to report on. Create a table that lists all the days. Once you have that you can LEFT JOIN your existing table to it and show all dates from your table of dates. That gets you a query that shows all dates. Of course some of those dates will have blank values.

To overcome that you will need a VBA function. You will pass the date and the number made to the function. In a query it would look like this:

Qty: get_Qty([ProductionDate], [NumberMade])

The function would return the correct quantity for that date. If NumberMade is null (which it will be for dates not in your data) it will find the date of the last time something is actually in your data and return the [Number Made] on that date. If NumberMade has data, it will just return that.

That's the broad strokes. If you need specific help with the function--first give it a shot then post back here any specific questions.