Auto Fill

[Engineer Clement]

I have am mdb file and have two tables in it.
One called “Drugs” and the other called “Inventory”
In the “Drugs” table, I have two columns called:
“Drug ID” and “Drug Name”
In the “Inventory” table, I have four columns called:
“Drug Code”,” Drug Name”, “Manufactured Date” & “Expiration Date”.
The Drug Code and Drug Name in “inventory” table are looking up from “Drug ID” and “Drug Name” in "Drug” table. When I type or click the Drug Code in “Inventory” table, I want the Drug Name that was assign to that id in “Drug” table to automatically fill itself into the drug name in the inventory.
What should I do?

[Stewart Ross]

The short answer is that under no circumstances should you be including the drug name in the inventory table. To do so is to invite update anomalies; if at some stage the drug name in the inventory table differs from the drug name in the drug table, how would you know which one was correct?

If your table structures were correctly normalised you would understand that the drug name does not belong to the inventory table. In third-normal form the fields in a table must depend solely on the value of the primary key, and the value of the drug name clearly does not depend on the primary key value of the inventory table.

You may find our HowTo article on database normalisation and table structures helps with the concepts involved.

To get the drug name for an inventory report, say, you simply use a query to join the inventory table to the drug table on the drug ID field, then include the drug name from the drug table in the query alongside the inventory details.

-Stewart