Design Help

[jbevans]

So glad that I found this place! :-)

Ok, Im kinda familiar with Access although its been quite a while since I used it so bare with me. Im going to try and describe this the best I can...

I will have a table with Employees and info
I will also have a table with Job# and info

These employees may end up doing hours on several different jobs within a pay week. How would I set that up?



Also, these employees have their own Emp ID but then also belong to a certain Crew (Would I need a table for Crews with employees listed there) but can be moved around at any given time to fill absenses/vacation/etc. Within the crew, on a job#, they have to complete a certain number of miles per week. That is based on a crew of all and how many man hours were used to get those miles. (It is not based on employee)

Info that I would like to be able to pull at any given time as examples would be.....
*Job#? 11/1/2001 -11/27/2001, how many miles did Crew get and how many man hours did it take?
*How many hours did this Employee have within this job#? (Bonuses are figured on employee percentage of time involved on the job)
*I really need the hours to go to that employee every week but also need to be able list what job it goes to. They may have 10 hours on job1 and 30 hours on job2.
*But then also need to be able to enter miles/man hours for the Crew# for the week.
*Need to be able to pull up a Job# and see any total man hours/miles for a period of time.

So................ what tables and PK/FKs would you set up to link all these things?
Thanks for any help!!

[June7]

Review the 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

Design a data schema and post for analysis. Get the data structure designed before thinking about output.

[dblife]

It is easy to get bogged down with the complexity of something like this but when broken down into normalised tables, it is quite straightforward.
A table for employees.
A table for jobs.
A table to link them, recording who was where etc
A table for customers.
Maybe a few others.
How you put them together is the trick to designing easy to use queries and forms - let me know if you would like more help.

[dblife]

I have attached an example I designed to show you the simplest version of what I think you might need?
You create or select a job from the first tab, on the second tab you choose a date that work happened on, who did the work and how long for.
You can hit the report button at any time to see what the status is.
This can be refined to show work by week or employee or even job if required.
Let me know if it is right for you and you would like help developing it.
tom"at"my-db.co.uk