Database design troubles…

[DannA]

Newbie needs help… with this time-card design.

I think I need 3 tables

DailySetup(repeated on all time cards) dayID, DateWorked, Crewcall, GeneralWrapTime

Employees: empID, Name, ContractNumber, JobTitle etc.

DaysWorked: dayID, empID, IndividualStartTime, MealBreak, EndTime.

Rather than fill (on a daily basis) each time-card for each employee, I want to enter times for ALL employees on a grid/template/portal for each day and then at the end of a work week print each employees individual time card. Entering all employees on one grid or in one portal window should (i hope) make it easy to look at the average hours worked for a given day and calculate the approximate cost of that work day.

I wanted to create a Layout with the repeating info for a work day at the top of my layout

DateWorked CrewCall GeneralWrapTime

Then have a portal match the dayID and start entering individual times for each employee. Sounds easy so far…..here’s the twist I can figure out.

The problem I face is that every time I enter a new daily setup I end up with no matching fields in the portal and I must enter the employees again (can I copy the employee names/ids day after day in the portal??) Is my design all backwards?? Is there a way to extract info from a template (like an Excel sheet) and fill the proper records in my DaysWorked table??

Thanks for any push in the right direction.

--Dan

[danjacoby]

3 tables is right.

1 Employee table, containing info on each employee (name, ID, etc.)

1 Job table, showing info on each specific job

1 TimeCard table, with individual records for each time one employee works one day.

The Job table is where the general info on a daily job is entered. It includes a portal where employees can be entered -- but the relationship is to the TimeCard table.

Time cards are printed from the TimeCard table.

Another portal in the Employee file, linked to the TimeCard table, shows jobs each employee worked.

Hope this makes sense.

Dan

[LiveOak]

I agree with Dan's idea for the files needed. I would possibly modify the TimeCard file. If you wish to track time by charge number or job in increments of less than one day, I would make a record in the TimeCard file the smallest billing entry. This will allow summarization by project, charge number, etc.

All other matrix displays of hours can be cooked up using relationships or globals.

-bd

[DannA]

Thanks for the replies...

I'm still not sure how to design my application...

This time sheet software is for one JOB (a film i'm working on) so i don't need to bill anyone based on employee hours. I just want a better way to keep track of employee hours and print a timesheet at the end of the work week.

i had the general info(valid for every employee) (DateWorkked, Camera/ShootCall and CameraWrap) on the top part of my layout then in a portal (linked on the DateWorked) i could enter (one by one) the employee that worked (his ID) and the hours worked. OK so far...

But i would rather see my portal filled with all employees and blanks in the hour fields, that way i could just enter all the needed hours info in the proper fields for that employee in my portal.

Just like an Excel spreadsheet that is used like a template(column1 = employeeNames, column2 and others = start, lunch, start, end of day times), and on a daily basis you wipe out column 2 and up(keep all the employees names)and enter the new hours for that work day.

So if this is a better description of what i would like to do,

i sure could use a hand/push in the right direction!!!

Thanks

--Dan