Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004

    Unanswered: Access Table Structure Help

    Good day to everyone, just cannot figure this out at the moment, wondering if anyone has any insights.

    The tables involved are
    Purchase Order, [PO]
    Purchase Order Details

    The Product table has the ProductID as primarykey.

    The Purchase Order Table's primarykey is POID.

    whereas the Purchase Order Details is linked to the PO table via POID
    and is linked to the Product Table via ProductID

    It is now configured where one POID can have many ProductID's.
    and each POID containing one shipping location.

    That however is not the solution that is required.

    The problem right now is each ProductID can actually be sent to more than one location, leaving the single shipping location inadequate.

    The other issue is the POID being subdivided into subPOS.

    Eg. Main PO number is ABC1234

    But it can be subdivided into ABC1234 dept40 -> dept 40 being the different shipping location.

    This example would make it easier to grasp.

    Main PO Number : ABC1234

    POID Quantity

    Product0001 100

    Product0002 100

    Product0003 200

    Product0001 and product0002 can be shipped to location X.

    but Product0003 can be shipped to location Y and Z
    Where each location can have individual quantities.

    I am having trouble with the structure of how to design the tables without having too much redundancy.

    Any ideas?

  2. #2
    Join Date
    Mar 2004

    Re: Access Table Structure Help

    Hi sotong.
    I've thought that:

    A new table "shipping", with POID as foreign key. Then each record of this table has the POPK, the shipping location and the quantity and product.

    For example

    POID -------- shipping ------- product ------- quantity

    2356 --------- LA --------------oranges---------5436
    2356 --------- Mexico ---------oranges---------54
    345 --------- Madrid ---------pommes ---------2365

  3. #3
    Join Date
    Apr 2004
    Hey pipeio,

    Thanks for the insight! That makes so much sense, no idea why I didn't think of it earlier.

    Will work on it right away. Thanks dude!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts