Relation between tables

[vincentsp]

Hi I'm making my first Time sheet layout in Access, everything is working out except the relations between my tables.
I have made a simplified version of my database in a separate file which I've attached, I would appreciate if somebody could take a look and explain where to make the proper relations.

[orange]

It is often better for you as developer, and for us as readers, if you write a brief description of WHAT you are trying to accomplish in plain English.
That helps us all to know What things are involved, what some of the attributes are and What business rules/facts exist. All of this helps with database and table structures.

Eg We are a small company of 20 employees. We build, sell and maintain widgets. All employees are "hourly" in that they get paid based on hours worked. Our workday begins at 8:00 AM and ends at 4:00PM. Our normal workweek is Mon -thru Friday. Work outside of those hours is considered overtime. ...bla,...bla

[vincentsp]

Of course .
I work at a relative small company were I'm a project leader in The Netherland.
The projects I do are done with +/-20 employees on-site and consist of maintenance work.
The work is done at different hours and on multiple work orders per day.
Because every project leader is responsible for there own paperwork, I started to make a database to make life easier.

At this moment I'm working at a time-sheet application, eventually making it possible for everybody working on the project to fill in and print there own time-sheet.
At this moment I have made three tables one with all the employees, one with the time-sheet data (start time, end time, project/work orders, and date) the last table contains all the work orders. My idea is to have a form were you first choose the employee and after that using a multi record sub-form to fill in the time-sheet data.
My choice for the multi record sub-form is based on the fact that the employees are working on several work orders per day.
After everything is filled in I want to save and print the time-sheet using a query which asks which employee and which date, and finally opens the report of the selected employee en date.

The problem I encountered is how to make the form and relationships between the different tables.
Especially between the form with employee data and the sub-form with the time-sheet data.
The query and report is not the problem, thanks to this forum

I hope this all makes sense..

[orange]

At this moment I have made three tables one with all the employees, one with the time-sheet data (start time, end time, project/work orders, and date) the last table contains all the work orders. My idea is to have a form were you first choose the employee and after that using a multi record sub-form to fill in the time-sheet data.

The database I looked at had only 2 tables - there was no workOrder table.

Sounds like your basic table and relationships would be similar to the attached jpg

Your forms etc are all based on your tables and relationships. Get those designed to support your needs, then move to queries, forms and reports.

If employees can enter their time start and end, I think your data will be more accurate. If employees fill in their time sheets at the end of the day, you're dependent on their recollection for accuracy. The related interfaces to the system will depend on how your processes are set up/working.

Good luck.

[vincentsp]

Thanks for your reply.
I choose for the option to let them fill in a complete time-sheet ones a day.
Because most of our employees get there work orders in the morning and come back in the office at the end of the shift, by that time they already filled in all the work orders of that day by hand.

I will start with you structure today and keep you posted!

[vincentsp]

Just finished my new structure as shown in the image below.
Now I'm trying to make a form as I described earlier, unfortunately without much luck..
I have started with making a multi record form based on the TimeSheet table using the StartTime,EndTime, WorkCode, WorkOrder and remarks.
This because these are the only fields that need changing.

Now I'm stuck on how to create a form for the Employee data and timesheet date and incorporate my multi record form (as subform).
The idea would be to only fill in the Employee and date ones and use the multi record form to fill in the times worked on each work order that day.

[orange]

I responded to another poster yesterday re form/subform here.
There is a demo database in post #8 .
It is a different subject matter, but if you read the thread dialog and look at the demo, you may find it useful.
Good luck.

[vincentsp]

Thanks for the link and effort, works great now!!!

[orange]

Glad it was useful. Happy to help.

I still recommend that you take your latest model, identify what you want out of your database (reports/forms)
then build some representative test data and scenarios. Test your model with these test scenarios. If any issue arises, determine exactly what the issue/cause is and adjust your model, or your data accordiingly and test again until the model supports your data. Then proceed to build the database.
Good luck.