P
Peggy
Is there a way to make a lookup table populate TWO columns, without having to
have a lookup table in the second column?
have a lookup table in the second column?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Sheridan
I think you are misunderstanding how a so-called 'look-up' table operates in
a relational database. The column in the 'referencing' table is not
'populated' by a non-key column in the 'look-up' table. What is actually
stored in the 'referencing' table is a foreign key value which references the
primary key of the 'look-up' table. A better term for the 'look-up' table
is, I'd suggest, the more technically correct 'referenced' table.
Take a simple example of a table Orders with a foreign key column CustomerID
which references the primary key column, also CustomerID, of a table
Customers. The value stored will most likely be am arbitrary number which
uniquely identifies each customer. This number has no meaning per se,
however, so you'd normally not want to see it, but 'look up' other columns
from Customers like FirstName, LastName etc. The way to do this, in the
RecordsSource for a report for instance, is by means of a query which joins
the tables on the CustomerID columns, e.g.
SELECT FirstName, LastName, OrderDate, OrderAmount
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
By referencing the Customers table solely on the one foreign key column in
the Orders table the integrity of the data is protected as it can only ever
reference the same values of FirstName, LastName etc in Customers. If these
values were duplicated in two columns in Orders there would be nothing to
stop a customer having different first of last names in separate rows in
Orders.
For data entry purposes via an Orders form a combo box bound to the
CustomerID column would normally be used. The values listed need to be such
that they can identify the customer however. You might just list the last
name values, but you could also list both first and last names by having two
visible columns in the combo box's list. Only one of these would be visible
in the control after a selection is made so a better approach might be to
concatenate them in the combo box's list as single column, so that when a
selection is made both will show in the control. This is done in the
RowSource property of the combo box like so:
SELECT CustomerID, FirstName & LastName AS FullName
FROM Customers
ORDR BY LastName, FirestName;
While you'd see the names in the format Ken Sheridan in the combo box, what
would be stored in the Orders table would be the value of the CustomerID
corresponding to that customer.
You could set up the CustomerID column in the Orders table in design view to
operate in the same way when you open the table in datasheet view, but this
is not recommended. Data entry should always be via forms, not in raw
datasheet view. Its better to show the actual value of the foreign key in
the latter, avoiding the use of a combo box as the display control, and even
more so avoiding the use of the 'look-up' wizard in table design.
Ken Sheridan
Stafford, England
a relational database. The column in the 'referencing' table is not
'populated' by a non-key column in the 'look-up' table. What is actually
stored in the 'referencing' table is a foreign key value which references the
primary key of the 'look-up' table. A better term for the 'look-up' table
is, I'd suggest, the more technically correct 'referenced' table.
Take a simple example of a table Orders with a foreign key column CustomerID
which references the primary key column, also CustomerID, of a table
Customers. The value stored will most likely be am arbitrary number which
uniquely identifies each customer. This number has no meaning per se,
however, so you'd normally not want to see it, but 'look up' other columns
from Customers like FirstName, LastName etc. The way to do this, in the
RecordsSource for a report for instance, is by means of a query which joins
the tables on the CustomerID columns, e.g.
SELECT FirstName, LastName, OrderDate, OrderAmount
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
By referencing the Customers table solely on the one foreign key column in
the Orders table the integrity of the data is protected as it can only ever
reference the same values of FirstName, LastName etc in Customers. If these
values were duplicated in two columns in Orders there would be nothing to
stop a customer having different first of last names in separate rows in
Orders.
For data entry purposes via an Orders form a combo box bound to the
CustomerID column would normally be used. The values listed need to be such
that they can identify the customer however. You might just list the last
name values, but you could also list both first and last names by having two
visible columns in the combo box's list. Only one of these would be visible
in the control after a selection is made so a better approach might be to
concatenate them in the combo box's list as single column, so that when a
selection is made both will show in the control. This is done in the
RowSource property of the combo box like so:
SELECT CustomerID, FirstName & LastName AS FullName
FROM Customers
ORDR BY LastName, FirestName;
While you'd see the names in the format Ken Sheridan in the combo box, what
would be stored in the Orders table would be the value of the CustomerID
corresponding to that customer.
You could set up the CustomerID column in the Orders table in design view to
operate in the same way when you open the table in datasheet view, but this
is not recommended. Data entry should always be via forms, not in raw
datasheet view. Its better to show the actual value of the foreign key in
the latter, avoiding the use of a combo box as the display control, and even
more so avoiding the use of the 'look-up' wizard in table design.
Ken Sheridan
Stafford, England