Payroll table query

[Dophia]

Hi Everyone: My database has a timesheet table and a pay period end table as follows:

Timesheet Table:
Employee ID
Timesheet Date
Pay Period End Date (based on biweekly pay period)
Hours (worked that day)
Days (if the employee is paid by the day and not hour)

PayPeriodEnd Table:
Pay Period End (dates based on biweekly pay period)(Joined to Timesheet Table)

What would be the best way to calculate the number of hours worked in a week instead of the PayPeriodEnd which is for two weeks? I currently have a query to calculate the total hours per pay period since the [PayPeriodEndDate] is in both tables. But I really need to calculate the total hours worked in each week of the biweekly pay period. I don't think I have enough information in the tables to calculate this easily.

Would a calculation in a module be better? Below is the query, which works, but not in the way I need.

SELECT tblPayPeriodDates.Pay_End_Date, tblTimesheets.Employee_ID, Sum(tblTimesheets.Hours) AS SumOfHours, Sum(tblTimesheets.Days) AS SumOfDays
FROM tblPayPeriodDates INNER JOIN tblTimesheets ON tblPayPeriodDates.Pay_End_Date = tblTimesheets.Pay_End_Date
GROUP BY tblPayPeriodDates.Pay_End_Date, tblTimesheets.Employee_ID;


Any help/guidance would be greatly appreciated.
Sophia

 

[PHV]

Have a look at the Format function:
Format(tblTimesheets.Date,'ww') As WeekNumber

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dophia]

Can you expand on what I should do with that function. I only have the biweekly pay date referred to in the timesheet table and the actual date of work.

Sophia

 

[lespaul]

expansion:

SELECT Format(TimeSheet.TimeSheetDate, "ww") As WeekNumber from TimeSheet

will return the week number of the year that the date falls in.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Dophia]

Thanks for clearing that up. I ended up adding a field to the table to distinguish between whether it was Week 1 or Week 2 of the pay period. Now, my query has the info that I need.

Thanks for all of your help.
Sophia