Setting up table and relationships

Microsoft Access 2003 (full product)
September 23, 2009 at 11:20:57
Specs: Windows XP
Ok I've been working on this for about 2 and half weeks now been goggling, using these forums and other but still my answers are illusive to me. So maybe if someone gets a chance they can help.

Here is what I’m trying to do. I have a 5 departments I’m in charge of in those departments I have 60 employees spread between them. Some of the 60 employees are trained in different areas but all have a "home" dept. I just can't seam to get the table setup down to make reports or forms. Here is how I have the tables setup right now.

Employee table----
Employee ID – Auto number (primary key)
Last Name - Text
First Name - Text
windows name - Text
Primary Dept - Text
Start Date of Primary Dept – date/time
Certified – Yes/No
Date of Certification – date/time
Training packet complete – yes/no

Dept table -------
Dept Id – Auto number (primary key)
Dept Name – Text
Date of Certification – date/time

Training table -------
Training ID – Auto number (primary Key)
Employee ID – Number
Dept ID - Number

Now my relation ships I have both Employee and Dept tables set to one-to-many to the Training table.

I need to be able to run a report for all employees that are working in one dept but also need to know who else is certified to work in that area to help cover overtime. This setup almost works but not quite what I need it to do first I can’t set up the employee’s certification date for the other department they may work in. Second if I want to run a query to pull out a whole dept and be able to determine who is the “home” dept and who is cross trained in the dept it won’t let me do that unless the “home” people are listed in both places. If anyone could help me setup the darn tables I should be able to get the forms (to add new people) and generate the reports.


See More: Setting up table and relationships

Report •


#1
September 23, 2009 at 11:57:37
I may have misunderstood how your organization is setup, but from my interpretation, your tables should look like this: http://ryantadams.com/ERD.jpg

-Ryan Adams

Free Computer Tips and more:http://RyanTAdams.com
Paid Tech Support: Black Diamond


Report •

#2
September 23, 2009 at 12:44:38
First thanks for the response but that still doesn't quite give me what I was needing. I will try and put the layout of how we are set up.

I have 5 Departments with about 60 people so its set up like this.

    Line 1     Line 2
     Jan        Mark
     Dan        Joe
     John       Dean

Of course there is 3 more lines and more employees but this is basically how we are set up to run.

Ok now Jan, Dan, John their home dept is Line 1. But Dan has been trained on Line 2 and is certified to work on that line but the date he was certified on Line 2 is different then the date he was certified on Line 1. Their certifications only last 3 years so I need to be able to run another report that tells me when his certification is up on the different lines. So I need say Dan to be set to Line 1 but when I run a report for Line 2 he will show up on their as well.


Report •

#3
September 23, 2009 at 14:16:55
That ERD I posted should work for that. The middle associative entity labeled "Employees-Departments" uses an employee_id (pulled from the Employees table) and a department_id (pulled from the departments table). Each "row" also has a date associated with it, and a indication of whether or not it's the "home" department.

In the Employees table you would have something like this:

1,John,Doe,JDoe
2,Jane,Smith,JSmith
3,Billy,Bob,BBob

In the departments table you would have:

1,Line1
2,Line2
3,Line3

The associate identity (Employees-department) would end up containing data like this:

1,1,1/1/2009,1
1,2,2/1/2009,0
2,2,1/1/2009,1
3,1,6/6/2009,0
3,3,6/6/2009,1

That indicates (going down the rows in order)
John Doe was certified to work on Line 1, which is his home department on 1/1/2009.
John Doe was certified to work on Line 2, which is not his home department, on 2/1/2009.
Jane Smith was certified to work on Line 2, here home department, on 1/1/2009.
Billy Bob was certified to work on Line1 (not home department) on 6/6/2009.
Billy Bob was certified to work on Line 3 (home department) on 6/6/2009.

I'm not very good at Access reports (which I assume is what you are using), but you should be able to generate all the reports you mentioned from that table setup. You can do things like return a list of all employees who are certified on Line 2, or you can return a list of all employees who are certified on Line 2 and where Line 2 is there home department. You could also do something like return a list of all employees who have a certification that expires with-in the next 3 months, and also show on the report whether or not that certification is in their home department.

-Ryan Adams

Free Computer Tips and more:http://RyanTAdams.com
Paid Tech Support: Black Diamond


Report •

Related Solutions

#4
September 23, 2009 at 14:26:21
One other thing, if you want to keep historical records of certifications, you will need to move the "date" into the primary key of the "employees-departments" table.

-Ryan Adams

Free Computer Tips and more:http://RyanTAdams.com
Paid Tech Support: Black Diamond


Report •

#5
September 23, 2009 at 15:57:06
Just for future reference, there is a Database Forum....

LIR


Report •

#6
September 24, 2009 at 08:57:00
Thanks for the help I see what I was doing wrong now. Thanks a TON for your help. I know there is a database forum and there is an office forum, I was working with Access which is a office forum so it was a 50/50 stake ;)

Report •

#7
September 24, 2009 at 09:23:42
So what did you end up doing?

-Ryan Adams

Free Computer Tips and more:http://RyanTAdams.com
Paid Tech Support: Black Diamond


Report •


Ask Question