G
Guest
can I set up a lookup box in a table to populate other fields within that
table?
Brook
table?
Brook
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.
G
Guest
Hi, Brook.
See above post to trashman. You don't WANT to store any field from another
table other than the foreign key in 99.99% of cases--the main exception is
time-based values that are expected to change, such as a UnitPrice in the
Product table. The current value of it would need to be stored in the Orders
table.
Display other fields on your form either by basing your form on a query, and
including the fields you'd like displayed, or through the Column property as
described.
Sprinks
See above post to trashman. You don't WANT to store any field from another
table other than the foreign key in 99.99% of cases--the main exception is
time-based values that are expected to change, such as a UnitPrice in the
Product table. The current value of it would need to be stored in the Orders
table.
Display other fields on your form either by basing your form on a query, and
including the fields you'd like displayed, or through the Column property as
described.
Sprinks
G
Guest
Why would I not want to store data from another table? It would give me
details on my product?
What I have is a table that houses my inventory, and I want to create a
lookup/dropdown for the Quality, and with the quality is chosen with is
Numeric (I.E. 50, 60, 80 etc), the quality Name and Quality Abbreviation will
populate into my table, is this not possible?
Brook
details on my product?
What I have is a table that houses my inventory, and I want to create a
lookup/dropdown for the Quality, and with the quality is chosen with is
Numeric (I.E. 50, 60, 80 etc), the quality Name and Quality Abbreviation will
populate into my table, is this not possible?
Brook
G
Guest
The reason is at the heart of what a relational database is, and points out
the distinction between what is stored in a table and what is displayed on a
form.
By all means, display as much helpful information on your form to assist
your users, including your QualityName and abbreviation. But to store
anything other than a foreign key (the primary key of another table) is
redundant and raises potential for error.
The QualityNumber completely and unambigously defines which record you
meant, and a query joined to the Quality table will retrieve the Name and
abbreviation correctly EVERY time.
Suppose, for example, in your Orders table, you stored a customer number and
customer name with every record. It's likely that, over time, your records
might look something like this (shown for a single customer):
Cust # Name Order #
etc.
-------------- ------------------------------------------ ----------
1234 Pratt & Whitney Canada A1
1234 Pratt/Whitney Canada A16
1234 Prat and Whitney CA A23
1234 Pratt and Whitney A34
These orders were all placed by the same customer, yet all have different
names, leading to errors in searching, totalling, etc. Define the name ONCE
in the Customer table, and you avoid this problem. Also, let's say Pratt is
purchase by Boeing and the new name is Boeing/Pratt. If the name is defined
once only in the Customer table, you change the name there, and ALL of the
associated Orders will have the correct new name associated with them.
So, to display the name and abbreviation on your form, either base your form
on a multi-table query that includes those fields, or use the column property
of the combo box (Its index starts from zero). If, for example, the Name is
the 2nd column in your combo box, you can display it in a textbox by setting
its ControlSource property to:
=YourComboBox.Column(1)
Hope that helps.
Sprinks
the distinction between what is stored in a table and what is displayed on a
form.
By all means, display as much helpful information on your form to assist
your users, including your QualityName and abbreviation. But to store
anything other than a foreign key (the primary key of another table) is
redundant and raises potential for error.
The QualityNumber completely and unambigously defines which record you
meant, and a query joined to the Quality table will retrieve the Name and
abbreviation correctly EVERY time.
Suppose, for example, in your Orders table, you stored a customer number and
customer name with every record. It's likely that, over time, your records
might look something like this (shown for a single customer):
Cust # Name Order #
etc.
-------------- ------------------------------------------ ----------
1234 Pratt & Whitney Canada A1
1234 Pratt/Whitney Canada A16
1234 Prat and Whitney CA A23
1234 Pratt and Whitney A34
These orders were all placed by the same customer, yet all have different
names, leading to errors in searching, totalling, etc. Define the name ONCE
in the Customer table, and you avoid this problem. Also, let's say Pratt is
purchase by Boeing and the new name is Boeing/Pratt. If the name is defined
once only in the Customer table, you change the name there, and ALL of the
associated Orders will have the correct new name associated with them.
So, to display the name and abbreviation on your form, either base your form
on a multi-table query that includes those fields, or use the column property
of the combo box (Its index starts from zero). If, for example, the Name is
the 2nd column in your combo box, you can display it in a textbox by setting
its ControlSource property to:
=YourComboBox.Column(1)
Hope that helps.
Sprinks