Timecards not working

[DJM]

I am trying to do a simple timecard databse, my tables are: EmployeeData,
DailyTimeEntry, LeaveAvailable, PayPeriod. The managers enter employee data
including initial bank of leave hours. Employees enter time daily including
use leave hours. Employees view a timecard report based on their EE# and the
pay period that shows their EE#, Name, pay period, all daily entries during
the pay period, totals for timecard and leave totals- initial amt, used,
left. I can enter records for an employee and daily time, but do not know
how to pull out timecard data based on EE# and date or how to show changes in
leave available. Please HELP. I keep reading and trying, but nothing seems
to work.

 

[Ken Sheridan]

It would help if you could post details of the columns in each table, and on
which columns the tables are related. I can second guess what they are, but
I could easily end up misleading you if I guess wrong.

Ken Sheridan
Stafford, England

 

[DJM]

Ken,
I will try to describe my tables from the relationship page.

Employee Data - Employee Number (PK), Name, Supervisor, Location Code

Daily Time Entry- Employee Number, Pay Period Numberr, Date (PK), Starte
Time, End Time, Break-Lunch, Total Regular hrs, Overtime hrs, Adjustment Add,
Adjustment Subtract, Leave Hrs, Vacation Hrs, Holiday Hrs.

Leave Hours- Employee Number, Initial Leave hrs (PK), Used Leave Hrs,
Available Leave Hrs, Initial Vacation Hrs, Used Vacation Hrs, Available
Vacation Hrs.

Pay Periods- Pay Period Number (PK), Start Date, End Date, Pay Date

Employee Data is linked to Daily Time Entry and Leave Hours by Employee
Number. Employee Data is linked to Pay Periods by Pay Period Number.

How I want it to work is the Mgr enters the employee's data and initial
leave/Vac hours. The employee enters their daily time. The employee can run
a report to show a full timecard for the pay period with column sums and a
table showing their leave prior to this timecard. When the manager pulls the
final timecard for the pay period it will look like the one the employee
pulled, but will update the leave/vacation totals and show original, used,
available

 

[Ken Sheridan]

I take it that the database only relates to one 'leave period' as there seems
to be no date column(s) in the Leave hours table to define different leave
periods for each employee.

The first thing I'd say is that there appears to be some redundancy in that
the Leave Hours table includes Used Leave Hrs, Available Leave, Used Vacation
Hrs and Available Vacation Hrs columns. As the Leave Hours and Vacation
Hours are entered in the Daily Time Entry table these can be computed from
this data and the Initial Leave Hours and Initial Vacation Hours values in
Leave Hours. I notice you also have Holiday Hours in Daily Time Entry??

The manager's version of the time card just needs a little extra adding to
the employee's version. I assume the report is either grouped by employee or
restricted to just one employee each time its printed and the totals of the
various hours are in the group or report footer. To incorporate the various
leave values you can add a few unbound text box controls to the footer.

To get the leave an employee has taken before the current pay period first
create a query, qryHoursTaken say, which joins the Daily Time Entry and Pay
Periods . The query should return columns for Employee Number, Leave Hrs,
Vacation Hrs and End Date (the last from the Pay Periods table). The
ControlSource for the text box would then get the sum of the leave hours
taken by the current employee in the Pay Periods with an End date earlier
than the current Date value in the time card.

= DSum("[Leave Hrs]", "[qryHoursTaken]", "[End Date] < #" &
Format([Date],"mm/dd/yyyy") & "# AND [Employee Number] = " & [Employee
Number])

To get the opening balance of leave hours available at the start of the
current time card you'd subtract this form the initial leave hours, so the
ControlSource of a text box to do that would be:

= DLookup("[Initial LeaveHrs", "[Leave Hrs]", "[Employee Number] = " &
[Employee Number]) - DSum("[Leave Hrs]", "[qryHoursTaken]", "[End Date] < #"
& Format([Date],"mm/dd/yyyy") & "# AND [Employee Number] = " & [Employee
Number])

To get the closing balance of leave hours available at the end of the
current time card subtract the sum of the LeaveHrs in the current time card:

= (DLookup("[Initial LeaveHrs", "[Leave Hrs]", "[Employee Number] = " &
[Employee Number]) - DSum("[Leave Hrs]", "[qryHoursTaken]", "[End Date] < #"
& Format([Date],"mm/dd/yyyy") & "# AND [Employee Number] = " & [Employee
Number])) – Sum([Leave Hrs])

For the vacation hours you'd do the same but look up the vacation hours
rather than the leave hours.

Ken Sheridan
Stafford, England