Relationships in a mess !

[Sandie]

Hi All,
I'm new here and seeing as this is my first post i'd like to say hi to everyone. This forum was recommended by a friend so i thought i'd give it a try. I'm working on a staff training database which is almost complete but i've run into some problems which i think are being caused by the way i've set up my relationships. So, i was wondering if one of you guys could take a quick look and see if anything looks out of place.
Bear in mind i'm a novice and this is the first time i've really used relationships at all.

Thanks a lot,
Sandie

 

[Mr Smin]

Hi,

I think you need to specifiy 1 employee to many training events.

Can a manager also be an employee? Your existing relationship there makes sense but there might be another way to implement that concept.
Consider a new table tblSubordinates with a relationship 'one employee to many subordinates'. This would also accommodate a hierarchy of management.

Employee ReportsTo
Bob --- Tim
Sarah --- Tim
Tim --- Larry

 

[PaulWilson]

Another method to accommodate a heiarchy is described in the queries chapter in the Access Cookbook. It's a self-join concept where one table provides all of the information and you can have many levels of supervisors and supervisees. It's recursive.

Don't quote me on the book but it's by O'Reilly and Getz is the first author.
Worth a look!

PaulWilson

 

[VilaRestal]

Also, perhaps the Block field in tbl_Jobs should link to tbl_Blocks
Maybe Jobs are a subtable of Blocks (every job must belong to a block) in which case there'd be no need to have both fields in tbl_EmployeeDetails - just job, which would give you the block by implication.

At the very least:
Enforce referential integrety on the join between tbl_EmployeeDetails and tbl_TrainingDetails
You don't need to store EmployeeName in tbl_TrainingDetails because you have that link
And join JobID in tbl_TrainingDetials to tbl_Jobs