Its all down to what's known as 'normalization', which is a formal process
for eliminating redundancy in tables. Redundancy is a bad thing because it
leaves the database wide open to inconsistent data being entered.
For an example of the lack of proper normalization take a look at the
Customers table in the sample Northwind database which comes with Access.
You'll see that this has City, Region and Country columns so we are told
numerous times that São Paulo is in SP region (as is Resende) and that SP
region is in Brazil. Not only does this require repetitive data entry, but
more importantly it opens up the risk of inconsistencies, e.g. it would be
perfectly possible to put São Paulo in California in one row and California
in Ireland! Proper normalization as I described above would prevent this as
the fact that São Paulo is in SP region would be stored only once in the
database as would the fact that SP region is in Brazil and California is in
the USA.
Normalization is defined by a series of 'normal forms', from 1 to 5 (with an
extra one inserted in the middle when it was found that one of the originals
was deficient in some special circumstances). For most purposes
normalization to Third Normal Form (3NF) at least should be undertaken. The
basis of normalization is what is known as 'functional dependency' and a
table is in 3NF if every non-key column is functionally dependent solely on
the whole of the table's primary key – "the key, the whole key and nothing
but the key, so help me Codd". Edgar F Codd was the inventor of the database
relational model.
A column is functionally dependent on another column, or combination of
columns (keys can be composite) if for any value of the key column(s) the
value of the non-key column wherever it appears is always the same. So if my
EmployeeID is 42 in a table Employees, wherever EmployeeID is 42 then
FirstName is 'Ken' and LastName is 'Sheridan'. But say a table ProjectStaff
is created and I work on Project 1 and Project 2, then rows in the table
could be:
Project 1 42 Ken Sheridan
Project 2 42 Ken Sheridan
Project 1 99 Carol Hicks
However, there is nothing to stop the following being entered:
Project 1 42 Ken Sheridan
Project 2 42 Keith Sheridan
Project 1 99 Carol Hicks
In fact I did find something very similar to this in one database where I
was recorded as the author of technical articles; in one row I was K W
Sheridan (correctly), in another K V Sheridan (a clear typo).
So we have inconsistent data in the ProjectStaff table. Now the key of that
table is in fact a composite one of Project and EmployeeID, so Firstname and
LastName must be functionally dependent on both columns. In fact they are
also functionally dependent on EmployeeID alone, not the whole of the key, so
the table is not in 3NF, which is why inconsistent data is possible. All
that’s needed in the table is:
Project 1 42
Project 2 42
Project 1 99
This can be joined to the Employees table in a query on the EmployeeID
columns to pull the names into the query's result table (not a real 'base'
table, but a virtual one). The result set of this table would in fact be
exactly the same as the first non-normalized example table above. In
Employees FirstName and LastName are functionally dependent on EmployeeID so
that table too is in 3NF.
The situation in your case is the same. By having only the PNID in the
tbleNCR table the table is in 3NF and protected from redundancy and therefore
from inconsistencies. You just join it to tblPartNumbers in a query to pull
in the part number and description in the result table.
A foreign key column is not defined in a table in the same way as a primary
key is. You just add a PNID column to the tbleNCR table, make sure it’s the
same data type as the PNID primary key column in tblPartNumbers (if that's an
autonumber, don't use an autonumber in tbleNCR, but a straightforward long
integer number data type) and index it non-uniquely (duplicates allowed).
When a non-normalized table is normalized in this way by being split into two
normalized table we talk of it being 'decomposed'. In the technical
literature you'll sometimes see references to 'non-loss decomposition', which
means the table is split without the two tables in combination losing any of
the information content of the original one table.
You can then create a relationship between tblPartNumbers and tbleNCR. When
creating the relationship enforce referential integrity (this prevents
invalid PNID values being entered in tbleNCR). If the PNID column in
tblPartNumbers is not an autonumber also enforce 'cascade updates' when
creating the relationship. This maens that if a PNID in tblPartNumbers
should be changed then any matches in tbleNCR will also change.
For entering data into tbleNCR you can use a combo box on a form bound to
the table in the way I described.
Ken Sheridan
Stafford, England
Stimpy707 said:
Sorry Ken, but I'm already a little confused. Why wouldn't the tbleNCR have
a Part Number or Description column? And how do I go about setting PNID as a
"foreign" key? I checked out foreign key in the Access Help and it didn't
seem to explain how you go about setting it up.
Ken Sheridan said:
Firstly your tbleNCR table should have a PNID foreign key column, not a Part
Number or Description column. The combo box would be set up as follows:
Name: cboPNID
ControlSource: PNID
RowSource: SELECT [PNID], [Description], [PartNumber] FROM
[tblPartNumbers] ORDER BY [PartNumber];
BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
two dimensions are zero to hide the first two columns and that the third is
at least as wide as the combo box.
Ad an unbound text box to the form and set its ControlSource property to:
=[cboPNID].[Column](1)
This will show the description for the selected part number. the column
property is zero-based so Column(1) is the hidden second column, Description.
If you are creating a Non-Conformance Report don't use a combo box as you
would in a form, but base the report on a query which joins the
tblPartNumbers and tbleNCR tables on the PNID columns. You can then have
text box controls in the report bound to the Part Number and Description
columns from tblPartNumbers.
Ken Sheridan
Stafford, England
:
I've read through some posts related to what I am trying to accomplish but I
am still missing something or I am just and e-tard. Please help if you can.
Here it is...
I have a table titled "tblPartNumbers." It has three fields. The first
column is titled "PNID" which is the Primary Key. The second column is
labeled "Part Number", and the third column is labeled "Description." See
example below.
PNID Part Number Description
1 20015 Chamber, Rocket Body
2 20096 Igniter, Solid Fuel
3 31347 Sheild, Thermo Vein
etc... etc... etc...
Whenever a defective part is made we need to document the details in a
Non-Conformance Report. That report will contain info such as Customer, PO
#, Lot #, Date of Manufacture, and so on including the above mentioned Part
Number and Description info. I've created a table called "tbleNCR" and
subsequently a form from that table called "frmNCR."
When filling out a new NCR report (via the Form) I want to be able to select
the Part Number via drop-down list or by typing it in. I would then like the
Description to be filled in automatically. This is where I am stuck.
In my form, I have only been successful in having the Description field
auto-update with the Primary Key number for the respective part number, as
shown below.
Part Number Description
31347 3
What do I need to do to be able to type in or select "31347" from a combo
box and have the description automatically come up with "Sheild, Thermo Vein"
as shown at the top of this message?
Access usually