Relationship error

[usman666]

I have two tables Employees and Salaries



Employees:

Eid PK
Name
Mobile
address
city


Salaries

Eid pk
Name
Mobile
Pay


when i relate Eid with Eid it works fine but i want to relate Name and Mobile fields too but when i do that i get "no unique index found for the referenced field of the primary table" how to solved that

[RuralGuy]

Those last two fields should *only* be in one of the tables, not both.

[jzwp11]

What is the relationship between the employee and their pay? If your intent is to only store the employee's current salary then you do not need the salaries table. If your intent is to track employees' salaries over time then you would need the salary table.

As a rule, you would not repeat data in multiple tables, so the mobile (phone # I assume) and the name should only be stored in 1 table. You would only need to link using the key fields (primary key of one table to its respective foreign key field in the related table)

If you were tracking salary history for each employee that table structure would look like this:

tblEmployee
-pkEid primary key, autonumber
-txtName
-txtMobile
-txtaddress
-txtcity

tblEmployeeSalaries
-pkEmpSalaryID primary key, autonumber
-pkEid foreign key to tblEmployees (must be a long number datatype)
-currPayRate
-dteEffective (effective date of the pay rate)

(txt=text field, dte= date/time field, curr=currency field, pk/fk=primary/foreign key fields autonumber for the primary, long number integer for the foreign)

By the way, the word "name" is a reserved word in Access, so it should not be used as a table or field name. Here is a list of the reserved words. Also, it is not recommended to have spaces or special characters in your table or field names

[usman666]

so when i`ll link that it wud auto add the name and mobile number of employee to salaries right?

[jzwp11]

No.

When you do your data entry forms, you would have a main form based on the employee table with a subform based on the salary table. If you have your relationships established in the Relationship Window, Access will automatically link the main and sub forms via the key fields.


If you want to see the data outside of a form you would create a query that includes both tables and select the fields you want to see (for either or both tables) and then run the query.