General discussion

  • Creator
  • #2297930

    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 :D)

    please help…thanks in advance…

All Comments

  • Author
    • #2673453

      Reply To: Access DB problem

      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.

    • #2681946

      Reply To: Access DB problem

      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…

    • #2727353

      Reply To: Access DB problem

      by lord foul ·

      In reply to Access DB problem

      This question was closed by the author

Viewing 2 reply threads