A Simple (?) Time Sheet Wanted

[Wizzweb]

Access 2000

I’m trying to build a simple time sheet, just as an example that I can refer to if required at a later date, and I am currently stumped on how to effectively produce a weekly individual report vice my current report which shows all data.

My current structure:

TABLES
tblEmployee
- EmployeeID
- LastName
- FirstName
- Address, etc…

tblTimeCards
- TimeCardID
- EmployeeID (Employee Information drawn from the table "tblEmployee")
- DateEntered "Date/Time"(Date Pay Information is applicable to)
- BasicHours "Number" (Number of hours worked)
- BasicRate "Currency"(Assigned Basic Rate of Pay)
- OTHours "Number" (Number of hours worked overtime)
- OTRate "Currency"(Assigned Overtime Rate of Pay)

FORMS
frmEmployee (Record Source: tblEmployee)

BUTTON on form to print current employee report:

'------------------------------------------------------------
' BUTTON - PRINT INDIVIDUAL EMPLOYEE PAY DETAIL REPORT
'
' Link the form and report via the Employee ID number
'------------------------------------------------------------
Private Sub cmdPayDetailRpt_Click()
On Error GoTo Err_cmdPayDetailRpt_Click

Dim strLinkcriteria As String

strLinkcriteria = "[EmployeeID]=" & Me![EmployeeID]

With DoCmd
.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
.OpenReport "rptEmployees", acPreview, , strLinkcriteria
.RunCommand acCmdZoom100
.Maximize
End With

Exit_cmdPayDetailRpt_Click:
Exit Sub

Err_cmdPayDetailRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPayDetailRpt_Click

End Sub


SUB-FORM
sfrmPayCalculations (Records Source, qryPayCalculations)

SELECT tblTimeCards.EmployeeID, tblTimeCards.TimeCardID, tblTimeCards.DateEntered, tblTimeCards.BasicHours, tblTimeCards.BasicRate, Nz([BasicHours],0)*Nz([BasicRate],0) AS Total, tblTimeCards.OTHours, tblTimeCards.OTRate, Nz([OTHours],0)*Nz([OTRate],0) AS [OT Total], Nz([BasicHours],0)+Nz([OTHours],0) AS [Daily Hours], Nz([Total],0)+Nz([OT Total],0) AS DailyPay, tblEmployee.NatInCon, tblEmployee.PAYE, tblEmployee.TaxRefund
FROM tblEmployee RIGHT JOIN tblTimeCards ON tblEmployee.EmployeeID = tblTimeCards.EmployeeID;


REPORTS
rptEmployees (Record Source: qryEmployeeReport)

SELECT tblEmployee.EmployeeID, [LastName] & (", "+[FirstName]) & (" "+[Initials]) AS EmployeeName, tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.Initials, tblEmployee.NINo, tblEmployee.DOB, Age([DOB]) AS Age, tblEmployee.Address, tblEmployee.Town, tblEmployee.PostCode, tblEmployee.PhoneHome, tblEmployee.PhoneMobile, tblEmployee.NatInCon, tblEmployee.PAYE, tblEmployee.TaxRefund
FROM tblEmployee;

srptPayDetails (Record Source: qryPayDetails)

SELECT tblTimeCards.EmployeeID, tblTimeCards.TimeCardID, tblTimeCards.DateEntered, tblTimeCards.BasicHours, tblTimeCards.BasicRate, Nz([BasicHours],0)*Nz([BasicRate],0) AS Total, tblTimeCards.OTHours, tblTimeCards.OTRate, Nz([OTHours],0)*Nz([OTRate],0) AS [OT Total], Nz([BasicHours],0)+Nz([OTHours],0) AS [Daily Hours], Nz([Total],0)+Nz([OT Total],0) AS DailyPay
FROM tblTimeCards;


The above info is pretty much the basis of what I currently have, any help, or suggestions on how I can print a weekly Pay guide report vice an accumulation of all the data entered would be greatly appreciated.

As an afterthought, any suggestions on how to effectively apply a means to have the user change the current pay rates without effecting all previously applied pay amounts would also be of help: ( I was considering a separate table, and making the current pay rate the default, but then I thought that it might be hard for a user to change the default).

All examples welcome (I tend to learn from examples much easier then from text solutions)

Thanks in advance.

 

[bgmiller]

I was working on a timesheet database myself and ran into similar problems. It sounds like you want to group the values by week.

Option 1. Setup a table in between Employee and timecards that will serve as the timesheetID for each week:
tblEmployee
- EmployeeID
- LastName
- FirstName
- Address, etc…

tblTimesheet
- TimesheetID
- EmployeeID
- Weekdate

tblTimeCards
- TimeCardID
- TimesheetID
- DateEntered
- BasicHours
- BasicRate

You can then connect the timecards to the weekly timesheet in a query and group them by timesheetID.

Option 2. Add a field to a query for tblTimecards that identifies which week it belongs to. You can then group this query using the weeknumber. This second option doesn't require the table in between but takes a huge amount of query processing and is more messy.

qrytimecard
Select *, weeknum(DateEntered) as WeekNum FROM tblTimecards

~ Find the week number using DateDiff or some other function.


Concerning your salary amount, I'd setup a separate salary table for each employee with effective dates for each salary change. Your timesheet can then pull the most current salary depending on its date.

Brian