Results 1 to 3 of 3
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Goods In / Out Design Ideas

    I'm struggling with table relationships with regards to a Goods In and Out Database

    So.

    I have Goods that are ordered in. They come with a Clearance Number. I can have multiple Clearance Numbers for a Product.
    eg.
    ClearanceID
    ClearanceNumber
    CearanceDate

    Then I will categorise the goods in by product - New Table.

    GoodsInID - PK
    ClearanceID - FK from table above
    ProductID - FK from a list of products
    NumberGoodsIn

    However I need to have goods go out. Now the goods out will need to be from the overall total.

    My own confusion is that If I have



    GoodsInID - 001
    ClearanceID - 001
    ProductID - 45
    NumberGoodsIn 300

    GoodsInID - 002
    ClearanceID - 001
    ProductID - 12
    NumberGoodsIn 400

    GoodsInID - 003
    ClearanceID - 002
    ProductID - 45
    NumberGoodsIn 400

    What is the best way to link goods out to take away from eg. ProductID 45 NumberGoodsIn 700 ?

    I was thinking of GoodsInID as a FK to the table GoodsOut, but in my mind I cant see that this is correct as on the reporting side I may have issues consolidating my aggregate queries etc.

    Am I overthinking this?

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Steven

    This example has Goods in (Aquired) and Goods Out (Invoiced)

    http://allenbrowne.com/AppInventory.html
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    It is bad practice to store values that can easily be calculated when required

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

Similar Threads

  1. Form Design Ideas
    By Steven19 in forum Forms
    Replies: 5
    Last Post: 03-09-2021, 05:43 AM
  2. Replies: 5
    Last Post: 05-24-2018, 06:52 AM
  3. UI Design Ideas (Just for Fun)
    By AccessPower in forum Database Design
    Replies: 22
    Last Post: 01-13-2017, 04:06 PM
  4. Design Ideas?
    By Voodeux2014 in forum Database Design
    Replies: 3
    Last Post: 10-26-2015, 11:24 AM
  5. Design Ideas
    By ajs112 in forum Access
    Replies: 4
    Last Post: 10-08-2014, 02:35 PM

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