By having separate tables for each sale type you are encoding data as
column names. A fundamental principle of the database relational
model, 'the information principle' is that data is stored as explicit
values at column positions in rows in tables, and in no other way.
You should do two things:
1. Merge the tables into a single Sales table.
2. Then decompose the table into related Sales, SalesTypes and
Customers tables
To merge the tables firstly create a Sales table which matches the
structure of your existing tables, plus a text SaleType column and a
numeric CustomerID column. Then insert rows from your existing tables
by appending all columns fro each of the originals, along with a
constant for the SaleType in each case. So you'll need to execute
three append queries along these lines (simplified), firstly for Mens
sales:
INSERT INTO Sales(Customer, Item, Amount, SaleType)
SELECT Customer, Item, Amount, "Mens"
FROM [Mens Sales];
Do the same for womens sales, changing the constant to "Womens" and
the table name to [Womens Sales], and then again for Youth sales.
Next create Customers table with columns CustomerID (primary key) and
Customer, and fill this with the following append query:
INSERT INTO Customers
SELECT DISTINCT Customer
FROM Sales:
Now update the CustomerID column in Sales with:
UPDATE Sales INNER JOIN Customers
ON Sales.Customer = Customers.Customer
SET Sales.CustomerID = Customers.CustomerID;
Now delete the redundant Customer column from Sales.
Create a SaleTypes table with one Column SaleType (primary key) and
insert three rows into it with values Mens, Womens and Youth. Note
that these values must exactly match the values used as the constants
when inserting rows into the Sales table.
Finally create relationships between Customers and Sales on CustomerID
and between Sales and SaleTypes on SaleType, enforcing referential
integrity in each case. In the relationship between Sales and
SaleTypes also enforce cascade updates as 'natural' keys have been
used rather than numeric 'surrogate' keys as with the relationship
between Customers and Sales. The reason for using surrogate keys in
the latter case is that names can be duplicated, so two or more
customers could have the same name.
I've simplified things slightly above, so there may be more columns
which will need to be appended into Sales and Customers than I've
indicated, but hopefully you'll see the principles involved. Post
back if you need further clarification.
Ken Sheridan
Stafford, England
