Table structure for time db

[scottappleford]

Hi

I am trying to set up a table structure for timesheets. I have read through the forums and found some good advice connected with what i can do to develop the db, however i am having trouble with the table structure e.g. in a day you can work RT, OT & DT.

I have a tbltimecard which will be the main form with TimecardID(PK), date, work codeID, companyID etc.

I then have a tbltimecadhours with timecardhoursID (PK), timecardID (FK), employeeID (which needs to filter according to the company selected), then I have RT, DT, OT number fields for the hours, but DT & OT could be left empty on alot of occasions.

i have a work code table and a work code description table joined with the work code linked to the tbltimecard.

I also have tblemployee linked to tblecompany with tble company linked to tble timecard.

Is this correct?

I could have the RT, OT & DT fields in one field as a look up but then i could have the name of an employee repeated upto three times on one sheet. I suppose the answer would be a new table? I am not sure.

Thanks for your input?

Scott

 

[Len Boorman]

Basically I think that you want a single transaction table to which control tables are linked.

The Transaction table would have

Peron, what they worked on,for how long, date and an indicator for RT,DT or OT.

Yes you will have multiple lines if somebody worked different tasks on same day and also some hours were DT. Do not really bthink you will get around this

Control tables used to control Work items, employee etc so that you do not get any orphan records. It's a bit like a stock system where the toatl stock is the sum of transactions. Here total hours is sum of transactions

Yoiu will need some controls to find who has not book normal hours for working days etc.

Its a bit of a game initially cos users can do amazing things when they try

L

 

[scottappleford]

db attached

I have attached the db - if you would review it for me?? Please!

I was thinking more of fields named RT, OT, DT and set as number and leave blank the OT or DT if no hours worked? At least that way i will only see the employee name once.

I mean to extend the timesheet from a day to a week would only complicate matters? as the controlling factors (theme) has to be 1st by work task, 2nd by company. with employees and hours (RT, OT, DT) as a sub form.

So each sheet has to have one task with one company with the list of employees and hours on it.

This is a project i want to do as it will make analysis alot easier and assist in seeing that the correct hours are applied for. You can imagine the task if an employee could appear on upto four tasks a day, especially with a paper system.

Thanks

 

[Len Boorman]

Cannot open your database . Is it 2003 ?. Only have 97 and 2000.

Okay you must go with your thoughts, you know what you need to achieve but do think very carefully about how you will for instance compile a report detailing the sum of hours worked on a project and also detect who has not accounted for their time.

These are areas that can give a lot of problem

L

 

[ScottGem]

I have attached the db - if you would review it for me?? Please!

I was thinking more of fields named RT, OT, DT and set as number and leave blank the OT or DT if no hours worked? At least that way i will only see the employee name once.

I mean to extend the timesheet from a day to a week would only complicate matters? as the controlling factors (theme) has to be 1st by work task, 2nd by company. with employees and hours (RT, OT, DT) as a sub form.

So each sheet has to have one task with one company with the list of employees and hours on it.

This is a project i want to do as it will make analysis alot easier and assist in seeing that the correct hours are applied for. You can imagine the task if an employee could appear on upto four tasks a day, especially with a paper system.

Thanks

Having field named RT, OT, DT constitutes a repeating group which violates normalization rules. You can do your reports with grouping to eliminate repeating employee names.

 

[scottappleford]

Hi ScottGem

I was thinking that it violates normalization rules, but if I only have an hours field with an hours type this will mean an employee name can be repeated twice so does this violate normalization rules as well?

How would you deal with the hour classification? - it makes a difference because it is a different rate.

Thanks for you help

scott

 

[ScottGem]

No, because the employee name isn't repeated, only the employeeID as a FK. Especially since each type is a different rate, does it make more sense to do it the way I'm suggesting.

 

[scottappleford]

Yes - sorry i agree - i am just stuck on table structure/forms - i have not even got to the reports yet.

thanks again for your help - as i am really stuck here

scott