Linking to two of the same table - confusion over database relationships and forms

[whitespace]

Hello all,

I'm developnig an Employee/Staff database and have done a lot of work trying to get the structure right on paper. I'm coming to now implement the desing but there's something I'm stuck with.

Basically I have a SalaryScaleFrom and SalaryScaleTo field in the Post table, I want the user to use a drop down box from table tlkSalaryScale, however I'm unsure how to create this relationship as they both need to access the same table.

I've currently set the relationships up (in relationshp view) as two of the same table ie: tblPost to tlkSalaryScale on SalaryScaleFromID and tblPost to tlkSalaryScale_1 on SalaryScaleToID. This doesn't seem to be correct though.

Should this look up be created as a relationship or as a link in the form? I only want to look the values up and store the selected ID in the Post table.

I have another query too but I'll do a seperate entry for that.

Many many thanks for any help

Jenny

 

[boblarson]

Your relationships sound like they are set correctly. There's nothing else that should be a problem in storing a value from one table in both fields. Each combo box should be bound to the applicable field and store the ID in that applicable field.

 

[Pat Hartman]

When you need to join the same table multiple times to another table, you need to create multiple instances of the "lookup table" on the diagram as you have done. Access automatically suffixes each additional instance with a unique number -- _1, _2, etc. This technique is used both in the relationships window and in the query designer. It does not duplicate the lookup table.