Linking to two of the same table - confusion over database relationships and forms (1 Viewer)

[whitespace]

Hello all,

I'm developing 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 design 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 lookup 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've purposely avoided the table lookup feature as recommended in various best practise guides)

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

Maybe I'm just having a blonde moment but many many thanks for any help, it's much appreciated.

Jenny

 

[DCrake]

Simple Software Solutions

If you have a member of staff on a specific grade of pay and within that grade there are several incerment points then what you need is to hold both values against the same grade.
Then in your employee table you need a field to hold the grade id and another field to hold the increment number.

Pay Scale table

Grade|Description|BOC|TOC
A |Administrator|10000|13000
B |Secretary |14000|17500
Etc.

You will also have an increments table that holds the grades of pay and the rate of pay for each icrement point. Such as:

A - 1 - 10000
A - 2 - 11000
A - 3 - 12000
A - 4 - 13000
B - 1 - 14000
B - 2 - 15000
etc.

The reason for having an increments table as it is easier to maintain should you change the number of pay points in the grade.

CodeMaster: