Can't figure out the relationships to make!!!

[dp1981]

Hi to everyone. I am trying, have been trying, will probably kill myself trying...to figure out the best way to make tables/relationships with the information I have already imported into my db. If someone could please PLEASE help me, I would appreciate it. I'm a visual person, and I have so many pieces of paper around my desk that I can't even see the top of it anymore!!!

Background info, I am building a db for employees and courses they MUST take. However, there are 60+ courses, only 4 of those courses are frequently taken (the others are more "train the trainer" and such). But which of those 4 courses you need to take depends on whether you're a supervisor or tech. So if you're a supv, you need to take courses 1, 2, and 4. Tech, you need to take 1, 2, and 3. My "dream" is when I later make a form, I can get the boxes to darken for the courses that don't apply to the employee.

Attached is a print screen (sorry it's .doc but I'm limited with the programs on my PC) of my current ONE table in design view so one can easily see how many fields there are.

Now, I realize to normalize, it's ideal to have only ONE subject matter per table. So, in a dream world, the tables that SHOULD be created to be completely normalized are as follows:

T_Employee
LastName
FirstName
MiddleName
SSN

T_EmpDetails
RNK
Supv
PPOCCGradeStep
EOD
DOBbasdSCD

T_Job
ParaLine
Para

T_JobDetails
Unit
DutyLoc
Activity
PositionTitle
Type

T_Courses (contains date course taken, IF taken)
NSPAE
NSPAS
.......etc..

So, hopefully you can see why I'm having such problems. I know that I could only create lookup lists for "Type", "Para", and "Rank". All other fields, although some do have repeating information, would result in HUGE lookup lists of probably more than 200 things.

Does anyone, anyone have any ideas??? Please!

 

[CraigDolphin]

So, in a dream world, the tables that SHOULD be created...

In the real world too.

So, are you saying that you are not prepared to create a normalized db to solve your issues?

If so, most of the people here who can probably help you, probably won't.

If not, then I'd be happy to go over your data model and see if we can get you what you need.

 

[dp1981]

In the real world too.

So, are you saying that you are not prepared to create a normalized db to solve your issues?

If so, most of the people here who can probably help you, probably won't.

If not, then I'd be happy to go over your data model and see if we can get you what you need.

Um...I'm not too sure what you meant, to be honest. Of course I want to create a normalized db. But to do that, I need to have relational tables. And THAT'S the part I'm struggling with...?

 

[CraigDolphin]

Ok then. Just checking because your post kept saying 'in a dream world' which to me suggests something unattainable. It suggested to me that, even though you knew that's what you need to do, you didn't think it was an option you were prepared to entertain.

Now that that's cleared up, lets look at your real world situation. From your first post:

-You have many people you need to track.(These people each have one job.)
-There are many jobs to track. Each job is categorized as one of two (many) 'job types':
-There are two (many) job types to track: tech and supervisor.
-You also have many TrainingCourses.
-Each person (one) may take many courses.
-Each Course (one) may be taken by many people.
-Each training class (one) must be taken by supervisors, or by techs, or by both (many) job types.

Some additional clarification might be useful here also.
Do you only need to only know if an employer has done a particular course at a yes/no level?
Or
Do you need to know if an employee has done a course, and when they completed the course?
Or
Do you need to know not only if they have completed a course, and when, but how many times that employee has attended that course (i.e., if they must do a particular course once each year and you need to track the history of course attendance too)

 

[dp1981]

Ok then. Just checking because your post kept saying 'in a dream world' which to me suggests something unattainable. It suggested to me that, even though you knew that's what you need to do, you didn't think it was an option you were prepared to entertain.

Now that that's cleared up, lets look at your real world situation. From your first post:

You have many people you need to track.
These people each have one job.
There are many jobs to track.
Each jobs comes in one of two flavors, or 'types': tech and supervisor.
You also have many TrainingCourses that must be taken by many employees.
Each training class (one) must be taken by supervisors, or by techs, or by both (many) job types.

Some additional clarification might be useful here also.
Do you only need to only know if an employer has done a particular course at a yes/no level?
Or
Do you need to know if an employee has done a course, and when they completed the course?
Or
Do you need to know not only if they have completed a course, and when, but how many times that employee has attended that course (i.e., if they must do a particular course once each year and you need to track the history of course attendance too)

need to know if an employee has done a course, and when they completed the course

 

[CraigDolphin]

Ok, checkout the attached example for the structure I believe that you need to use. Note: I haven't added in all the fields for the various tables that you might want (like maybe SSN for an employee, location for a job, etc).

There's also three queries to show you how to use this structure to get a list of all required courses for each person, another list showing the courses completed by each employee, and when, and a third list showing the required courses that have not yet been done by each employee.

The sample data is, of course, made up. Only one employee has done any training at all (as you'll see if you look in the EmployeeTrainingEvents table).

Post back if you need any further help or clarification.

 

[dp1981]

Ok, checkout the attached example for the structure I believe that you need to use. Note: I haven't added in all the fields for the various tables that you might want (like maybe SSN for an employee, location for a job, etc).

There's also three queries to show you how to use this structure to get a list of all required courses for each person, another list showing the courses completed by each employee, and when, and a third list showing the required courses that have not yet been done by each employee.

The sample data is, of course, made up. Only one employee has done any training at all (as you'll see if you look in the EmployeeTrainingEvents table).

Post back if you need any further help or clarification.

Wow, now I'm really confused. I see definitely some of the things I know are needed in my db. But some of the fields you created [such as "employee-specificfield1"] I don't understand. I swear I'm not an idiot. I can do the more difficult things in Access, like make 2 combo-boxes dependent on each other in forms, but can't seem to get the simple stuff! Can you clarify the fields and how they relate to the ones I originally posted?

Thanks, by the way. You've been very very helpful!

 

[dp1981]

Ok, checkout the attached example for the structure I believe that you need to use. Note: I haven't added in all the fields for the various tables that you might want (like maybe SSN for an employee, location for a job, etc).

There's also three queries to show you how to use this structure to get a list of all required courses for each person, another list showing the courses completed by each employee, and when, and a third list showing the required courses that have not yet been done by each employee.

The sample data is, of course, made up. Only one employee has done any training at all (as you'll see if you look in the EmployeeTrainingEvents table).

Post back if you need any further help or clarification.

Ok well I think I've come up with something...but am a little unsure about my "tbl_Job" setup and whether my new way of doing this is set up correctly. Can someone take a look at my attachment and tell me if my relationships will work please? CraigDolphin, you definitely got me further than I've gotten myself in a long long time. Lol. Thank you so much!!!

 

[CraigDolphin]

Hi again,

It looks like the basics table structure is ok and follows what I showed you earlier. The fields that were confusing you were there simply to show that you could place other employee-specific fields, etc in that table. I hadn't actually looked at all the fields in your original model since I figured you would understand which table they belonged to better than I.

After all, 'PPOCCGradeStep', 'EOD', and 'DOBbasdSCD', etc means absolutely nothing to a biologist like me so I don't know if they are specific to an employee (person) or to a job that the person holds. Whichever entity they are specific to, they should go in that entity's table.

So, the tables should work for tracking if, and when, an employee has done a training course. The queries I put in my original example should guide you how to make lists of course required, completed, and not completed for each employee. You could use these to make subforms that would show up on your employee form. Or you could base reports on them.

One quick comment though: the table tbl_Activity in your scheme currently has a one to many relationship with tbl_Job. That's fine if one job can only ever have one 'activity'. If, however, one job might have more than one activity then you need a junction table between tbl_Activty and tbl_Job.

If you don't know what a junction table is: you already have an example of one of these in your table tbl_CoursesRequired. You need a table of this sort wherever a mnay to many relationship exists. So, if a job can have many activities, and an activity may have many jobs associated with it, then you need a junction table. If not, then never mind


Good luck with the rest of your project.

 

[dp1981]

Hi again,

It looks like the basics table structure is ok and follows what I showed you earlier. The fields that were confusing you were there simply to show that you could place other employee-specific fields, etc in that table. I hadn't actually looked at all the fields in your original model since I figured you would understand which table they belonged to better than I.

After all, 'PPOCCGradeStep', 'EOD', and 'DOBbasdSCD', etc means absolutely nothing to a biologist like me so I don't know if they are specific to an employee (person) or to a job that the person holds. Whichever entity they are specific to, they should go in that entity's table.

So, the tables should work for tracking if, and when, an employee has done a training course. The queries I put in my original example should guide you how to make lists of course required, completed, and not completed for each employee. You could use these to make subforms that would show up on your employee form. Or you could base reports on them.

One quick comment though: the table tbl_Activity in your scheme currently has a one to many relationship with tbl_Job. That's fine if one job can only ever have one 'activity'. If, however, one job might have more than one activity then you need a junction table between tbl_Activty and tbl_Job.

If you don't know what a junction table is: you already have an example of one of these in your table tbl_CoursesRequired. You need a table of this sort wherever a mnay to many relationship exists. So, if a job can have many activities, and an activity may have many jobs associated with it, then you need a junction table. If not, then never mind


Good luck with the rest of your project.

Thank you so much for the time you've taken to guide me through this and help me with the basic structure. I'm very appreciative!

"Activity" is actually another way of saying "Sub unit" but they refer to it as "activity" for some reason. So it IS possible to have a job have only one activity, but an activity to be repeated. So by what you said previously, I think I should be ok. Hopefully...fingers crossed. Lol.

Thanks again.

 

[CraigDolphin]

Glad I could help. All the best with you project.