Showing totals by date 1

[SuicidED]

Hi All
You enter a start date and end date (could be the same date) and a total of units for the contract. You calculate the number of units per day (no problem).

But I want to see on a day to day basis the the total units for each day.

For example
Total Start date End date Units per day
Contract A 6000 units 1/1/03 3/1/03 2000
Contract B 10000 units 1/1/03 10/1/03 1000
Contract C 93000 units 1/1/03 31/1/03 3000


Now looking at this on 2/1/03 I have 6000 units, on on 6/1/03 4000 units and 17/1/03 I have 3000 units.

Can anyone point me to the code in Access to use so that reports/queries will give me the daily totals.

Many thanks Eddie

 

[JoyInOK]

You could add an expression to your query that divides the total # of units by the number of days in the contract. I've included the +1 so Access will include the start day in the calculation:

UnitsPerDay: [Total]/((DateDiff("d", [FirstDate],[SecondDate])+1)

 

[Rolliee]

You can also write simple VB that does the same and anything else you wishs it to do.

sub cmdSeeIt()
dim rs as DOA.recordset, totVal as double, lstDate as date

set me.recordsetclone
ORDERBY date1 ' not sure of this without looking at Help
rs.movelast
rs.movefirst
lstDate = rs![Date1]
tot Val = 0
do while not rs.eof
if lstDate = rs![Date1] then
totVal = totVal + rs![ThisVal]
else
msgbox lstDate & " " & str(totVal)' or whatever
totVal = 0
lstDate = rs![Date1]
endif
rs.movenext
loop
rs.close
set rs = nothing


Piece of Cake.

rollie@bwsys.net

 

[Rolliee]

send me your email address and I will send you a sample of how to do this easily with VB

rollie@bwsys.net