multiple key fields in parent table and combo boxes for foreign ke

A

AccessMan

I have the following tables and keys and am seeking a way to easily populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to
separately display the values available in the parent table fields to which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way. If I set
Bound Column and Column Count to 2 I see both columns in the combo box, and I
can seemingly select something that populates the field without violating
referential integrity, but a PK1 value is displayed and not a PK2 value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values, and I set
the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I
should have to do this, so I suspect that I am missing something quite
obvious.
 
J

Jeff Boyce

I'm not sure I'm following your description (but maybe I'm being too literal
<g>)...

A table would only have one PK, although you could create a multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without a PK!
A PK is supposed to uniquely identify a row, so there would NEVER be any
duplicates.

If you are describing the use of Lookup data types in your tables, please
reconsider. This approach, while well-intentioned, can cause considerable
confusion for those who try to understand it later (including yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a few
'rows' of data as an example, so we can get some idea what domain you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

AccessMan

Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an autonumber
field as a key. Instead, I select fields that naturally serve to uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for first name
and a second for last name. Duplicates should certainly be allowed for each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and last
names).

As an example, assume I want a table that documents a person's job history
([Person Job History]). This table would have the same primary keys as the
[Person] table above (separate fields for first name and last name), but
would also have a third primary key field, probably for date of employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be able to
use a combo box that pulls down available values from the [Person] table for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person Job
History], specifically, the second column position of the LastName field in
the [Person] table. The combo box for the FirstName field uses the first
column of [Person] with no problem. If I set the BoundColumn and COlumnCount
to 2 for the combo box for the LastName field I can successfully populate
[Person Job History], but the values that appear in this field are first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the last
name in the first column, and to set this query as the Row Source for the
combo box for LastName in [Person Job History]. This seems silly to have to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

Jeff Boyce said:
I'm not sure I'm following your description (but maybe I'm being too literal
<g>)...

A table would only have one PK, although you could create a multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without a PK!
A PK is supposed to uniquely identify a row, so there would NEVER be any
duplicates.

If you are describing the use of Lookup data types in your tables, please
reconsider. This approach, while well-intentioned, can cause considerable
confusion for those who try to understand it later (including yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a few
'rows' of data as an example, so we can get some idea what domain you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes
to
separately display the values available in the parent table fields to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way. If I
set
Bound Column and Column Count to 2 I see both columns in the combo box,
and I
can seemingly select something that populates the field without violating
referential integrity, but a PK1 value is displayed and not a PK2 value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values, and I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem right
that I
should have to do this, so I suspect that I am missing something quite
obvious.
 
J

Jeff Boyce

The natural vs. arbitrary key discussion can devolve into religious wars, so
let's not go there!

I'm curious why you would want to select LastName and FirstName separately
for your Job History. Aren't you actually collecting information about the
Person?

What about using a query to concatenate these two and letting your users
pick the correct person? You could still load those two separate values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks having
the same name (and even living in the same house). You might be operating
in a VERY limited domain in which this could never happen ... GOOD LUCK!

If you ever do have two people with the same name, are you going to expand
the number of fields you use to uniquely identify an individual? (this is
one of the situations in which an "unnatural" key may have fewer problems in
"behind the curtain" implementation -- of course, your users would never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for first
name
and a second for last name. Duplicates should certainly be allowed for
each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and last
names).

As an example, assume I want a table that documents a person's job history
([Person Job History]). This table would have the same primary keys as
the
[Person] table above (separate fields for first name and last name), but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be able
to
use a combo box that pulls down available values from the [Person] table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person Job
History], specifically, the second column position of the LastName field
in
the [Person] table. The combo box for the FirstName field uses the first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully populate
[Person Job History], but the values that appear in this field are first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the last
name in the first column, and to set this query as the Row Source for the
combo box for LastName in [Person Job History]. This seems silly to have
to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

Jeff Boyce said:
I'm not sure I'm following your description (but maybe I'm being too
literal
<g>)...

A table would only have one PK, although you could create a multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER be any
duplicates.

If you are describing the use of Lookup data types in your tables, please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a few
'rows' of data as an example, so we can get some idea what domain you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo
boxes
to
separately display the values available in the parent table fields to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1 as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way. If I
set
Bound Column and Column Count to 2 I see both columns in the combo box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values, and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem right
that I
should have to do this, so I suspect that I am missing something quite
obvious.
 
A

AccessMan

Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the combo
box functionality. My actual tables truly need the multiple key fields, but
they would have only presented unnecessary confusion if I had described them
instead.

You suggest using a query to concatenate the first and last names for
selection purposes, with VBA functionality behind it I assume to place the
separate values in the table fields. This is a nice idea that would halve
the effort on the user's part in doing data entry for these fields. This is
not the problem that I thought I was trying to solve, but you have changed my
way of looking at this issue and I may very well give it a try.

However, I still hope there is a cleaner way of handling combo box lookups
The natural vs. arbitrary key discussion can devolve into religious wars, so
let's not go there!

I'm curious why you would want to select LastName and FirstName separately
for your Job History. Aren't you actually collecting information about the
Person?

What about using a query to concatenate these two and letting your users
pick the correct person? You could still load those two separate values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks having
the same name (and even living in the same house). You might be operating
in a VERY limited domain in which this could never happen ... GOOD LUCK!

If you ever do have two people with the same name, are you going to expand
the number of fields you use to uniquely identify an individual? (this is
one of the situations in which an "unnatural" key may have fewer problems in
"behind the curtain" implementation -- of course, your users would never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for first
name
and a second for last name. Duplicates should certainly be allowed for
each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and last
names).

As an example, assume I want a table that documents a person's job history
([Person Job History]). This table would have the same primary keys as
the
[Person] table above (separate fields for first name and last name), but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be able
to
use a combo box that pulls down available values from the [Person] table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person Job
History], specifically, the second column position of the LastName field
in
the [Person] table. The combo box for the FirstName field uses the first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully populate
[Person Job History], but the values that appear in this field are first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the last
name in the first column, and to set this query as the Row Source for the
combo box for LastName in [Person Job History]. This seems silly to have
to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

Jeff Boyce said:
I'm not sure I'm following your description (but maybe I'm being too
literal
<g>)...

A table would only have one PK, although you could create a multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER be any
duplicates.

If you are describing the use of Lookup data types in your tables, please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a few
'rows' of data as an example, so we can get some idea what domain you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo
boxes
to
separately display the values available in the parent table fields to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1 as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way. If I
set
Bound Column and Column Count to 2 I see both columns in the combo box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values, and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem right
that I
should have to do this, so I suspect that I am missing something quite
obvious.
 
J

Jeff Boyce

If you are describing an "in-the-table" use of lookup datatypes, please stop
NOW! What you described (using other than ...) is one of the issues that
crops up trying to do data entry/validation/lookup via tables instead of
forms, where it belongs.

In a form, a combobox can bind any column you choose, and can display any
column(s) you choose. Check the properties on the combobox control in the
form...

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the combo
box functionality. My actual tables truly need the multiple key fields,
but
they would have only presented unnecessary confusion if I had described
them
instead.

You suggest using a query to concatenate the first and last names for
selection purposes, with VBA functionality behind it I assume to place the
separate values in the table fields. This is a nice idea that would halve
the effort on the user's part in doing data entry for these fields. This
is
not the problem that I thought I was trying to solve, but you have changed
my
way of looking at this issue and I may very well give it a try.

However, I still hope there is a cleaner way of handling combo box lookups
The natural vs. arbitrary key discussion can devolve into religious wars,
so
let's not go there!

I'm curious why you would want to select LastName and FirstName
separately
for your Job History. Aren't you actually collecting information about
the
Person?

What about using a query to concatenate these two and letting your users
pick the correct person? You could still load those two separate values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks
having
the same name (and even living in the same house). You might be
operating
in a VERY limited domain in which this could never happen ... GOOD LUCK!

If you ever do have two people with the same name, are you going to
expand
the number of fields you use to uniquely identify an individual? (this
is
one of the situations in which an "unnatural" key may have fewer problems
in
"behind the curtain" implementation -- of course, your users would never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to
uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for
first
name
and a second for last name. Duplicates should certainly be allowed for
each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and
last
names).

As an example, assume I want a table that documents a person's job
history
([Person Job History]). This table would have the same primary keys as
the
[Person] table above (separate fields for first name and last name),
but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist
between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be
able
to
use a combo box that pulls down available values from the [Person]
table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person
Job
History], specifically, the second column position of the LastName
field
in
the [Person] table. The combo box for the FirstName field uses the
first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully
populate
[Person Job History], but the values that appear in this field are
first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the
last
name in the first column, and to set this query as the Row Source for
the
combo box for LastName in [Person Job History]. This seems silly to
have
to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

:

I'm not sure I'm following your description (but maybe I'm being too
literal
<g>)...

A table would only have one PK, although you could create a
multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without
a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER be
any
duplicates.

If you are describing the use of Lookup data types in your tables,
please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including
yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a
few
'rows' of data as an example, so we can get some idea what domain
you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed
with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2
are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo
boxes
to
separately display the values available in the parent table fields
to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1
as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way.
If I
set
Bound Column and Column Count to 2 I see both columns in the combo
box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values,
and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem
right
that I
should have to do this, so I suspect that I am missing something
quite
obvious.
 
A

AccessMan

Yes, you make a good point about form combo boxes.

Thanks!


Jeff Boyce said:
If you are describing an "in-the-table" use of lookup datatypes, please stop
NOW! What you described (using other than ...) is one of the issues that
crops up trying to do data entry/validation/lookup via tables instead of
forms, where it belongs.

In a form, a combobox can bind any column you choose, and can display any
column(s) you choose. Check the properties on the combobox control in the
form...

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the combo
box functionality. My actual tables truly need the multiple key fields,
but
they would have only presented unnecessary confusion if I had described
them
instead.

You suggest using a query to concatenate the first and last names for
selection purposes, with VBA functionality behind it I assume to place the
separate values in the table fields. This is a nice idea that would halve
the effort on the user's part in doing data entry for these fields. This
is
not the problem that I thought I was trying to solve, but you have changed
my
way of looking at this issue and I may very well give it a try.

However, I still hope there is a cleaner way of handling combo box lookups
The natural vs. arbitrary key discussion can devolve into religious wars,
so
let's not go there!

I'm curious why you would want to select LastName and FirstName
separately
for your Job History. Aren't you actually collecting information about
the
Person?

What about using a query to concatenate these two and letting your users
pick the correct person? You could still load those two separate values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks
having
the same name (and even living in the same house). You might be
operating
in a VERY limited domain in which this could never happen ... GOOD LUCK!

If you ever do have two people with the same name, are you going to
expand
the number of fields you use to uniquely identify an individual? (this
is
one of the situations in which an "unnatural" key may have fewer problems
in
"behind the curtain" implementation -- of course, your users would never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to
uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for
first
name
and a second for last name. Duplicates should certainly be allowed for
each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and
last
names).

As an example, assume I want a table that documents a person's job
history
([Person Job History]). This table would have the same primary keys as
the
[Person] table above (separate fields for first name and last name),
but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist
between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be
able
to
use a combo box that pulls down available values from the [Person]
table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person
Job
History], specifically, the second column position of the LastName
field
in
the [Person] table. The combo box for the FirstName field uses the
first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully
populate
[Person Job History], but the values that appear in this field are
first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the
last
name in the first column, and to set this query as the Row Source for
the
combo box for LastName in [Person Job History]. This seems silly to
have
to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

:

I'm not sure I'm following your description (but maybe I'm being too
literal
<g>)...

A table would only have one PK, although you could create a
multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without
a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER be
any
duplicates.

If you are describing the use of Lookup data types in your tables,
please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including
yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a
few
'rows' of data as an example, so we can get some idea what domain
you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed
with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2
are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo
boxes
to
separately display the values available in the parent table fields
to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1
as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way.
If I
set
Bound Column and Column Count to 2 I see both columns in the combo
box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values,
and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem
right
that I
should have to do this, so I suspect that I am missing something
quite
obvious.
 
A

AccessMan

Jeff:

It sounds like you are deadset against setting table fields as combo boxes
linked to fields in other tables. If that is the case, I'd appreciate
hearing more about why. I do this regularly, but not directly in support of
any functionality that a user would see. (I have users manipulate data
through forms as you indicate.) I just want to know if I am doing something
that might cause me problems. So far, all of my databases are well-behaved.

Thanks!

Jeff Boyce said:
If you are describing an "in-the-table" use of lookup datatypes, please stop
NOW! What you described (using other than ...) is one of the issues that
crops up trying to do data entry/validation/lookup via tables instead of
forms, where it belongs.

In a form, a combobox can bind any column you choose, and can display any
column(s) you choose. Check the properties on the combobox control in the
form...

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the combo
box functionality. My actual tables truly need the multiple key fields,
but
they would have only presented unnecessary confusion if I had described
them
instead.

You suggest using a query to concatenate the first and last names for
selection purposes, with VBA functionality behind it I assume to place the
separate values in the table fields. This is a nice idea that would halve
the effort on the user's part in doing data entry for these fields. This
is
not the problem that I thought I was trying to solve, but you have changed
my
way of looking at this issue and I may very well give it a try.

However, I still hope there is a cleaner way of handling combo box lookups
The natural vs. arbitrary key discussion can devolve into religious wars,
so
let's not go there!

I'm curious why you would want to select LastName and FirstName
separately
for your Job History. Aren't you actually collecting information about
the
Person?

What about using a query to concatenate these two and letting your users
pick the correct person? You could still load those two separate values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks
having
the same name (and even living in the same house). You might be
operating
in a VERY limited domain in which this could never happen ... GOOD LUCK!

If you ever do have two people with the same name, are you going to
expand
the number of fields you use to uniquely identify an individual? (this
is
one of the situations in which an "unnatural" key may have fewer problems
in
"behind the curtain" implementation -- of course, your users would never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to
uniquely
identify rows. Looking at it simplistically, a table that identifies a
person by name (table [Person]) would have two key fields, one for
first
name
and a second for last name. Duplicates should certainly be allowed for
each
of these key fields individually, but taken together they are obviously
unique (ignoring the fact that two people can have the same first and
last
names).

As an example, assume I want a table that documents a person's job
history
([Person Job History]). This table would have the same primary keys as
the
[Person] table above (separate fields for first name and last name),
but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist
between
the key fields of [Person] and the matching key fields in [Person Job
History]. In filling out this table with a form, I would want to be
able
to
use a combo box that pulls down available values from the [Person]
table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person
Job
History], specifically, the second column position of the LastName
field
in
the [Person] table. The combo box for the FirstName field uses the
first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully
populate
[Person Job History], but the values that appear in this field are
first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the
last
name in the first column, and to set this query as the Row Source for
the
combo box for LastName in [Person Job History]. This seems silly to
have
to
do. I'm wondering if there is a different way to solve this problem.

Thanks!

:

I'm not sure I'm following your description (but maybe I'm being too
literal
<g>)...

A table would only have one PK, although you could create a
multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off without
a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER be
any
duplicates.

If you are describing the use of Lookup data types in your tables,
please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including
yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have a
few
'rows' of data as an example, so we can get some idea what domain
you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have the following tables and keys and am seeking a way to easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed
with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2
are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these combo
boxes
to
separately display the values available in the parent table fields
to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table 1
as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way.
If I
set
Bound Column and Column Count to 2 I see both columns in the combo
box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2 values,
and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem
right
that I
should have to do this, so I suspect that I am missing something
quite
obvious.
 
J

Jeff Boyce

No, I'm not against the notion, just asking. If it's working for you, why
mess with it?!

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Jeff:

It sounds like you are deadset against setting table fields as combo boxes
linked to fields in other tables. If that is the case, I'd appreciate
hearing more about why. I do this regularly, but not directly in support
of
any functionality that a user would see. (I have users manipulate data
through forms as you indicate.) I just want to know if I am doing
something
that might cause me problems. So far, all of my databases are
well-behaved.

Thanks!

Jeff Boyce said:
If you are describing an "in-the-table" use of lookup datatypes, please
stop
NOW! What you described (using other than ...) is one of the issues that
crops up trying to do data entry/validation/lookup via tables instead of
forms, where it belongs.

In a form, a combobox can bind any column you choose, and can display any
column(s) you choose. Check the properties on the combobox control in
the
form...

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessMan said:
Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the
combo
box functionality. My actual tables truly need the multiple key
fields,
but
they would have only presented unnecessary confusion if I had described
them
instead.

You suggest using a query to concatenate the first and last names for
selection purposes, with VBA functionality behind it I assume to place
the
separate values in the table fields. This is a nice idea that would
halve
the effort on the user's part in doing data entry for these fields.
This
is
not the problem that I thought I was trying to solve, but you have
changed
my
way of looking at this issue and I may very well give it a try.

However, I still hope there is a cleaner way of handling combo box
lookups
on fields that are not in the first column of the parent table.



:

The natural vs. arbitrary key discussion can devolve into religious
wars,
so
let's not go there!

I'm curious why you would want to select LastName and FirstName
separately
for your Job History. Aren't you actually collecting information
about
the
Person?

What about using a query to concatenate these two and letting your
users
pick the correct person? You could still load those two separate
values
into the Job History table, but your users might find it easier.

Note: I have several (some quite painful) experiences with two folks
having
the same name (and even living in the same house). You might be
operating
in a VERY limited domain in which this could never happen ... GOOD
LUCK!

If you ever do have two people with the same name, are you going to
expand
the number of fields you use to uniquely identify an individual?
(this
is
one of the situations in which an "unnatural" key may have fewer
problems
in
"behind the curtain" implementation -- of course, your users would
never
need to know...).

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jeff:

Sorry, I don't think I conveyed my question well enough. Let me try
to
elaborate.

Firstly, I am of the "natural key field" school. I rarely use an
autonumber
field as a key. Instead, I select fields that naturally serve to
uniquely
identify rows. Looking at it simplistically, a table that
identifies a
person by name (table [Person]) would have two key fields, one for
first
name
and a second for last name. Duplicates should certainly be allowed
for
each
of these key fields individually, but taken together they are
obviously
unique (ignoring the fact that two people can have the same first
and
last
names).

As an example, assume I want a table that documents a person's job
history
([Person Job History]). This table would have the same primary keys
as
the
[Person] table above (separate fields for first name and last name),
but
would also have a third primary key field, probably for date of
employment.

[Person]
FirstName (PK)
LastName (PK)

[Person Job History]
FirstName (PK)
LastName (PK)
DateOfEmployment (PK)
Employer

A one-to-many relationship with referential integrity would exist
between
the key fields of [Person] and the matching key fields in [Person
Job
History]. In filling out this table with a form, I would want to be
able
to
use a combo box that pulls down available values from the [Person]
table
for
first name and last name, separately of course.

My problem lies with the combo box for the LastName field in [Person
Job
History], specifically, the second column position of the LastName
field
in
the [Person] table. The combo box for the FirstName field uses the
first
column of [Person] with no problem. If I set the BoundColumn and
COlumnCount
to 2 for the combo box for the LastName field I can successfully
populate
[Person Job History], but the values that appear in this field are
first
names and not last names. This is not at all desirable.

My workaround is to create a query on [Person] that simply lists the
last
name in the first column, and to set this query as the Row Source
for
the
combo box for LastName in [Person Job History]. This seems silly to
have
to
do. I'm wondering if there is a different way to solve this
problem.

Thanks!

:

I'm not sure I'm following your description (but maybe I'm being
too
literal
<g>)...

A table would only have one PK, although you could create a
multi-field
primary key.

A table with "duplicates OK" on its PK would be ... better off
without
a
PK!
A PK is supposed to uniquely identify a row, so there would NEVER
be
any
duplicates.

If you are describing the use of Lookup data types in your tables,
please
reconsider. This approach, while well-intentioned, can cause
considerable
confusion for those who try to understand it later (including
yourself!).

Perhaps I'm just getting lost with all the PK/F/... -- do you have
a
few
'rows' of data as an example, so we can get some idea what domain
you're
working in?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have the following tables and keys and am seeking a way to
easily
populate
Table 2 with values from Table 1.

Table 1: PK1, PK2, F1, F2, ...

Table 2: FK1, FK2, PK3, F1, F2, ...

PK1 and PK2 are primary key fields in Table 1. They are indexed
with
Duplicates OK.

FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2
are
foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and
inconsequential
non-key fields in each table.

[Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table
1].PK2
has
a 1:N relationship to [Table 2].FK2.

I set the Lookup Display Control to Combo Box for the foreign key
fields
[Table 2].FK1 and [Table 2].FK2, and I would like each these
combo
boxes
to
separately display the values available in the parent table
fields
to
which
they are linked.

This is not a problem for [Table 2].FK1 - I simply specify Table
1
as
the
Row Source and retain the default Bound Column value of 1.

However, I am unable to get [Table 2].FK2 to behave the same way.
If I
set
Bound Column and Column Count to 2 I see both columns in the
combo
box,
and I
can seemingly select something that populates the field without
violating
referential integrity, but a PK1 value is displayed and not a PK2
value.
This makes no sense to me.

My typical workaround is to create a query that yields PK2
values,
and
I
set
the Row Source for [Table 2].FK2 to this query. It doesn't seem
right
that I
should have to do this, so I suspect that I am missing something
quite
obvious.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top