Relationship help please

[barnesy]

As you all know Im not a expert on databases but work in a volunteer basis in a small community area where we have no money for people to do anything for us.
Im proud because I did the other database and its up and working but this one has got me beat.
Im setting up one for the volunteers, which has computer knowledge, any basic training they need, when they are available and what tasks have been set for them.
I need to have a one form which has just their personal details but to be able to put in data on the other forms and it will link back to the volunteers name.
Ive attached what Ive done and Im sure your all going to laugh - but any help would be appreciated

Barnesy

 

[neileg]

OK, it's not obvious what you want to do here, but I'll give you a few pointers.

Each table has a field called EmployeeID, but in each case it's an autonumber. That's not right. You link tables together using key fields, by holding the primary key of the first table as a field in the corresponding record in the second table. In the second table this value is known as a foreign key. The foreign key must be the same value as the primary key in the first table, so you can't use an autonumber in the second table. Typically, you would mange these in a form/subform and the primary key would automatically be allocated to the foreign key.

You do need a primary key in each table, and this may well be an autonumber, but you need to give this a name relevant to the contents of the table, eg TrainingID, ComputerID.

The Employees table seems to duplicate what is PersonalDet, so you don't need Employees.

If you let us know what the purpose of the databse is, we can help you some more.

 

[barnesy]

let me see if I have this right, I need to remove the EmployeeID field in all apart from the main table and then assign a new field using FK?

What Im trying to achieve is a form with the employee details on it with a subform with the days they are available to work. I also need to record what computer programs they can use, and what programs they need training on (yes my nameis down for access training!)
Each volunteer is involved in varies tasks, eg support groups so I need to keep a record of what task they are doing, the location etc.

 

[ColinEssex]

No, Neil means that you still have the EmployeeID in the the other tables but just change them to numbers not AutoNumbers

Col

 

[neileg]

Try this for size.

 

[barnesy]

guess its easy when you know how........a big thank you for all your help I really appreciate it.



Barney