I'll illustrate it with a simple example. A relational database represents
different entity types as tables, so you might have a table OrderDetails with
columns OrderID and ProductID , both numbers. Each of these are foreign keys
referencing the primary keys of two tables Orders and Products. The former
might have columns OrderID, CustomerID and OrderDate, the latter ProductID
and ProductName. CustomerID is also a foreign key referencing the primary
key of a Customers table. It’s the combination of primary and foreign keys
in the tables which maps one row in one to one or more rows in the other.
This is the fundamental mechanism of a relational database.
Each of the above tables represents a different entity type, but the
OrderDetails table represents a special kind of entity type because it is
modelling a many-to-many relationship between Orders and Products.
Diagrammatically the model looks something like this:
Customers---<Orders---<OrderDetails>---Products
where the < and > signs represent the 'many' end of each relationship (the
'referencing tables' in the jargon). Take a look at the sample Northwind
database which comes with Access and which uses a model like this.
To show which customers have ordered which products you'd join all four
tables in a query like so:
Customers to Orders on CustomerID
Orders to OrderDetails on OrderID
OrderDetails to Products on ProductID
You can then return the customer names from customers, the order dates from
orders and the product names from products in the result set. You can try
this for yourself in Northwind by opening the query designer, not adding any
tables, switching to SQL view and pasting in the following SQL:
SELECT CompanyName, OrderDate, ProductName
FROM Products INNER JOIN ((Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
ORDER BY CompanyName, OrderDate;
If you then switch to design view you'll see how the same query can be
constructed visually.
You haven't given us a lot of information to go on as regards your specific
scenario, but if you have a Products table with the product #s in one column
and the name in another, and only one row per product that's fine. If you
have a table with two columns like this which can have the same product in
more than one row, e.g. an OrderDetails table, then that is a bad design as
it introduces redundancy, and the risk of inconsistent data. Redundancy is
eliminated by a process known as normalization and is achieved by decomposing
tables into several tables so that each 'fact' is stored once and once only
e.g. the fact that product number 123 is a widget is represented by one row
in a products table and nowhere else in the database.
Is the form you refer to a bound form in which data is entered (you refer to
entering 'info' which suggests it is) and if so into which table, or is it a
dialogue form for selecting a product for reporting purposes?
Ken Sheridan
Stafford, England
Joey said:
Ok so i have a table that has the product #'s in one column and the name in
another....I'm confused on what you mean by foreign key? Can you go in depth
a little more on it all?
Ken Sheridan said:
Presumably you have a Products table with columns [product #] and something
like [product name]. For your report's RecordSource use a query which joins
the Products table to whatever table the product # is in as a foreign key
column. The join will be on the product # columns of course. You can then
return the product name column from the products table in the query and
include a text box bound to that column in the report.
Ken Sheridan
Stafford, England
Joey said:
I have it set up now to where on the form my end-users are using to enter
info they type the product #. I want it to where when I run a report, that
instead of it saying the product # it actually shows the product name.
