relationships

[tmarsh]

Hello. I'm new to databases and anything I know I've read up on. I know that relationships are essential so I'm trying to sort a database in ms access that I have inherited at work. It contains health service staff and their record of training - various training that they do regularly. I've managed to put the data in 1nf but am having difficulty after that.

A couple of questions:

If I give each staff member a unique id do I need to link their job title to it? Job titles chang quite a lot.

Other information about courses, dates of training, trainers etc. - should they be linked to staff name via separate tables?

I'm using a spreadsheet example from microsoft that shows me how to go from raw data to 3nf but I'm not sure how to split the information, so if you have any suggestions or know any good guides I'd be grateful for your help.

Thanks.

 

[ColinEssex]

Each staff member should have a unique ID. Usually an AutoNumber (call it StaffID). The job title is part of the demographic data so change it as you would a change of address.

You need a table for available courses, with an ID called CourseID.

You need a table for TrainingDone. The TrainingDone table should have these fields as a minimum

StaffID
CourseID
DateTrained
TrainedBy

Col

 

[noahsmom]

Hi tmarsh. I'm new to Access, as well, and I am learning through forums like this. From reading ColinEssex's response and from what I've read in my textbook, an employee is assigned a unique number (sorta like their social security number). It identifies that one employee to any fields within a database. From what I understand about relationships, a unique number (or SSN) is a one to many relationship.

 

[ColinEssex]

Use an AutoNumber to create a unique number and to link to other tables. That way there's no need to change it.
If you use a number that the user types in themselves (like an SSN) then you'll hit problems if they do it wrong and you have multiple entries in the 2nd table

Col

 

[tmarsh]

The job title is part of the demographic data so change it as you would a change of address.


Col

Thanks for that Colin (and thanks to the others for their replies), I've also got base and department, are they treated the same as job title?

 

[ColinEssex]

If you have a multi site hospital trust. You may like to consider doing Cascading ComboBoxes for site (base) and Dept.

Then you can select SiteA from the 1st ComboBox and only the Depts for SiteA will appear in the 2nd one. Also you may have a dept that is on several sites (like X/Ray) for example, so identifying them by site is recommended.

If you go along this route, search for Cascading ComboBoxes, there are good examples posted.

Col

 

[tmarsh]

Still haven't got my tables sorted yet. I'm using a spreadsheet example from microsoft that shows me how to go from raw data to 3nf but I'm not sure how to proceed.

Each staff member should have a unique ID. Usually an AutoNumber (call it StaffID). The job title is part of the demographic data so change it as you would a change of address.

I've given staff a unique id but am not sure how to sort dept/base/course etc. I have

A Employee_ID LNAME FNAME JOB_TITLE

B BASE DEPT/WARD

C COURSE DATE TRAINERS TRAINING_VENUE

These came from 1 original table and I thought tables as above A would be a table but how do I link the rest. Using the excel procedure I put the data in 1nf and then am supposed to create the tables and delete multiple instances of staff name. This helped me to see a bit but when it came to base and dept I was stuck. I need to do this (below) but how are the tables linked? Do I need staff_id in each of these tables?

If you have a multi site hospital trust. You may like to consider doing Cascading ComboBoxes for site (base) and Dept.

I'm trying to take the existing data with me rather than re-type.

Thanks.