mnature, I'm ready for the relationships!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need help with the relationships now. If you can help me set up the first
set, I'm sure I can set the rest up. Have the following:

tblEmployees
EmployeeID (PK)
EmployeeInfo (Name, Rank, SSN, Gender)

tblCompetency
CompID (PK)
CompetencyInfo (CompTitle, CompLevel)

tblWard
WardID (PK)
WardInfo (WardName, WardNumber)

tblWardComp
WardCompID (PK)
WardCompInfo (WardID, CompID)

tblPersComp
PersCompID
PersCompInfo (SSN, Comp, CompDate)

How do I tie alls these together so I can say: "If SFC Traver is assigned to
Ward Surgical ICU, he needs *these* competencies and then enter the dates he
does them"?

I hope I did the table design right....I had a LOT of help :)
Thanks!
 
I would expect to see the WardID in a table (maybe tblEmployees) with the
EmployeeID. It is real unclear what you would use WardCompInfo for.
tblWardComp should have WardID and CompID fields.
 
OK, just glancing through, I think I see what you want to do. You have
certain competencies that are associated with certain wards. Then you have
employees that have certain competencies. You need to have some way to check
that when an employee is assigned to a ward, that their competencies are
current for that ward, and also when they next need to be retrained in those
competencies. Is this correct?

Also, an employee can be suddenly reassigned to a different ward, so you
need to be able to update which ward they are currently assigned to?

Do you also want to sometimes see which wards an employee is currently
trained for?

Since you are working with medical personnel, this does become a bit more
tricky than most businesses. Still can do it, but I know how managers move
people around as if everyone can do every job equally well. This would be a
good tool for showing how easy it is to move from one place to another.
Clever, really.
 
Correct for the most part. Each ward has a group of required competencies.
Depending on what ward the employee is assigned, s/he will need that group of
comps. These are usually required annually, so I do need to see what is
current. Yes, employees do get moved around ALOT. Some comps are needed for
multiple wards (i.e. Blood Products Administration). Don't need to see what
wards an employee is qualified for, because once they are moved, they would
get the missing competencies. But since I would have tied the comps to the
wards, the only ones that would be null (once I moved their ward assignment)
would be the "new" ones for that ward. Make sense?

Thanks for all your help. Have a done a decent job with taking your
directions in making the tables?
Thanks
Dan
 
"Each ward has a group of required competencies" This statement suggests you
have a table as I have suggested to you at least twice.
tblWardCompetencies
WardCompID
WardID
CompetencyID
 
And I took it...it's the fourth table listed:
tblWardComp
WardCompID (PK)
WardCompInfo (WardID, CompID)
Did I list it wrong or do it wrong....?
 
Duane- I see where I went wrong in my post. I was using the directions
mnature had given me to post my tbls. In () are the ACTUAL field names. The
*Info was to let you know that. Sorry for the confusion.
 
tblEmployees
EmployeeID (PK)
EmployeeName
EmployeeRank
EmployeeSSN
EmployeeGender

tblCompetencies [I prefer to spell out word completely, because then I don't
have to remember what the abbreviation stands for a year from now]
CompetencyID (PK)
CompetencyTitle
CompetencyLevel [I have no idea what this field is for. If you can have
different levels for the same competency, then you will have duplicate titles
in this table.]

tblWards [Standard procedure is to use a plural for table name, and singular
for field names]
WardID (PK)
WardName
WardNumber [This table is just for the different types of wards that you
have. If you have multiples of the exact same type of ward, then that needs
to be addressed elsewhere.]

tblWardCompetencies
WardCompetencyID (PK)
CompetencyID (FK)
WardID (FK)

tblEmployeeCompetencies [If you are using an employee table, then don't
change what you are calling employees. If you would prefer to use the term
personnel, then use it consistently throughout your database]
EmployeeCompetencyID (PK)
EmployeeID (FK)
CompetencyID (FK)
NextTrainingDate [If there is no next training date, then the person doesn't
have the competency. If it is past the next training date, then the person
has not retrained on that competency.]

Once your tables look like this, you can go in and connect similar ID's.
The EmployeeCompetencies table will connect both to Employees and
Competencies, and the WardCompetencies table will connect both to Wards and
Competencies. By keeping the primary and foreign keys with the same name, it
is easy to see how they relate to each other.

You can then create queries based upon the EmployeeCompetencies and
WardCompetencies tables, and their associated tables.
 
Thanks a bunch! I see how easy it will be now to add wards, competencies, etc
vs. the way I had it set up before (to add a competency I would have had to
add it to the forms and queries and reports....). Thanks for everything! Now
I'll have to brush up on my query building as now I've got MANY tables to
chose from....! I'm sure I'll be back with questions later.... :)
 
I'm glad that it seems to make sense. It is always difficult to let go of
the spreadsheet model (which seems so nicely intuitive) and go to a database
model. The queries and forms for this might be a little tricky, but it is
good for you to work on them first, and then come back with questions. You
will probably find that subforms will be very useful.
 
Back
Top