dear technophiles…
i am having a problem w/ an access DB. The DB in question is very similar in concept to the Northwind DB that comes w/ MS Access.
The theory of my DB is as follows:
Tables:
customer,order,product,ordered products.
Relationships:
customer (1) – order(many)
order (1) – ordered products (many)
product (1) – ordered products (many)
the ordered products table is a result of the many-to-many relationship between the product and order tables.
the ordered product table contains the primary key fields of both the order and product tables as foreign keys. i have selected them both as a primary key for the ordered product table.
i have populated the customer and product tables w/ records, i then started populating the order table, no problems here.
my problem…
the ORDERED PRODUCTS table. Do i have to re-enter the records from the order table into the ordered products table? it seems to me to be a double up of data entry.
Is there a way where i can enter the data only once? I mean that the ORDERED PRODUCTS table should be the main table/form interface where i enter the sales data and as a consequence the order table is also populated? (or vice/versa)
I am having a conceptual nightmare w/ this…i understand why the ORDERED PRODUCTS table needs to be there (one order can contain many products and many products can be in any given order :D)
please help…thanks in advance…