Relationships and lookup tables.

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.
--
B. Bamford


"Damon Heron" wrote:

I don't see a thread for CC, but I think I can answer your question.
If you have no data in the tblEmployeeInfo, then why would you expect to see
a relation to the employeeID?
You noted that when you enter salary in the tblEmpInfo, then the EmpID shows
up. So there is your relation.
Next, are you entering data from a form or directly to the table? If a form
then you probably should have the mainform's record source as the
tblEmployee, and add a subform with the source as tblEmployeeInfo. The link
between the two is the EmpID.

Finally, when you look at tables and related tables using the little plus
sign, it doesn't show the EmpID, because the record you click on is
associated with the EmpID. If the Info table is blank for that record, it
still shows the blank record.

Damon

"Ben Bamford" wrote in message
...
As a novice developer (and to this use of "communities"), I am hoping to
gain
guidance on the subject of this thread. I think I've understood "CC"'s
question because it seems the same as mine and my experimentation with the
answers has not produced a correct result.

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?

Opening "tblEmployee" and expanding to see the subdatasheet of
"tblEmployeeInfo", I see only the "Salary" field, not the "EmpID" field.
If
I enter a salary value in the subdatasheet, I do see the "EmpID" values in
tblEmployeeInfo".

I just do not get why I do not automatically see values in both tables
when
I enter them in the Parent table. I will continue to seek other resources
to
answer this, but this seems SO BASIC and I'm having such a hard time with
it.

--
B. Bamford


"CC" wrote:

OK, I thought I had this straight after going through all the online
tutorials, but now I'm actually implementing, and I'm confused about
relationships and lookup fields. I'm doing an Orders database. I have:

TblCustomers
TblOrders
TblOrderDetails

OK, so these are all related. So in the Orders Table, there is a column
for
Customers, to show which customer made that order, right? So I created a
CustomerID column in the Orders Table and one to many relationship to the
Customers Table for that. But I'm NOT supposed to set the properties of
that
column to Lookup the Customers table? I'm supposed to let the data for
that
field be entered by a Form?

I guess the confusion is that I don't feel like the tables are really
linked
unless I see that drop-down list in the table. But is that the wrong way
to
think of it? I went through the MS online tutorials, and all their sample
databases are full of lookup fields. Is that wrong?

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 Bamford" wrote in message
...
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.
--
B. Bamford


"Damon Heron" wrote:

I don't see a thread for CC, but I think I can answer your question.
If you have no data in the tblEmployeeInfo, then why would you expect to
see
a relation to the employeeID?
You noted that when you enter salary in the tblEmpInfo, then the EmpID
shows
up. So there is your relation.
Next, are you entering data from a form or directly to the table? If a
form
then you probably should have the mainform's record source as the
tblEmployee, and add a subform with the source as tblEmployeeInfo. The
link
between the two is the EmpID.

Finally, when you look at tables and related tables using the little plus
sign, it doesn't show the EmpID, because the record you click on is
associated with the EmpID. If the Info table is blank for that record,
it
still shows the blank record.

Damon

"Ben Bamford" wrote in message
...
As a novice developer (and to this use of "communities"), I am hoping
to
gain
guidance on the subject of this thread. I think I've understood "CC"'s
question because it seems the same as mine and my experimentation with
the
answers has not produced a correct result.

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?

Opening "tblEmployee" and expanding to see the subdatasheet of
"tblEmployeeInfo", I see only the "Salary" field, not the "EmpID"
field.
If
I enter a salary value in the subdatasheet, I do see the "EmpID" values
in
tblEmployeeInfo".

I just do not get why I do not automatically see values in both tables
when
I enter them in the Parent table. I will continue to seek other
resources
to
answer this, but this seems SO BASIC and I'm having such a hard time
with
it.

--
B. Bamford


"CC" wrote:

OK, I thought I had this straight after going through all the online
tutorials, but now I'm actually implementing, and I'm confused about
relationships and lookup fields. I'm doing an Orders database. I have:

TblCustomers
TblOrders
TblOrderDetails

OK, so these are all related. So in the Orders Table, there is a
column
for
Customers, to show which customer made that order, right? So I created
a
CustomerID column in the Orders Table and one to many relationship to
the
Customers Table for that. But I'm NOT supposed to set the properties
of
that
column to Lookup the Customers table? I'm supposed to let the data for
that
field be entered by a Form?

I guess the confusion is that I don't feel like the tables are really
linked
unless I see that drop-down list in the table. But is that the wrong
way
to
think of it? I went through the MS online tutorials, and all their
sample
databases are full of lookup fields. Is that wrong?

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.

"Damon Heron" wrote:

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 Bamford" wrote in message
...
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.
--
B. Bamford


"Damon Heron" wrote:

I don't see a thread for CC, but I think I can answer your question.
If you have no data in the tblEmployeeInfo, then why would you expect to
see
a relation to the employeeID?
You noted that when you enter salary in the tblEmpInfo, then the EmpID
shows
up. So there is your relation.
Next, are you entering data from a form or directly to the table? If a
form
then you probably should have the mainform's record source as the
tblEmployee, and add a subform with the source as tblEmployeeInfo. The
link
between the two is the EmpID.

Finally, when you look at tables and related tables using the little plus
sign, it doesn't show the EmpID, because the record you click on is
associated with the EmpID. If the Info table is blank for that record,
it
still shows the blank record.

Damon

"Ben Bamford" wrote in message
...
As a novice developer (and to this use of "communities"), I am hoping
to
gain
guidance on the subject of this thread. I think I've understood "CC"'s
question because it seems the same as mine and my experimentation with
the
answers has not produced a correct result.

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?

Opening "tblEmployee" and expanding to see the subdatasheet of
"tblEmployeeInfo", I see only the "Salary" field, not the "EmpID"
field.
If
I enter a salary value in the subdatasheet, I do see the "EmpID" values
in
tblEmployeeInfo".

I just do not get why I do not automatically see values in both tables
when
I enter them in the Parent table. I will continue to seek other
resources
to
answer this, but this seems SO BASIC and I'm having such a hard time
with
it.

--
B. Bamford


"CC" wrote:

OK, I thought I had this straight after going through all the online
tutorials, but now I'm actually implementing, and I'm confused about
relationships and lookup fields. I'm doing an Orders database. I have:

TblCustomers
TblOrders
TblOrderDetails

OK, so these are all related. So in the Orders Table, there is a
column
for
Customers, to show which customer made that order, right? So I created
a
CustomerID column in the Orders Table and one to many relationship to
the
Customers Table for that. But I'm NOT supposed to set the properties
of
that
column to Lookup the Customers table? I'm supposed to let the data for
that
field be entered by a Form?

I guess the confusion is that I don't feel like the tables are really
linked
unless I see that drop-down list in the table. But is that the wrong
way
to
think of it? I went through the MS online tutorials, and all their
sample
databases are full of lookup fields. Is that wrong?

On Thu, 3 Jul 2008 13:57:00 -0700, Ben Bamford
wrote:

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.
--

John W. Vinson [MVP]