Question Help with Relationships? (Access 2007) No Unique Index Found

[tjjamal]

Hi,

I have created a datebase in access and have created 10 tables with relationships but I am having a problem creating TWO relationships with just one table.

I need help (yes I am a N00B at databases). In my datebase I have an academic staff table and I want to link it to the deparment table, and there are two fields that I wanted to link to (From the tblAcademicStaff I want to link Department and AcademicStaffID to the tblDepartment). When I link them it comes up with the dialouge 'No Unique index found for the referenced field of the primarytable'. .

I have attached a screenshot of what is going on.



Thank You, your help is greatly appreciated, especially at this time of the year.

TJ (My Christmas, spent with Access, hopefully get this done before then)

 

[The_Doc_Man]

In general, the "ONE" side of any relationship MUST have a unique key on it.

It is not clear from your description, so this is a "shot in the dark"

If you are linking a table to another table through two fields in that target table, only one of those fields should be unique. Has to do with normalization. (If it could be unique then you had two equally viable candidate keys. If either is autonumber, it is redundant.)

If we're still on the same path, then I think where you have a problem is that there must be a non-duplicate key on the "one" side of the relationship based on the two fields that are being used to define the relationship. If not, you cannot build that relationship.

If this is NOT what you meant or if this seems unresponsive, it is because I could not understand your question somehow. In which case, please clarify.

 

[johncallaghan]

What is your key field in 'Departments'. Have you got a DepartmentID for instance. If this is the key filed you will have no problem creatinf two relationships.

Cheers
John

 

[tjjamal]

Hi,

Thanks for the reply.

I just want to create a relationship between the two fields. I want the database in such a way that I manually enter a unique key in tblDepartment (DepartmentID -field) and from there it automatically gets linked with tblAcademicStaff (DepartmentID field). Basically it can be selected as a department from the other table when entering data.

Should I upload my database for further inspect, I can also provide the specification for this datebase, if anyone is interested in helping.


Thank You

TJ

 

[tjjamal]

What is your key field in 'Departments'. Have you got a DepartmentID for instance. If this is the key filed you will have no problem creatinf two relationships.

Cheers
John

Hi John,

That is exactly the relationship I want. I have attached the spec for the database and the database, I am open to all constructive comments.


Thank You

TJ

 

[tjjamal]

any ideas guys?

 

[The_Doc_Man]

Again, we aren't communicating fully here, but it sounds like you might benefit from reading up on something called "Cascading Combo Boxes" - which you can do by searching this forum for that exact topic. Set your age filter to at least a year and maybe 2 years would be better.