Recording the hours worked on different jobs by different employees

[Tokenblood]

Hi all!
These forums seem like a good place to ask for some help. I have volunteered to help produce a database, I did this before I knew much about access. I consider myself quite competent with Excel and thought I could step into Access simply... I truly am lost with how I might design this database, not knowing well enough how records in fields can relate to separate tables and back again.

Anyhow this is the situation; In our service department there are 9 electricians, this can change seasonally. They each repair different machines, but sometimes can work together on the same repair job. Daily an electrician fills out a job card with which "Job Number" he worked on and how many hours he spent on each. The database is to be used by others, so it needs to have a form where you can type in a job number and see who worked on that job and for how long, and also be able to enter an electricians name and see what jobs he has worked on and for how long. Also, if possible, it would be good if it could be sorted by dates, so the user could see how many hours an electrician has worked within a range of dates.



The other issue is that each electrician has 3 labor codes, being, 1 for normal labor charges, 1 for warranty work and the other for hours on a callout. For example. John will have codes: EAA010, EAA015 and EAA110. Bob will have codes: EAA020, EAA025 and EAA120. This is true for all 9 Electricians. I am guessing that the Job Number would be the key field?

Icing on the cake is that I won't be around forever and so the user needs to be able to add a new electrician if they employ a new one.

Since this is my first dabble into access i am struggling, any advice would really be appreciated..

Thanks in advance!

[orange]

It would appear that you have the "many to many" situation.

You may wish to research junction tables and search youtube for "trainsignal many to many msaccess"

[Tokenblood]

The trainsignal videos are all very good, thanks for the tip Orange. I can see how the many to many relationship would help me, but i am still unsure of the basic design of the database. Should i have a table for each electrician? or a table for each of the codes? or just 2 big tables, one with the job no and details and one with all the electricians?

Once i understand how it should be structured i can learn the relationships and then the form designer :/
cheers

[orange]

Review this process and then try it with your own data.

http://www.rogersaccesslibrary.com/T...lationship.zip

[burrina]

I already have something very similar to what you are describing,here are a couple of screenshot to help.If you need table structure let me know.Main

[orange]

Tokenblood,

It isn't clear what "helping produce a database" entails.
Are you expected to support this database as well? Who are you helping - someone with database experience?
Did you want readers to offer already built solutions, or are you trying to learn database as you proceed?

[burrina]

If I can help let me know.

[orange]

Dave,

If your post #7 is referring to my post #6 -
I wasn't inferring that you would offer support or anything.
I was specifically asking Tokenblood --the OP -- what his expectations and involvement were.

My post was not directed to you in any way.

[burrina]

Then I sincerely offer you my apology! I apparently was off-base! sometimes I am too sensitive! Thanks, and again, I am sorry!