Jim:
It would be better to put both tables in a single .mdb file as that way you
can enforce referential integrity. To do this simply create a new .mdb file
and import both tables. Keep the original databases until you are absolutely
satisfied with the new single one, however, as you can then always go back to
square one if necessary.
The two tables contain a lot of redundancy as you will have the customer
details repeated in the Customers table and the Transactions table. All you
need is a foreign key CustomerID column in the Transactions table rather than
repeating the names addresses etc in that table. The elimination of
redundancy is not just economical, but more importantly protects the
integrity of the data by avoiding update anomalies, e.g. where the same
customer might mistakenly be given different address details in separate rows
of the Transactions table. The process of eliminating redundancy is known as
'normalization' and there are formal rules governing this which we don't need
to go into right now.
The first thing to do is identify a set of columns in the tables which
uniquely identify the customer in each, and can consequently be used to join
the tables. Names can be duplicated so you might well need to use address
data as well. The customers names plus the Zip code might be enough, but if
you need to incorporate other address columns this is not a problem and you
should be able to amend the following if necessary. Its essential that these
column's values match exactly in each table, so if you use address data
things like 'Avenue' being used in some rows and 'Ave' in others for the same
address will prevent the matches being made.
Having established the sets of fields which can be confidently used to join
the tables you then need to create a Customer column in the Transactions
table in design view. This should be of the same data type as the Unique
CustomerID primary key column of the Customers table. Thse are usually long
integer numbers, but while the CustomerID in Customers can be an autonumber,
the CustomerID foreign key column in Transactions would be a straightforward
long integer number and uindexed non-uniquely (duplicates allowed).
The next step is to fill the new CustomerID column in Transactions with the
CustomerID values from the matching rows in Customers. This is done with an
update query which joins the two tables on the set of columns which uniquely
identify each customer, so if these are FirstName, LastName and Zip the query
would look like this:
UPDATE Transactions INNER JOIN Customers
ON Transactions.FirstName = Customers.FirstName
AND Transactions.LastName = Customers.LastName
AND Transactions.Zip = Customers.Zip
SET Transactions.CusrtomerID = Customers.CustomerID;
You should now be able to join the tables like so to show all customers with
transactions:
SELECT Customers.FirstName, Customers.LastName, Customers.Zip
Transactions.TransactionAmount, Transactions.TransactionDate
FROM Customers INNER JOIN Transactions
ON Customers.CustomerID = Transactions.CustomerID;
You can show all the customers, regardless of whether thay have any
transactions by using an outer join:
SELECT Customers.FirstName, Customers.LastName, Customers.Zip
Transactions.TransactionAmount, Transactions.TransactionDate
FROM Customers LEFT JOIN Transactions
ON Customers.CustomerID = Transactions.CustomerID;
Once you are happy that all the correct rows are joined you can delete the
redundant customer columns from the Transactions table in table design.
Finally you can create a relationship between the two tables on CustomerID
and in the relationship dialogue enforce referential integrity. This
prevents a transaction being entered for a non-existent customer, and
prevents a customer being deleted if they have any matching rows in
Transactions. You can automatically delete matching transactions rows when
you delete a customer if you wish. This is done by enforcing Cascade
Deletions in the relationship dialogue. If the CustomerIID in Customers is
not an autonumber you should also enforce Cascade Updates. If you then
chanfe a customer's CustomerID any matching rows in Transactions will be
automatically updated to the new CustomerID.
With the two normalized tables set up you can the start building forms,
reports and queries based on the tables. These are best put in a separate
'front end' .mdb file with links to the table in the 'back end'. On a
network the back end would go on the server and a copy of the front end on
each local machine, but even with a single user desktop database its better
to split it. Before designing the front end, however, consider whether any
further normalization is needed. For instance you might want to have
separate States and Cities tables, the former referenced by the latter and
the latter referenced by the Customers table. That way you don't have
redundant multiple references to the cities and sates in the Customers table,
but simply a CityID foreign key.
Ken Sheridan
Stafford, England
