maximyshka
asked on
Find data for each day between the two dates using access and vba
Hi,
I was wondering if some one can help me.
I have attached sample access file. It has two tables.
The total used data recorded in table1 is by interval(from start date to end date)
I need to convert this data to see daily usage. From that I need to calculate daily usage data
total_used/date difference. Then I need to record that data for each day in a range((from start date to end date) to table2
For example first record in table1 is
ID_interval text1 startdate enddate total_used
1 1A 1/8/2013 4/2/2013 34578.28
2 2B 4/3/2013 6/7/2013 3458
so the matching records in table2 should be:
day_id day ID_interval total_used
1 1/8/2013 1 411.6461904761905 ( 34578.28/84)
2 1/9/2013 1 411.6461904761905
.
.
.
.
84 4/2/2013 1 411.6461904761905
85 4/3/2013 2 53.2(3458/65)
86 4/4/2013 2 53.2
........
Avould like to write a vba procedure to append the data to table2 automatically. Any help will be apreciated
test.accdb
I was wondering if some one can help me.
I have attached sample access file. It has two tables.
The total used data recorded in table1 is by interval(from start date to end date)
I need to convert this data to see daily usage. From that I need to calculate daily usage data
total_used/date difference. Then I need to record that data for each day in a range((from start date to end date) to table2
For example first record in table1 is
ID_interval text1 startdate enddate total_used
1 1A 1/8/2013 4/2/2013 34578.28
2 2B 4/3/2013 6/7/2013 3458
so the matching records in table2 should be:
day_id day ID_interval total_used
1 1/8/2013 1 411.6461904761905 ( 34578.28/84)
2 1/9/2013 1 411.6461904761905
.
.
.
.
84 4/2/2013 1 411.6461904761905
85 4/3/2013 2 53.2(3458/65)
86 4/4/2013 2 53.2
........
Avould like to write a vba procedure to append the data to table2 automatically. Any help will be apreciated
test.accdb
ASKER
I'm not sure if you understood correctly.
Right now Imy data base stores data between two dates.
For example between 01/12/2014 - 01/15/2014 where used 16 units.
I need to convert it to daily usage
01/12/2014 - 4 units
01/13/2014 - 4 units
01/14/2014 - 4 units
01/15/2014 - 4 units
I need to do it for each record in a table.
Right now Imy data base stores data between two dates.
For example between 01/12/2014 - 01/15/2014 where used 16 units.
I need to convert it to daily usage
01/12/2014 - 4 units
01/13/2014 - 4 units
01/14/2014 - 4 units
01/15/2014 - 4 units
I need to do it for each record in a table.
If you do not have the daily data recorded somewhere you can't get it simply because it is not there. You may be able to make it up assuming that the same amount was used in that interval.
So example 01/12/2014 - 01/15/2014 is 4 days. Then you divide 16 by 4 and assume that 4 units were used per day in that interval.
Is that what you have in mind.
So example 01/12/2014 - 01/15/2014 is 4 days. Then you divide 16 by 4 and assume that 4 units were used per day in that interval.
Is that what you have in mind.
still can't download. Please post a copy of the code
ASKER
So example 01/12/2014 - 01/15/2014 is 4 days. Then you divide 16 by 4 and assume that 4 units were used per day in that interval.
that is true, this is my assumption.
@maximyshka
did you test what i uploaded ?
.
did you test what i uploaded ?
.
Hi Rey,
Did you managed to download Max's sample db?
Max then I would use something like:-
Dim strSQL as String
Dim lngInterval as Long
Dim strInterval as String
Dim dteStart as Date
Dim dteEnd as Date
Dim dteDay as Date
Dim lngSum as Long
dteStart = Inputbox("Insert Start Date")
dteDay=dteStart
dteEnd = Inputbox("Insert End Date")
lngInterval=DateDiff(dteSt art,dteSta rt)
lngSum = DSum("total_used","table1" ,"startdat e>#" & dteStart & "# AND enddate <#" & dteEnd & "#")
For i=0 to lngInterval - 1
strInterval=DLookup("ID_in terval","t able1","st artdate<#" & dteDay & "# AND enddate >#" & dteDay & "#"
strSQL="Insert Into table2(" day,ID_interval,total_used )
strSQL=strSQL & " VALUES(" & dteDay & "," & strInterval & "," & lngSum/lngInterval & ")"
Currentdb.Execute strSQL
dteDay=DateAdd("d", 1, dteDay)
Next
Did you managed to download Max's sample db?
Max then I would use something like:-
Dim strSQL as String
Dim lngInterval as Long
Dim strInterval as String
Dim dteStart as Date
Dim dteEnd as Date
Dim dteDay as Date
Dim lngSum as Long
dteStart = Inputbox("Insert Start Date")
dteDay=dteStart
dteEnd = Inputbox("Insert End Date")
lngInterval=DateDiff(dteSt
lngSum = DSum("total_used","table1"
For i=0 to lngInterval - 1
strInterval=DLookup("ID_in
strSQL="Insert Into table2(" day,ID_interval,total_used
strSQL=strSQL & " VALUES(" & dteDay & "," & strInterval & "," & lngSum/lngInterval & ")"
Currentdb.Execute strSQL
dteDay=DateAdd("d", 1, dteDay)
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
VBA Code
Open in new window