Results 1 to 4 of 4
  1. #1
    sjs94704 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2012
    Location
    Berkeley, CA
    Posts
    20

    Request for suggestion for table design/relationship structure

    OK, I'm working on a basic order database. The steps the order goes through are as follows:

    1-Enter Order
    2-Orders get staged to the warehouse for fulfillment
    3-Warehouse workers fill the order (either complete or partial order
    4-For orders that get delivered, it is possible that they can ship PARTIAL products ordered, so there needs to be a way to track partial shipments.

    So I know I need an order table, an order detail table and then I need these other additional tables to track partial (or whole) shipments.

    Suggestions?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It sounds as though operations are already in place and active. I suggest breaking off small pieces and migrating operations to Access one part at a time. Perhaps starting with a Clients module or a Vendor module would be appropriate.

  3. #3
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    Sounds to me like the orders table should have a flag to indicate unfilled, partial, or complete. Then each order detail record should have a qty sent field and when the qty sent is equal to the qty ordered it can be flagged as complete. I don't know what other table you might need.

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    In general practice. Orders that are shipped but only being partial completion create new back ordered orders for the missing completion. You can track the new backorder as being a child of the original order however you wish. I usually use a suffix related to the record. Like order O12345 had 2 items that weren't ready, so O12345A is created. Invoicing can still invoice the previous order and the new order could then flow through the system.

    And if O12345A was a partial, you could bill the partial and backorder the remainder to O12345B etc

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-04-2013, 02:24 PM
  2. Looking for db design suggestion
    By Jamescdawson in forum Database Design
    Replies: 2
    Last Post: 08-14-2012, 10:37 AM
  3. Table and Relationship Design
    By GrayWolf in forum Access
    Replies: 6
    Last Post: 01-31-2012, 02:04 PM
  4. Schedule: Table/relationship structure
    By capnponcho in forum Access
    Replies: 1
    Last Post: 12-18-2011, 01:24 AM
  5. Complex Survey: Table Structure Design and Normalization
    By kevin007 in forum Database Design
    Replies: 2
    Last Post: 07-06-2010, 09:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums