If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Timecards not working
I am trying to do a simple timecard databse, my tables a 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. -- -DJM |
#2
|
|||
|
|||
Timecards not working
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" wrote: I am trying to do a simple timecard databse, my tables a 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. -- -DJM |
#3
|
|||
|
|||
Timecards not working
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 -- -DJM "Ken Sheridan" wrote: 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" wrote: I am trying to do a simple timecard databse, my tables a 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. -- -DJM |
#4
|
|||
|
|||
Timecards not working
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 "DJM" wrote: 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 -- -DJM |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outlook Rules not Working | Marty | General Discussion | 0 | May 6th, 2005 05:58 PM |
Working time and days | Nortos | Worksheet Functions | 5 | May 6th, 2005 04:17 PM |
Working time and days | Nortos | General Discussion | 1 | May 6th, 2005 03:47 PM |
Trace Dependents and Precedents not working | Manish | Worksheet Functions | 0 | April 11th, 2005 09:39 PM |
"Format cells" not working | v matheson | General Discussion | 1 | July 26th, 2004 06:46 PM |