Relationships and lookup tables.

[Ben Bamford]

Damon,

Thanks for the response. The problem may lie in my assumption that if you
use id fields (such as "EmpID" in both parent and child tables), then when
you enter a value in "EmpID" in the parent table, you should expect to see
that same value in the "EmpID" field in the related table. That is not the
case and it seems as though it should be.

When I create a form based on "tblEmployee" with a subform based on
"tblEmployeeInfo", linked on the common "EmpID" field, then this does occur.
When I navigate to each record in the main form, I see AT MINIMUM the
corresponding "EmpID" value in the subform, even if I have not filled in
other field values for that record.

The corresponding "EmpID" values do not appear for each record when both
tables are opened and examined. This is the source of my frustration.

The "EmpID" field is a "text" datatype.

If I skip a couple of records when I open "tblEmployee" and expand to view
the subdatasheet of record 5 and enter a value for the "Salary" field (in
"tblEmployeeInfo"), close both tables, then reopen both, this is what I see:

I see that in "tblEmployeeInfo", three records with values for the "Salary"
field. There are "EmpID" values of 0001, 0002 and 0005 in that table, ie
there are no "placeholder values" of 0003 and 0004 showing (but those are
present in the "tblEmployees"). It seems to me that, logically, there should
be the same set of "EmpID" values in both tables, regardless of whether or
not additoinal fields for those IDs are completed.

What's the problem with my logic? Thanks.

 

[Damon Heron]

Okay, lets take a step back. Relationships between tables can be one to
one, one to many, or many to many. Most common are one to many. That
means, for instance, that a customer table (the one side) could be linked to
an Orders table (the many side) -- one customer may have many orders.
In your example, you have employee table linked to employee info, which is
salary. Can an employee have more than one salary?
If not, then why is it in a separate table?
Here is another way to design the tables:

EmployeeTable
EmpID
EmpName
Address
Phone
JobID (foreign key to JobTable)
SalaryID (foreign key to SalaryTable)
etc.

JobTable
JobID (Primary key)
JobTitle
Description
etc....

SalaryTable
SalaryID (Primary key)
SalaryAmt

In this case, if you have 10 employees, with differing jobs, you can assign
SalaryIDs to each. Now, suppose there is a pay raise. With your original
system, you would have to go thru each employee and adjust their salary
individually. With this format, you only have to open the salary table and
change the amount once. Similarly, if any employee switches jobs in the
company, you just need to change his jobID.

Perhaps you could benefit from looking at some examples of db models. Check
out this website:
http://www.databaseanswers.org/data_models/index.htm

Oh, and the reason the empID is showing on your subform is because you are
working on the main record. If you don't entry the subform and add a
salary, then there is nothing to save, hence it is blank.

Damon

 

[Ben]

I understand your point, however, there might be occasions when such 1:1
relationships occur for privacy reasons (splitting "private" information into
a separate table) and providing a link to the "public" table. In any event,
whether 1:1 or 1:M, my key frustration seems to be that I do not see ALL of
the same "EmpID" values in both "tblEmployees" and "tblEmployeeInfo" (whether
or not fields other than "EmpID" have any values entered -- in the
"tblEmployeeInfo").

Maybe it's this idea I have that there should at least be a "placeholder" in
evidence in the subordinate table to ensure that one can visually verify the
match between EmpID values in the tblEmployees and EmpID values in the
tblEmpInformation.

As to your comment about the main form and subform, I get exactly what I
expect when I navigate through the records in the main form (I see ALL EmpID
values in both tables, even if additional field values have not been entered
into the subform or subordinate table).

So, I am still left with my issue, but I do thank you for the resource link.

 

[John W. Vinson]

I have a Parent Table "Employee" with PK "EmpID" and 3 other fields in that
table.
I have a Related Table "EmployeeInfo" with PK "EmpID" and 1 other field
("Salary")in that table.
I related "tblEmployee" to "tblEmployeeInfo", enforcing referential
integrety, and cascade update and cascade delete.
I entered 4 employee records in "tbl Employee" (all fields).
The "EmpID" values in "tblEmployee" DO NOT appear in the "EmpID" field of
"tblEmployeeInfo". Why not?

Because creating a relationship does not (and emphatically SHOULD not!)
automagically create a new record in the child table.

A relationship *prevents* the addition of a record with a nonexistant EmpID,
should you try to create such a record. It does not cause a new record to be
created. A one to one relationship is jargon shorthand for a

One to (zero or one)

relationship; it means that if there is a record in tblEmployeeInfo then it
must use a value of EmpID which exists in tblEmployee, but there might not be
any records in the table for that ID, or for that matter, there might not be
any records at all.