Running Sum From rs To Excel

[DMT Dave]

Hi Guy's i am trying to output a recordset to Excel, working successfully with one little adjustment required, would appreciated if the code i post can be adjusted wherever the running sum needs to be ?

So if there is 5 records in rs record count

The first record is correct, the next 4 are incorrect, some of the fields (Cells) need to calculate from the last record ie: record 2 certain cells mentioned cells needs to from previous record then the calculation, same for record 3 needs to be record 1 and record 2 then the calculation etc



so record in this case the highlighted cells needs to be calculated from the 4 previous records then the calculation

Please forgive me if i haven't explained correctly but hope i have, i think I'm just mind boggled in how to do it!!!!!

Code

Dim pOpen As String, fOpen As String, apXL As Object, xlWB As Object, xlWS As ObjectDim rs As DAO.Recordset, strSQL As String, msMin As Integer, msMax As Integer
Dim myDriver As String, myStart As Date, myEnd As Date
Dim LR As Long


If Me.txtIndexNo = "2" Then
myDriver = Me.cboDriver
myStart = Format(Me.txtStartDate, "mm/dd/yyyy")
myEnd = Format(Me.txtEndDate, "mm/dd/yyyy")


xlPath = "T:\DMT Ltd\XL Files\Driver Hours\" & Me.cboDriver & "\"
xlFileName = Me.cboDriver & ".xlsx"


Set rs = CurrentDb.OpenRecordset("Select tblDriverHours.StartDate, tblDriverHours.MSNo, tblDriverHours.StartTime, tblDriverHours.FinishTime, tblDriverHours.RestBreak, tblDriverHours.HoursDone, tblDriverHours.StdHours From tblDriverHours WHERE Driver = '" & myDriver & "' And StartDate Between #" & myStart & "# And #" & myEnd & "#")


pOpen = "T:\DMT Ltd\XL Files\Driver Hours\" & Me.cboDriver & "\"
fOpen = Me.cboDriver & ".xlsx"
Set apXL = CreateObject("Excel.Application")
Set xlWB = apXL.Workbooks.Open(pOpen & fOpen)
apXL.Workbooks.Open pOpen & fOpen, True, False
Set xlWS = xlWB.Worksheets("Time Sheet")
apXL.Visible = False
With xlWB
rs.MoveFirst
Do Until rs.EOF
MsgBox ("There Are: " & rs.RecordCount & " Records To Output"), vbInformation + vbOKOnly, "RECORD COUNT"
LR = xlWS.Cells(xlWS.Rows.Count, 1).End(xlUp).Row
.Worksheets(1).Cells(LR + 1, 1) = Format(rs.Fields("StartDate"), "ddd-dd-mmm-yyyy")
.Worksheets(1).Cells(LR + 1, 2) = rs.Fields("MSNo")
.Worksheets(1).Cells(LR + 1, 3) = Format(rs.Fields("StartTime"), "00:00")
.Worksheets(1).Cells(LR + 1, 4) = Format(rs.Fields("FinishTime"), "00:00")
.Worksheets(1).Cells(LR + 1, 5) = rs.Fields("RestBreak")
.Worksheets(1).Cells(LR + 1, 6) = rs.Fields("HoursDone")
.Worksheets(1).Cells(LR + 1, 7) = rs.Fields("StdHours")
.Worksheets(1).Cells(LR + 1, 8) = rs.Fields("StdHours") - rs.Fields("HoursDone")
.Worksheets(1).Cells(LR + 1, 10) = rs.Fields("HoursDone") - rs.Fields("StdHours") - rs.Fields("RestBreak")
.Worksheets(1).Cells(LR + 1, 11) = rs.Fields("StdHours") - rs.Fields("HoursDone") + rs.Fields("RestBreak")
'.Worksheets(1).Cells(LR + 1, 1).CopyFromRecordset rs
.Worksheets(1).Cells.EntireColumn.HorizontalAlignment = xlLeft
rs.MoveNext
Loop
.Save
End With
xlWB.Close
apXL.Quit
Set xlWS = Nothing
Set apXL = Nothing
Set rs = Nothing


MsgBox ("Sucessfully Updated" & vbNewLine & vbNewLine & _
"Click Open In Excel Button"), vbInformation + vbOKOnly, "UPDATE SUCESSFULL"


End If

Record quantities may vary but never typically any more than 5 or 6

Result with records 2 to 5 incorrect values

[CJ_London]

Your description is a bit vague - why not show the required result and the calculation used to get it in a couple of columns

[DMT Dave]

Hi Ajax So Cell H3 should be 11.66 (F2) + 7.08 (F3) - G3 (37) = 18.26
The Result from rs is 29.91
Should be 18.26

Cell H4 Should be 11.66 (F2) + 7.08 (F3) + 13.5 (F4) - G4 (37) = 32.24
The result from rs is 23.5
should be 4.76

So std Hours is 37 (H2)
rs record 1 hours done 11.66 F2
Hours Left should be 25.33 which is correct because its the first record
next record
hours left should be 25.53 - 7.08 should be 18.45 (Not 29.91)
next record
hour left should be 25.53 - 7.08 - 13.5 should be 4.95 (Not 23.5)

So 37 hours - 3 days of hours done should keep reducing accordingly

Hope this explains,

I have thought of telling the Excel cells from within the recordset what sums should be but unsure if i can so something like

.Worksheets(1) .Cells = "=SUM H4-F4-F3-F2 etc... to give a reducing running sum then do the calcs for hours over and hours under

again i hope i have explained a little better

Much appreciated to you guy's out there

[CJ_London]

Sorry now away from my computer for a couple of days, perhaps someone else can pick this up?

[CJ_London]

You will need something that defines the order to determine the previous record, usually a date time record or perhaps pk, don’t see this in your data. Your date+start or end time doesn’t fit the bill, nor does the ms number

And what happens with h5, h6 etc?

[Welshgasman]

Dave, see the code I posted to your other thread, before you posted this?

I anticipated your query.

FWIW you appear by the values to just be taking the Hours Done from Standard hours for each record?

You are NOT summing Hours Done at all?

[DMT Dave]

Hi WGM yes will do thank you, i think because i dragged away a lot from one project to another it sometimes distraction doesn't help !!

Plus compared to many of you guys on here, my knowledge is probably more limited....

Every day is a School day

[Welshgasman]

You need to have some sort of order for a running sum as mentioned.
Mine was the ID, the TransactionDate and the ClientRef

[DMT Dave]

Hi, WGM i have looked at your SELECT statement and going to try and adapt the method to mine

I just thought though, can't we not reference a rs.count after rs.move next ?

Something like

.Worksheets(1).Cells(LR + 1, 8) = rs.Fields("StdHours") - rs.Fields("HoursDone") Can we not refence this to record 1 of record count (particular MSNo)
rs.movenext
.Worksheets(1).Cells(LR + 1, 8) = rs.Fields("StdHours") - rs.Fields("HoursDone") Can we not refence this to record 2 of record count (particular MSNo > than last one)

I have MSNo which is not auto number barring an assignment, these should never be the same, end of 2 lines above

Record Count 2 will then always calculate on figures based on record count2 ?

I may be loosing myself here now ? i think i need certain fields carried over from rs count 1 to rs count 2 so a new calculation can be done

Will try your select statement method

Kindest

[Welshgasman]

I would do the calc in the query.
It could be done in the excel sheet as well?

[DMT Dave]

Hi WGM, yes I initally done the sums on the Excel sheet but won't the recordset over write it ?

If not, i can easily do that ?

Many Thanks

[Welshgasman]

Well you are not using CopyFromRecordSet. That would overwrite your sheet, if the fields exceeded the column where your formulae would be.
You are just using a recordset to populate various cells?, so an extra column could be running sum with your formulae? and you do not put anything from your recordset into that, if you start with a template. That also allows for checking the data is correct and summing correctly?

Alternative you place your fomulae in the cell, in the same way you do your values? Just involves some calculation?

[DMT Dave]

Okie Dokie, thank you again for your input, I will try doing calc on Excel sheet, maybe a hidden column

Kindest

[DMT Dave]

Hi WGM, i have found the best way is to take out the incorrect values and add sums after the loop

stdHrs = rs.Fields("StdHours")
HrsDone = DSum("HoursDone", "tblDriverHours", "[Driver] = '" & myDriver & "' And [StartDate] Between #" & myStart & "# And #" & myEnd & "#")

LR = xlWS.Cells(xlWS.Rows.Count, 1).End(xlUp).Row
.Worksheets(1).Cells(LR + 1, 1) = Format(rs.Fields("StartDate"), "ddd-dd-mmm-yyyy")
.Worksheets(1).Cells(LR + 1, 2) = rs.Fields("MSNo")
.Worksheets(1).Cells(LR + 1, 3) = rs.Fields("StartTime")
.Worksheets(1).Cells(LR + 1, 4) = rs.Fields("FinishTime")
.Worksheets(1).Cells(LR + 1, 5) = rs.Fields("RestBreak")
.Worksheets(1).Cells(LR + 1, 6) = rs.Fields("HoursDone")
.Worksheets(1).Cells(LR + 1, 7) = rs.Fields("StdHours")
.Worksheets(1).Cells.EntireColumn.HorizontalAlignm ent = xlLeft
rs.MoveNext
Loop
.Worksheets(1).Cells(LR + 2, 1) = "Totals:"
.Worksheets(1).Cells(LR + 2, 6) = HrsDone
.Worksheets(1).Cells(LR + 2, 8) = stdHrs - HrsDone
.Worksheets(1).Cells(LR + 2, 10) = HrsDone - stdHrs

[Welshgasman]

Whatever floats your boat?

Not the running sum you were asking for in the first place?

However, if that suits your purpose, then go with that.