General discussion


Access DB problem

By Lord Foul ·
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:
customer,order,product,ordered products.

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 )

please help...thanks in advance...

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by john_wills In reply to Access DB problem

The ORDERS table should contain an order number as key and some information about date, customer, etc. The ORDEREDPRODUCTS table should not contain any information from the ORDERS table except the order number. What your probably need is an order input form which has an ORDERPRODUCTS scrolling subform, with information displayed from the PRODUCTS tables according to what you enter - it were probably best to have a dropdown specification of the product number, without the actual product number necessarily appearing.

Collapse -

by Lord Foul In reply to

Poster rated this answer.

Collapse -

by Lord Foul In reply to Access DB problem

How then do i lookup the product name and price in the product table...I made a sql statement that returns the product name minus the ID# however when i choose the product in a form it does not give the actual price...

Collapse -

by Lord Foul In reply to Access DB problem

This question was closed by the author

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums