# Thread: populate a year from date ranges (2000 SR 1)

1. ## populate a year from date ranges (2000 SR 1)

I have records like this:

Fields and value

record 1:

identifier: 1001
from_date: 1 Feb 2001
to_date: 25 Feb 2001
value: 300
Owner: ABC

record 2;

identifier: 1001
from_date: 1Mar 2001
to_date: 31 Mar 2001
value: 450
owner: XYZ

There are numerous records for each identifier and around 10,000 records in total.

I need to produce a query that will show how many days in the year (and what they are) that do not have any data.

So for the above example: 3 days missing, 26-28th Feb.

Then calculate an estimate for the missing data based on the average of the data available for each identifier

so (450+300)/(25+31 days) = 13.4 per day * 3 days = 40.2

I would like to be able to rank each identifier by the number of days missing in a time period (between mar 2000 and Sept 2002 or just 1 year) and list the owner prior to the missing period (ie ABC)

2. ## Re: populate a year from date ranges (2000 SR 1)

Simon, try this.
First copy this Function to a new Module and name it anything but DaysInMonth

Function DaysInMonth(dteInput As Date) As Integer
Dim intDays As Integer
' Add one month, subtract dates to find difference.
intDays = DateSerial(Year(dteInput), Month(dteInput) + 1, Day(dteInput)) _
- DateSerial(Year(dteInput), Month(dteInput), Day(dteInput))
DaysInMonth = intDays
End Function

Then create a query I'll call Query1
In Query1, you are going to add the Table that has your records in it. Then add the fields
identifier and value
TotalDaysInMonthaysInMonth(from_date)
TotalDaysWithDataateDiff("d",from_date,to_date)
Then turn on the Totals and Groupby identifier, Sum value, Sum TotalDaysInMonth, and Sum TotalDaysWithData.
Now create another query
Bring all the fields down to the Field line
MissingDataCalculationSumOfValue/SumOfTotalDaysWithData)*(SumOfTotalDaysInMonth - SumOfTotalDaysWithData)

That should give you the first part of what you want. Now about ranking, I'm not entirely sure if it can be done. I don't have any data readily available to test it on. Post back if you have problems with the first part.
Paul

3. ## Re: populate a year from date ranges (2000 SR 1)

<hr>First copy this Function to a new Module and name it anything but DaysInMonth<hr>
Just to clarify, he means you should name the module anything but DaysInMonth. The name of the function is OK.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•