Automatically adding data between two dates.

  • Thread starter Thread starter FacEngr
  • Start date Start date
F

FacEngr

I need to populate a series of rows between a start date and an end date. I
am trying to analyze a group of monthly gas bills (84 in all) that randomly
start and end on different dates. To complicate things the duration of the
"days billed" change from month-to-month; -Some bills within the same month
can span 26 days while others may be 35 days long.

What I would like to do is populate a querie that has columns for the
meter#, date, and average cost per day. Once populated I could sort out the
date(s) that I need to look at.

I have the data entered in a table that has the meter number, start date,
end date, and Price.

Any help would be appreciated.

-FacEngr
 
What are your fields to start with?

Assumption: Your table has the following four fields
MeterNo
StartDate
EndDate
BillAmount

SELECT [MeterNo], [StartDate], [EndDate], [BillAmount]
, [BillAmount]/(DateDiff("d",[StartDate],[EndDate])+1) as AverageCost
FROM [YourTable]

In query design view
== Add the fields you want to see
== Enter the following in a field rectangle
AverageCost: [BillAmount]/(DateDiff("d",[StartDate],[EndDate])+1)

You add one to DateDiff function since it counts the number of changes to go
from one date to another. So Dec 12 to Dec 15 is 3 changes of the day, but
that is actually 4 days (12, 13, 14, and 15).


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
John,

I can calculate the cost per day, and you are correct about the duration is
n+1 days. What I need is a database that look like so...

Bill Database:
Meter# start end cost #days $/day
a 1/5/10 1/29/10 $25 25 $1
b 1/1/10 1/31/10 $60 30 $2
a 1/30/10 2/25/10 $30 28 $1.01
b 2/1/10 2/28/10 $60 29 $2.01

Data Querie:
Meter# date $/day
a 1/5/10 $1
a 1/6/10 $1
....
a 1/29/10 $1
b 1/1/10 $2
b 1/2/10 $2
....
b 1/30/10 $2
a 1/30/10 $1.01
a 2/1/10 $1.01
....
a 2/25/10 $1.01
b 2/1/10 $2.01
b 2/2/10 $2.01
....
b 2/28/10 $2.01

Ultimately, I am trying to merge the data with a third database. This
vendor used a summarized bill that starts on the first of the month & ends on
the last. By combining parts of the previous (and next) months bills, I will
be able to massage all of the bills to start on the first and end on the
last. The root problem I have is that the read date for one meter may be on
the first of the month, the next meter may be the 18th.

If you need any other information let me know.

-FacEngr


John Spencer said:
What are your fields to start with?

Assumption: Your table has the following four fields
MeterNo
StartDate
EndDate
BillAmount

SELECT [MeterNo], [StartDate], [EndDate], [BillAmount]
, [BillAmount]/(DateDiff("d",[StartDate],[EndDate])+1) as AverageCost
FROM [YourTable]

In query design view
== Add the fields you want to see
== Enter the following in a field rectangle
AverageCost: [BillAmount]/(DateDiff("d",[StartDate],[EndDate])+1)

You add one to DateDiff function since it counts the number of changes to go
from one date to another. So Dec 12 to Dec 15 is 3 changes of the day, but
that is actually 4 days (12, 13, 14, and 15).


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need to populate a series of rows between a start date and an end date. I
am trying to analyze a group of monthly gas bills (84 in all) that randomly
start and end on different dates. To complicate things the duration of the
"days billed" change from month-to-month; -Some bills within the same month
can span 26 days while others may be 35 days long.

What I would like to do is populate a querie that has columns for the
meter#, date, and average cost per day. Once populated I could sort out the
date(s) that I need to look at.

I have the data entered in a table that has the meter number, start date,
end date, and Price.

Any help would be appreciated.

-FacEngr
Click to expand...
.
Click to expand...
 
I need to populate a series of rows between a start date and an end date. I
am trying to analyze a group of monthly gas bills (84 in all) that randomly
start and end on different dates. To complicate things the duration of the
"days billed" change from month-to-month; -Some bills within the same month
can span 26 days while others may be 35 days long.

What I would like to do is populate a querie that has columns for the
meter#, date, and average cost per day. Once populated I could sort out the
date(s) that I need to look at.

I have the data entered in a table that has the meter number, start date,
end date, and Price.

Any help would be appreciated.

-FacEngr
Click to expand...

There's no need to add any data that I can see! You can use an expression such
as

AvgPrice: [Price] / DateDiff("d", [start date], [end date])

to calculate the price per day.
 
Ok, to do that you are going to need an additional table. I have a calendar
table in many of my applications with one record for each date for whatever
time frame I need.

Create a table (CalendarTable) with one field (TheDate)

Then use a routine like the following

Public Sub AddDates(StartDate As DateTime, EndDate as DateTime)
Set DbAny = CurrentDb()
Set rst = DbAny.OpenRecordset("SELECT TheDate FROM CalendarTable" & _
" WHERE TheDate is Null")

With rst
For iCount = 0 To DateDiff("d", StartDate, EndDate)
.AddNew
rst!TheDate = DateAdd("d", iCount, StartDate)
.Update
Next iCount
End With
End Sub

Now you can build a query to get the data you want.

SELECT [Meter#]
, C.[TheDate]
, [Cost]/ (1 + DateDiff("d",[Start],[End])) as DailyCost
FROM CalendarTable as C INNER JOIN Bill as B
ON C.TheDate >= B.Start and C.TheDate <= B.End

If you feel it is absolutely needed, you can use this query to populate a
table that you have created with the needed fields. If you don't know how to
build a query in SQL view, post back and I will try to give you some
instructions on how to do so. The above query can only be built in SQL view.

There is an option that you can build a similar query using no joins and query
criteria.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John,

I can calculate the cost per day, and you are correct about the duration is
n+1 days. What I need is a database that look like so...

Bill Database:
Meter# start end cost #days $/day
a 1/5/10 1/29/10 $25 25 $1
b 1/1/10 1/31/10 $60 30 $2
a 1/30/10 2/25/10 $30 28 $1.01
b 2/1/10 2/28/10 $60 29 $2.01

Data Querie:
Meter# date $/day
a 1/5/10 $1
a 1/6/10 $1
...
a 1/29/10 $1
b 1/1/10 $2
b 1/2/10 $2
...
b 1/30/10 $2
a 1/30/10 $1.01
a 2/1/10 $1.01
...
a 2/25/10 $1.01
b 2/1/10 $2.01
b 2/2/10 $2.01
...
b 2/28/10 $2.01

Ultimately, I am trying to merge the data with a third database. This
vendor used a summarized bill that starts on the first of the month & ends on
the last. By combining parts of the previous (and next) months bills, I will
be able to massage all of the bills to start on the first and end on the
last. The root problem I have is that the read date for one meter may be on
the first of the month, the next meter may be the 18th.

If you need any other information let me know.

-FacEngr


John Spencer said:
What are your fields to start with?

Assumption: Your table has the following four fields
MeterNo
StartDate
EndDate
BillAmount

SELECT [MeterNo], [StartDate], [EndDate], [BillAmount]
, [BillAmount]/(DateDiff("d",[StartDate],[EndDate])+1) as AverageCost
FROM [YourTable]

In query design view
== Add the fields you want to see
== Enter the following in a field rectangle
AverageCost: [BillAmount]/(DateDiff("d",[StartDate],[EndDate])+1)

You add one to DateDiff function since it counts the number of changes to go
from one date to another. So Dec 12 to Dec 15 is 3 changes of the day, but
that is actually 4 days (12, 13, 14, and 15).


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need to populate a series of rows between a start date and an end date. I
am trying to analyze a group of monthly gas bills (84 in all) that randomly
start and end on different dates. To complicate things the duration of the
"days billed" change from month-to-month; -Some bills within the same month
can span 26 days while others may be 35 days long.

What I would like to do is populate a querie that has columns for the
meter#, date, and average cost per day. Once populated I could sort out the
date(s) that I need to look at.

I have the data entered in a table that has the meter number, start date,
end date, and Price.

Any help would be appreciated.

-FacEngr
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top