I am stupid- Access Relationships and such

[rsmccli]

Most of the time my job has been working with a fairly large existing AC2002 database: making minor changes, automating functions, adding command buttons or text boxes here and there... Now I have to actually build a small database for recording personnel and their company affiliations; it also has to allow for remote additions from satellite companies. I have noticed recently that I am not good at making tables and setting up their relationships etc. I understand that this is a fairly simple process, but for some reason I am still having trouble.

I posted here before to ask the best setup for the main table and have decided:

tblEmployers
CompanyID (PK)
Employer

tblEmployeeInfo
SSN (PK)
FirstName
LastName
DOB

tblEmployeeHybrid
SSN (PK1)
FirstName
LastName
DOB
Company(PK2)
(a ton more fields with additional information)


Okay, so when these relationships are set up, each SSN can be in the tblEmployeeHybrid several times, but each entry must have a different Company affiliation, and any given company cannot have dupicate SSN entries.

Now I just want to know what type of relationship should each field get. Do you just put relationships for the Primary Key fields? In the hybrid table, if you typed in an SSN that already exists in tblEmployees, will the FirstName, LastName, etc. fields automatically update, or is that something you have to do in a form? If this is true, do you base the form on a query with fields from the two smaller tables or just the "hybrid" table. Thank you for your time, sorry about being dense.

[FishVal]

Hi, there.

What you are trying to do is called many-to-many relationship. This type of relationship between two tables is implemented by addition of third table which contains foreign keys of these two tables. I somewhat confused about what do you mean with PK1 and PK2. I hope that is not composite primary key because this should be two foreign keys.

To ensure your table contains only unique combinations of [SSN] and [Employer] set multifield index restricting duplicate values. You may learn more about indexes reading Indexes in Microsoft Access article.

Best regards,

Fish