Opinion on design idea/possibility

[fireman_gcfd]

Hello Everyone

I am looking to build and Overtime tracking form and have been pondering on the design of it. Here is my idea and not sure how I should go about doing it (straight forms and tables or queries). I would like to select my employees in a combobox and have their information autofill the remaining fields and/or add new ones. Additionally have fields that I can input any overtime occurred which would be stored in a seperate table but displayed in a sub-form in datasheet view. These fields that were just filled out with the overtime information would clear everytime the add record button is hit.

I know that sounds like alot and sure I can muddle my way through that part of it, the big question I have is do I need a seperate table for every employees overtime? Or is there code/query that can select the records of the selected employee and display the information that pertains just to them?

Has anyone come across or posess a sample database similar to this? If not can someone point me in the right direction for example code and tutorials?

I look forward to hearing from everyone on this.

Thank You

Jaz

 

[neileg]

At its simplest, you need two tables, one with employee details with one record per employee and including a unique employee identifier (a primary key). The second table will store the overtime with one record per overtime event. This record will like to the employee concerned by holding the primary key for the employee in the overtime record. The employee field in the overtime table is known as a foreign key.

You can then build a form/subform setup. The employee data will go in the main form and the overtime in the subform. Access will use the employee ID to link the two forms so the only the overtime relating to the employee in the main form is shown.

 

[Bilbo_Baggins_Esq]

Technically, you do not really need a seperate table just for overtime. one table for all hours should be sufficient. One line per work entry per employee with a field to enter the beginning and or ending date of the pay period, and then yu can have it do your math for you as far as what is technically over time or not.