MAKING 3 TABLES WORK TOGETHER

[Guest]

I have a tables named Customers, Products and Transactions.

The products table is filled with all sorts of STANDARD cars.
The customers table is filled with customers (obviously).
The transaction table ties a customer to particular products (cars).

What if a customer bought a non-standard car (special like a hot rod or
something), which wasn't in my products table. I have so far created
another table called Special_Products, but cannot seem to connect it to the
transactions table. Below is how the transaction table looks: -

CUSTOMERID PRODUCTID SPECIALPRODUCTID
C001 P001
C001 P002
C002 P003
C003 Left Blank SP001

Any ideas on this would be most appreicated.

Many Thanks
Graham

 

[Guest]

Transaction_ID = PKey Transaction Table = Autonumber
Customer_ID = PKey Customer Table = Autonumber
Product_ID = PKey Product Table = Autonumber
SProduct_ID = PKey SpecialProduct Table = Autonumber

SProduct_ID = Number Long Integer in Transaction Table and related
one-to-many to the SProduct_ID in Special Product Table

Product_ID = Number Long Integer in Transaction Table and related
one-to-many to the Product_ID in the Product Table

Customer_ID - Number Long Integer in Transaction Table and related
one-to-many to the Customer_ID in the Customer table

Try this concept