Solved

Find data for each day between the two dates using access and vba

Posted on 2014-02-14
9
1,742 Views
Last Modified: 2014-03-10
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
0
Comment
Question by:maximyshka
  • 4
  • 3
  • 2
9 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 39860100
I could not download your database. I presume that you have a table with daily records and call it tablex for the sake of this exercise.

VBA Code

Dim strSQL as String

strSQL="Insert Into table2"
strSQL=" Select * from tablex inner join table1 on tablex. ID_interval= table1. ID_interval"
strSQL=strSQL & " where table1.startdate>[Insert Start Date] And table1.enddate >[Insert End Date]"

Currentdb.Execute strSQL

Open in new window

0
 

Author Comment

by:maximyshka
ID: 39860122
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39860154
test this, see code in module1
open table2
test.accdb
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 16

Expert Comment

by:Sheils
ID: 39860184
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.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 39860195
still can't download. Please post a copy of the code
0
 

Author Comment

by:maximyshka
ID: 39860232
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39860242
@maximyshka


did you test what i uploaded ?






.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 39860343
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(dteStart,dteStart)

lngSum = DSum("total_used","table1","startdate>#" & dteStart & "# AND enddate <#" & dteEnd & "#")


For i=0 to lngInterval - 1

      strInterval=DLookup("ID_interval","table1","startdate<#" & 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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39860401
here is my codes


Sub populateTable2()
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim j As Long, x As Long
Set rs1 = CurrentDb.OpenRecordset("table1")
Set rs2 = CurrentDb.OpenRecordset("table2")

rs1.MoveFirst

Do Until rs1.EOF
     j = DateDiff("d", rs1!startdate, rs1!enddate)
     For x = 1 To j
          With rs2
               .AddNew
               ![Day] = rs1!startdate + x - 1
               !id_interval = rs1!id_interval
               !total_used = rs1!total_used / j
               .Update
          End With
     Next
rs1.MoveNext
Loop

rs1.Close
rs2.Close
End Sub
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now