Question Spread a value across a date range

[mbw1975]

Hello,
I have a table in Access 2007 containing three inputs:

1) START DATE
2) END DATE
3) VALUE (i.e. 100)

Is it possible to take the VALUE and distribute it evenly across the date range (Start Date - End Date) using a chart within Access?

For example:

Start Date = 3/25/10
End Date = 3/29/10
Value = 100

Resulting Chart would graph the following: 3/25/10 = 20
3/26/10 = 20
3/27/10 = 20
3/28/10 = 20
3/29/10 = 20

If it can't be done with a chart, can a table be generated on the fly to record the values I want to display in the chart?

 

[Mr Smin]

There are more knowledgeable people than me on here who may have a better method, but I do something similar to what you are doing by looping between the start and end dates, writing a row of values to a table for each date and then displaying the table as a pivot chart.

I do this in VBA - code is in a button's on click event on a form which has text boxes for the user to select start and end dates. I have deleted code specific to my project that is unlikely to be relevant to you.

dtDateInQuestion = Me!dtFrom
Me!txtDateInQuestion = dtDateInQuestion
'loop from one date to other in increments of 1
While dtDateInQuestion < (Me!dtTo + 1)
Me!txtDateInQuestion = dtDateInQuestion

'insert new row in tblDailyTally
DoCmd.SetWarnings False
strSQL = "INSERT INTO tblDailyTally (DateInQuestion)" & _
"VALUES ('dtDateInQuestion');"
'MsgBox strSQL
DoCmd.RunSQL strSQL
dtDateInQuestion = dtDateInQuestion + 1
Wend

 

[mbw1975]

Thank you. I have the form working and I used the code that you supplied for the command button's On Click event.

What I would like to do next is to loop through the entire recordset and update tblDailyTally for each record.

For example, I have the form which is built from a query and the query returned 10 records. Instead of having to navigate through each record on the form and click the command button 10 times to update tblDailyTally is there a way to automate the event to loop through to the end of the recordset?