Hey there

I've just started creating a database that will keep track of my client's information, who they work for and what days/hours they work. After being being given some articles to read on setting up a DB, normalization and relationships, I have managed to create a many - many relationship between Clients and Employers. I need another table/form (at least one I'm thinking) to track the hours. Some of my clients have more than one job and some work split shifts.

Can someone tell me what I'm supposed to connect the Hours Table to? Also, does anyone know where I can find an example of a table that tracks multiple people's work hours at multiple jobs?

I'm including a Word document that shows what I have right now (which isn't much).

Any help, advice or tips would be greatly appreciated.

tblClient:

ClientID
FirstName
LastName

tblJobs:

ClientID
EmployerID

tblEmployer:

EmployerID
EmployerName

tblHours:

HoursID
ClientID
EmployerID


A query could pull up a single clientID, relate that to tblJobs to pull up each EmployerID the person works for (duplicate clientID's can exist in this table) and relates the EmployerID here to tblEmployer.

A relation between tblJob and tblEmployer should be 1:1 at the Client level as each client should only work for an Employer one time right? Relating tblJob to tblEmployer doesn't do any good unless we bring tblClient into the picture - it doesn't give us information we are looking for right?

For hours worked, just make a ClientID field in that table as well as a HoursID field (primary key - can be autonumber in this instance). You can enter hours for any client and employer - and can use your clientID to show only hours which that client worked as well as tie in employers that way too.

I hope this makes sense. If I've overlooked something I'm sure someone will point it out.

Oh and if tblJobs is only for the purpose of creating a one to many relationship between clients and employers, then you don't need to have a primary key as the only information in this form is going to be foreign keys used to tie two tables together without creating extra fields in either tblEmployer or tblClient. What you COULD do is make them both primary keys by highlighting both fields and clicking the primary key button. This would prevent identical data like:

ClientID, EmployerID:

01, 01
01, 01

It wouldn't take that second set if you tried to enter it as you can't work for the same employer twice. Where you could do this:

01, 01
01, 02
02, 01

etc.

Thank you so much for replying and helping me out. i've printed off your answer and I'll go over it (and over it and over it...) to see if I can get my mind around it and get it to work.

I REALLY appreciate you taking the time to help me out.

tblClient:

ClientID
FirstName
LastName

tblJobs:

ClientID
EmployerID

tblEmployer:

EmployerID
EmployerName

tblHours:

HoursID
ClientID
EmployerID


A query could pull up a single clientID, relate that to tblJobs to pull up each EmployerID the person works for (duplicate clientID's can exist in this table) and relates the EmployerID here to tblEmployer.

A relation between tblJob and tblEmployer should be 1:1 at the Client level as each client should only work for an Employer one time right? Relating tblJob to tblEmployer doesn't do any good unless we bring tblClient into the picture - it doesn't give us information we are looking for right?

For hours worked, just make a ClientID field in that table as well as a HoursID field (primary key - can be autonumber in this instance). You can enter hours for any client and employer - and can use your clientID to show only hours which that client worked as well as tie in employers that way too.

I hope this makes sense. If I've overlooked something I'm sure someone will point it out.

Oh and if tblJobs is only for the purpose of creating a one to many relationship between clients and employers, then you don't need to have a primary key as the only information in this form is going to be foreign keys used to tie two tables together without creating extra fields in either tblEmployer or tblClient. What you COULD do is make them both primary keys by highlighting both fields and clicking the primary key button. This would prevent identical data like:

ClientID, EmployerID:

01, 01
01, 01

It wouldn't take that second set if you tried to enter it as you can't work for the same employer twice. Where you could do this:

01, 01
01, 02
02, 01

etc.

Hi Odin

I created the tables you suggested to me, but I don't think I got the relationships quite right. When I go to populate tblClient, I put the ID and names in and it allows me to expand to put in the Employer info. The problem is that when I put in an employer for one person, it's the same employer for all people.

This is what I have done so far. Could you please tell me what I did wrong?

Also, it asked me for information about a subdatasheet - the child and parent. I'm looking up and reading about that on a website to learn what I'm supposed to do with that.

Thanks Odin

tblClient:

ClientID
FirstName
LastName

tblJobs:

ClientID
EmployerID

tblEmployer:

EmployerID
EmployerName

tblHours:

HoursID
ClientID
EmployerID


A query could pull up a single clientID, relate that to tblJobs to pull up each EmployerID the person works for (duplicate clientID's can exist in this table) and relates the EmployerID here to tblEmployer.

A relation between tblJob and tblEmployer should be 1:1 at the Client level as each client should only work for an Employer one time right? Relating tblJob to tblEmployer doesn't do any good unless we bring tblClient into the picture - it doesn't give us information we are looking for right?

For hours worked, just make a ClientID field in that table as well as a HoursID field (primary key - can be autonumber in this instance). You can enter hours for any client and employer - and can use your clientID to show only hours which that client worked as well as tie in employers that way too.

I hope this makes sense. If I've overlooked something I'm sure someone will point it out.

Oh and if tblJobs is only for the purpose of creating a one to many relationship between clients and employers, then you don't need to have a primary key as the only information in this form is going to be foreign keys used to tie two tables together without creating extra fields in either tblEmployer or tblClient. What you COULD do is make them both primary keys by highlighting both fields and clicking the primary key button. This would prevent identical data like:

ClientID, EmployerID:

01, 01
01, 01

It wouldn't take that second set if you tried to enter it as you can't work for the same employer twice. Where you could do this:

01, 01
01, 02
02, 01

etc.

whats the difference between a client and an employer - that would make it easier to see whats going on

Hey gemma

I work with and help find jobs for adults with Asperger's Syndrome/High-Functioning Autism. They are my clients. When they get a job(s) we need to track their hours. The clients job(s) is tblEmployers. Some of my clients have more than one job/employer and some work split shifts (as well as having more than one job) just to make my life more difficult[g].

I tried following Odin's advice and made up the tables, but got a bit lost with the relationships. I understand the concept of Access and tables, normalization and such, but I can't wrap my head around the 'artistry' of it and making it all come together.

whats the difference between a client and an employer - that would make it easier to see whats going on

so as a guide, the tables you need are

clients (ie your clients)

employers( the employers who your clients work for)

jobs (clientid , employerid) - identifying which of your clients work for which employer, and therefore serving as a link between a many to many join

so

client 1 ..... m jobs m ........ 1 employers

one employer offers many jobs
one client has many jobs

------
fiinally you need a workedhours table attached to the jobs

so

workedhours(jobid {which identifies employer and client}, date [or week no - depends how you record hours worked] , hoursworked, payment, any other details you need storing}

--------
as you take it further you may need other tables, but hopefully this makes sense

Maybe this will help. I put together a sample DB, just to give you an idea of what I mean.

Now, you have to build forms to control this.

First a form to add Clients, Jobs, and Employers.

But you have to remember the order. You need to create a client first. Then pick from available jobs and assign a job. If you need to add a job, you can do that if the employer exists - otherwise you would need to add the employer first.

Then you need a form to add hours where you can pick a client, then pick which job the hours are for and enter those.

I made the ID's in their main tables AutoNumbers - you don't have to - you can manually assign ID's if you want.

Now with this design - you can have clients switch jobs all over the place, but you still will always have the hours information because you recorded in that table the client who worked the hours, and which job it was for. So if someone goes through 5 jobs - you have all the hourly data still.

One thing you will never do is to remove or change a job or an employer. Once that data is in there, it needs to stay the same. If the job title changes, I would make another jobID personally.

Anyway...I just kind of threw this together, so it's not super thought out. And depending on what you want to do, you may want to change it.

Oh, and one important distinction. The tblClientJobs table is not all that important. All this table does is relate what job(s) a client is currently working. You can delete a record if they are no longer working at that job. Again you need a form to assign clients to jobs. Deleting records in here or changing the jobID to something else will only affect what job is currently assigned.

If you really think about it, you may not even need it if all you are doing is tracking hours - because in the hours table you have the client ID and the job ID as well. It just depends on what type of other data you're looking for - say a report showing what jobs clients are currently working - you will need that table.

Also, you could create a table to store the dates which an employee worked for a particular job (employer) - have hire and term dates - these you keep forever and could do reporting on how many jobs, etc.

Anyway it's up to you what you want, but plan everything out and hopefully this quick sample will at least give you the ideas behind the design.

You'll notice I have no "relationships" per se at all. All of my relationships are done in queries.