Results 1 to 14 of 14
  1. #1
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9

    Products and Parts Database

    I am working on my first access database project and I have come to a wall.



    I build products (about 200) and i buy parts to make those products (about 500). I need to create a form (basically a BOM) listing what parts I use to make each product and how much of that part i use. ie 3 x A screws, 5 x G Washers, etc. I do not use all parts on all products. so there will be some that are not used.


    I would really appreciate at any Help i can get.

    ultimate goals would be:

    identifying all parts required to build a product and the qty and cost
    list of parts by vendor so i can keep track of cost changes

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You might start with a Form for the Product and a SubForm for the parts needed to build it.

  3. #3
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    You might start with a Form for the Product and a SubForm for the parts needed to build it.
    I have a table listing all my products. I have another table listing all my parts. I have another table listing all my vendors for those parts.

    but i feel i am missing something.
    how do i see this:

    Product A
    • 2 x Part Z
    • 3 x Part Y
    • 5 x Part X

    Product B


    • 4 x Part Z
    • 3 x Part W
    • 4 x Part V

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You'll need another table to hold the list of parts to beild each product. It will need a PrimaryKey field (AutoNumber) and a Foreignkey field which will be the PK of the Products table. You can then add quantity, Price, and any other field you feel you need in this table.

  5. #5
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    You'll need another table to hold the list of parts to beild each product. It will need a PrimaryKey field (AutoNumber) and a Foreignkey field which will be the PK of the Products table. You can then add quantity, Price, and any other field you feel you need in this table.
    ok...so heres the thought.

    tblPRODUCTS
    ProductID (PK)
    ProductName (text)

    tblVENDORS
    VendorID (PK)
    BusinessName (text)
    ContactInfo (text)

    tblPARTS
    PartID (PK)
    PartDescription (text)
    Vendor ID (PK from tblVENDORS)
    Unit Price (currency)

    TblBOM
    ID(PK)
    PartID(PK FROM tblPARTS)
    Qty Req (number)

    I think thats right. but how do i make sure that for Product A i can list all parts and their qty's. Form? Query? Will there be a table showing me that a particular part is part of that assembly of a product?

    This is what Im not able to grasp the understanding of.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your tblBOM is going to want a FK of the PK from the tblProducts. It is also going to probably want a price field in case the price changes.

  7. #7
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    Your tblBOM is going to want a FK of the PK from the tblProducts. It is also going to probably want a price field in case the price changes.
    Im not sure what you mean about the price changing, but could i bother you to take a look at what I have so far?

    BOM.mdb

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you don't keep the history of the price then any previous builds will always reflect the current price of your parts.

  9. #9
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    If you don't keep the history of the price then any previous builds will always reflect the current price of your parts.
    so where should the price be kept? and how should i add this price?

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you are not worried about previous costs of your products then you only need to have the price in the parts table. Your decision.

  11. #11
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9
    Quote Originally Posted by RuralGuy View Post
    If you are not worried about previous costs of your products then you only need to have the price in the parts table. Your decision.
    Your absolutely right. I would need to keep track of price changes. Would that be done in the parts table where I am thinking of keeping the price or in the bom table? I probably should also keep a date attached to the price so I know when that prices effective date was.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You will notice your db is starting to get a bit complex. When you ship a product, do you plan to have a list of the parts shipped to make up the product?

  13. #13
    aesp533262 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    9
    I won't be keeping inventory so shipping will not be tracked either. I am only using this to keep track of costs.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by aesp533262 View Post
    I won't be keeping inventory so shipping will not be tracked either. I am only using this to keep track of costs.
    I personally would not recommend trying to keep a FIFO on costs; just your current cost should probably do. In which case dates and prices do not seem as important. Just keep the part cost in the Parts table. You can still show it on your BOM Form.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-02-2012, 11:37 AM
  2. Replies: 8
    Last Post: 04-24-2012, 01:05 PM
  3. Parts/Work Orders Database Design
    By snewton in forum Database Design
    Replies: 5
    Last Post: 03-13-2012, 07:06 PM
  4. Replies: 13
    Last Post: 02-29-2012, 07:09 AM
  5. Prohibiting access to parts of a database?
    By Delta223 in forum Access
    Replies: 1
    Last Post: 01-05-2011, 07:31 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