D
DaveSlater
Guest
Hi. I'm using Access97 and I would like some advice on how to code this.
I have a table with the following records in it:
StaffFileNo Date TotalHoursWorked TotalHoursAbsent
There should be a record for each day for each staff member with the total hours worked for that day, and absent for that day.
Problem:
I need a way to add up the total hours for each week, and write this information away to another table.
So far I've created a recordset, but don't really know what to do next to achieve my aim. Can anyone give me some pointers?
Thanks.
Current Code if it helps.
Dim db As Database
Dim rstHoursLogged As Recordset
Dim strSQL As String
Dim strUserID As String
'Get the current Users Name
strUserID = GetUserName
'get recordset from tbl_hours_logged containing all records for the user
db = CurrentDb()
strSQL = "SELECT * FROM tbl_hours_logged WHERE fldFileNo = '" & strUserID & "' ORDER BY fldDate"
Set rstHoursLogged = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
'from this calculate the total number of hours for each week
'calculate 17week rolling average totals and write away record in to table with user id.
I have a table with the following records in it:
StaffFileNo Date TotalHoursWorked TotalHoursAbsent
There should be a record for each day for each staff member with the total hours worked for that day, and absent for that day.
Problem:
I need a way to add up the total hours for each week, and write this information away to another table.
So far I've created a recordset, but don't really know what to do next to achieve my aim. Can anyone give me some pointers?
Thanks.
Current Code if it helps.
Dim db As Database
Dim rstHoursLogged As Recordset
Dim strSQL As String
Dim strUserID As String
'Get the current Users Name
strUserID = GetUserName
'get recordset from tbl_hours_logged containing all records for the user
db = CurrentDb()
strSQL = "SELECT * FROM tbl_hours_logged WHERE fldFileNo = '" & strUserID & "' ORDER BY fldDate"
Set rstHoursLogged = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
'from this calculate the total number of hours for each week
'calculate 17week rolling average totals and write away record in to table with user id.
