Very simple question

[joh1135]

I am very new to Acess - and I think I have a very simple project to accomplish quickly.

I have created a three tables -

1) EMPLOYEE (Name, Grade, Work Location, and Shift) for all employees.

2) OT1 (Name, OTHours, Explaination) employee + hours worked during pay period 1

3) OT2 (Name, OThours, Explaination) employee + hours worked during pay period 2

I would like to show all employees who has worked OT by querying EMPLOYEE, OT1 and OT2) and showing the total OThours worked.

I plan to create an OT table for each pay period during the year (26) so that I can see who has worked OT when and how much.

Thank you all in advance - appreciate all your previous help (in Excel) - it is a wonderful service that you provide for the rest of us.

Dave Johnson

Just trying to get to the end gracefully.....

 

[Norie]

Dave

Why do you want to create 26 different tables that essentially will contain the same sort of data?

Whynot just have one table with a field that indicates rge pay period?

This would greatly simplify any queries or reports.

Also you should add a primary key in the Employee table.

This could be an Autonumber.

So your table layout would be something like this:

1) EMPLOYEE (EmployeeID, Name, Grade, Work Location, and Shift) for all employees.

2) OT (EmployeeID, OTHours, Explaination, PayPeriod) employee + hours worked during pay period 1

 

[joh1135]

Norie,

Thanks for the very quick response.

The reason I layed it out the way I did is that I get a sheet of paper for the current payperiod with the employees name and the amount of OT that individual worked.

I can then just enter the employees name with out having to do a lookup of 2500 names to find the employee, and enter the amount of OT that he/she worked.

I have didnt want to us an employee number (auto number) because I would have to know that number..

I used the employee name,(which is lastname, firstname, mi.) as the primary key because I only have one employee with that name (Usually).

I also want to show the total amount of OT an employee is working thru out the year, and also by pay period.



So I guess I should re think - this

Dave

 

[HomerJ]

Norie's suggestion will save you a lot of headaches over using multiple tables. Even if you go by employee name instead of an ID number you could still use one single overtime table that has a PayPeriod field within it.

You may want to consider applying an autonumber to your employees as a primary key even if you wish to go by their names. If you use a form for entering new data, you could set up a combobox that would allow you to select anybody in your employees table (whether you use an employee ID or not), and then enter the OT hours and the period.

You will have a much easier time setting up queries for employees and pay periods by having one table instead of 26. Especially if you intend to use this for more than one year. After two years it would be 52 tables, then 78, and so on.

Just some things to consider before you proceed further.

HTH

 

[Norie]

Dave

All the points you made can be dealt with.

The reason I layed it out the way I did is that I get a sheet of paper for the current payperiod with the employees name and the amount of OT that individual worked.

I can then just enter the employees name with out having to do a lookup of 2500 names to find the employee, and enter the amount of OT that he/she worked.

I don't quite know what you mean by this, especially the part about looking uo names.

You could use a dropdown to list all employees.

I have didnt want to us an employee number (auto number) because I would have to know that number..

No you wouldn't.

I used the employee name,(which is lastname, firstname, mi.) as the primary key because I only have one employee with that name (Usually).

You are probably better using 3 different fields - one for lastname, one for first name and one for middle initial.

What happens when you do have employees with the same name?

With 2500 employees this could happen.

I also want to show the total amount of OT an employee is working thru out the year, and also by pay period.

This could be dealt with using totals queries where you could group by pay period and sum overtime.

 

[joh1135]

Thanks everyone,

Took Nories advice and created a one table with employee information and pay periods in it.

I now find out that I also need to include where the overtime was worked - we have 5 different areas to work in.

IE
Employee Dave
Pay Period 01 - 2 hours OT - 1 hour worked at Area A and 1 hour at Area B.

Employee Jack
Pay Period 01 - 1 hour OT worked Area C

Should I create a Table for each area or just add the areas to the employee information table?

Thanks again for your assistance on this - couldnt have done it with out ya'all.


Dave

 

[Norie]

Dave

Just add a field to the overtime table for the area.