Results 1 to 14 of 14
  1. #1
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12

    A table of sub-parts and quantity for each record.

    Good morning all,

    I am new to access and to this forum. I am trying to create a simple database that keeps track of carpentry products (each has a list of items needed to build these furniture and their quantity). Can you recommend how to organize it in a way that (1) These sub-items are selected from a table and (2) each sub-item has a quantity an.

    You can see my example in the attached file.

    Thanks a bunch!
    Attached Files Attached Files

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You have pretty much got it split out right in terms of how to put it into a database with a small modification

    Code:
    tblPart
    P_ID  P_Description
    1     Plywood 2x3
    2     60" Legs
    3     Plywood 5x5
    4     5" Screws
    5     Plywood 6x6
    6     8" Screws
    7     72" Legs
    
    tblProduct
    PR_ID  PR_Name
    1      Chair A
    2      Chair B
    3      Table A
    4      Table B
    
    tblProductPart
    PPR_ID  PR_ID  P_ID  Qty
    1       1      1     6
    2       1      2     4
    3       1      3     2
    4       1      4     32
    5       2      1     6
    6       2      2     4
    7       2      3     2
    8       2      4     45 
    etc...
    where all of your ID fields are autonumbers, just to make building your primary key easy on you.

  3. #3
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12

    Re

    Thanks - it works now

    Quote Originally Posted by rpeare View Post
    You have pretty much got it split out right in terms of how to put it into a database with a small modification

    Code:
    tblPart
    P_ID  P_Description
    1     Plywood 2x3
    2     60" Legs
    3     Plywood 5x5
    4     5" Screws
    5     Plywood 6x6
    6     8" Screws
    7     72" Legs
    
    tblProduct
    PR_ID  PR_Name
    1      Chair A
    2      Chair B
    3      Table A
    4      Table B
    
    tblProductPart
    PPR_ID  PR_ID  P_ID  Qty
    1       1      1     6
    2       1      2     4
    3       1      3     2
    4       1      4     32
    5       2      1     6
    6       2      2     4
    7       2      3     2
    8       2      4     45 
    etc...
    where all of your ID fields are autonumbers, just to make building your primary key easy on you.

  4. #4
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12
    Good morning,

    So I tweaked it a bit to apply to my database features. Now I want to design a query (I think that what it is for anyway) so that users can quickly display a Bill of Material of a certain product with its subassembly parts and respective quantities. Any suggestions?

    Thanks - please see attached. Please note that in the two sample products, Edge and Edge W/ Rubber Strips and their only difference is that the latter has the 4 additional Rubber Strips.


    Part Database.zip

    Quote Originally Posted by rpeare View Post
    You have pretty much got it split out right in terms of how to put it into a database with a small modification

    Code:
    tblPart
    P_ID  P_Description
    1     Plywood 2x3
    2     60" Legs
    3     Plywood 5x5
    4     5" Screws
    5     Plywood 6x6
    6     8" Screws
    7     72" Legs
    
    tblProduct
    PR_ID  PR_Name
    1      Chair A
    2      Chair B
    3      Table A
    4      Table B
    
    tblProductPart
    PPR_ID  PR_ID  P_ID  Qty
    1       1      1     6
    2       1      2     4
    3       1      3     2
    4       1      4     32
    5       2      1     6
    6       2      2     4
    7       2      3     2
    8       2      4     45 
    etc...
    where all of your ID fields are autonumbers, just to make building your primary key easy on you.
    Last edited by namy77; 08-15-2017 at 08:57 AM. Reason: forgot to attach file

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    SELECT prod.PR_Name As [ProdName], part.P_Description As [Part], SUM(prodpart.Qty) AS Quantity
    FROM tblProduct prod ((LEFT OUTER JOIN tblProductPart prodpart ON prodpart.prID = prod.PR_ID) LEFT OUTER JOIN tblPart part ON part.P_ID = prodpart.P_ID)
    WHERE PR_ID IN (1)
    GROUP BY prod.PR_Name, part.P_Description
    ORDER BY prod.PR_Name, part.P_Description

    In where clause, you can have a comma-separated list of product ID's, or you can omit the where clause - then you get a full list of products and needed parts.

  6. #6
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12
    Hi,

    I am not quite sure how I would incorporate this to my database? Is this a macro?

    Quote Originally Posted by ArviLaanemets View Post
    SELECT prod.PR_Name As [ProdName], part.P_Description As [Part], SUM(prodpart.Qty) AS Quantity
    FROM tblProduct prod ((LEFT OUTER JOIN tblProductPart prodpart ON prodpart.prID = prod.PR_ID) LEFT OUTER JOIN tblPart part ON part.P_ID = prodpart.P_ID)
    WHERE PR_ID IN (1)
    GROUP BY prod.PR_Name, part.P_Description
    ORDER BY prod.PR_Name, part.P_Description

    In where clause, you can have a comma-separated list of product ID's, or you can omit the where clause - then you get a full list of products and needed parts.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    This is a query, and it returns a recordset. Depending what you need, you can use it as:
    a) a saved query, which you can run manually, call it from VBA, or use as datasource for form or report. You can have a parameter in WHERE clause for saved query too [like WHERE PR_ID IN (?)];
    b) a datasource for form or report (you type/copy the proper query text directly into according field in reports/forms properties. Usually in this case you use the query without WHERE clause, and define filtering conditions for form/report instead;
    c) define and run it in VBA, and process returned recordset using VBA code.

  8. #8
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12
    Good afternoon,

    See attached - I attempted at creating a simple query using the available wizard. They seem to return with type mismatch in JOIN expression

    Part Database (2).zip

    Is it possible because I misunderstand how relationships work here in these 3 tables? I tried to link some autonumber with number and perhaps this is why it cannot query for the information?

    I apologize, my skills and understanding of access is very minimal.

    Thanks!

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Without any changes to tables design, this query worked:

    SELECT name.[Product Name], ass.SubAss, Sum(bom.[SubAss Qty]) AS [Qty]
    FROM ((tblName AS name LEFT JOIN [Product BOM] AS bom ON name.M_ID = bom.M_ID) LEFT JOIN SubAss AS ass ON bom.SubAss_ID = ass.SubAss_ID)
    GROUP BY name.[Product Name], ass.SubAss;

  10. #10
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12

    Re

    Yep - it works. I finally understood the data integrity concept and have all tables agree with each other throughout.

    I am exploring lookup tool in field. So as you can see that in the table Product BOM, it is hard to see which product I am entering parts and quantities for (just 1 or 2 - the autonumber assigned in the tblName) - so I inserted a column after M_ID to look up M_ID to hope return value from tblName - but it seems that I can only select from one table? How can I look up let's say the value in the M_ID of Product BOM against a list from tblName?

    Thanks! I added a text field with Excel's good ole Vlookup function to express what I need in Access's terms. Thanks! Part Database (3).zip



    Quote Originally Posted by ArviLaanemets View Post
    Without any changes to tables design, this query worked:

    SELECT name.[Product Name], ass.SubAss, Sum(bom.[SubAss Qty]) AS [Qty]
    FROM ((tblName AS name LEFT JOIN [Product BOM] AS bom ON name.M_ID = bom.M_ID) LEFT JOIN SubAss AS ass ON bom.SubAss_ID = ass.SubAss_ID)
    GROUP BY name.[Product Name], ass.SubAss;

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    1. It is a bad practice to have calculated fields in tables;
    2. It is a bad practice to use tables to enter new data - no way to control, are entered data correct, or not.

    To enter and edit data, forms must be used. I designed a simple form to register and view products, and a subform in it to enter/edit BOM lists for every product, based on tables tblName and Product BOM, as an example.
    Attached Files Attached Files

  12. #12
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12
    Good morning,

    How did you create the subform fBOM that pulls the SubAss Name from the SubAss Table? I assume you created this subform from the Product BOM table?

    Thanks!

    Quote Originally Posted by namy77 View Post
    Yep - it works. I finally understood the data integrity concept and have all tables agree with each other throughout.

    I am exploring lookup tool in field. So as you can see that in the table Product BOM, it is hard to see which product I am entering parts and quantities for (just 1 or 2 - the autonumber assigned in the tblName) - so I inserted a column after M_ID to look up M_ID to hope return value from tblName - but it seems that I can only select from one table? How can I look up let's say the value in the M_ID of Product BOM against a list from tblName?

    Thanks! I added a text field with Excel's good ole Vlookup function to express what I need in Access's terms. Thanks! Part Database (3).zip

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Yes, the subform has table Product BOM as datasource. Two forms are linked through tblName.M_ID and [Product BOM].M_ID (look at LinkMasterFields and LincChildFiels properties of subform). This ensures, that:
    a) the subform displays always the set of records with M_ID same as in parent form;
    b) whenever you add a new row into subform, [Product BOM].M_ID for new record is set equal to M_ID in parent form.

    The combo box for assembly part has [Product BOM].Subass_ID as ControlSource.
    The RowSource is a query, which gets a 2-column recordset from SubAss table - SubAss_ID, TRIM(SubAss_Code & ":" & SubAss_Name). (I don't have Access at home, so I can't get actual field names.)
    The BoundColumn property is set to 1 - i.e. combo is linked to 1st column of RowSource recordset, which returns SubAss_ID.
    The ColumnCount is set to 2, as RowSource recordset has 2 columns.
    The ColumnWidth property is set to 0,2.5 or 0;2,5 depending on Windows Regional Setings, so you see only second column of combos RowSource recordset on form. NB! Access shows only first visible column or RowSource recordset, so when you set the width of first colum not 0, you see only SubAss_ID in combo.

  14. #14
    namy77 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2017
    Posts
    12
    I am struggling with this a little bit - I tried to create a form to add more Subassemblies in the SubAss table, but this does not result in expanding the list for neither the fBom form or the fProducts form?

    Maybe I misunderstood the way these tables are structured? Thanks~!

    Quote Originally Posted by ArviLaanemets View Post
    Yes, the subform has table Product BOM as datasource. Two forms are linked through tblName.M_ID and [Product BOM].M_ID (look at LinkMasterFields and LincChildFiels properties of subform). This ensures, that:
    a) the subform displays always the set of records with M_ID same as in parent form;
    b) whenever you add a new row into subform, [Product BOM].M_ID for new record is set equal to M_ID in parent form.

    The combo box for assembly part has [Product BOM].Subass_ID as ControlSource.
    The RowSource is a query, which gets a 2-column recordset from SubAss table - SubAss_ID, TRIM(SubAss_Code & ":" & SubAss_Name). (I don't have Access at home, so I can't get actual field names.)
    The BoundColumn property is set to 1 - i.e. combo is linked to 1st column of RowSource recordset, which returns SubAss_ID.
    The ColumnCount is set to 2, as RowSource recordset has 2 columns.
    The ColumnWidth property is set to 0,2.5 or 0;2,5 depending on Windows Regional Setings, so you see only second column of combos RowSource recordset on form. NB! Access shows only first visible column or RowSource recordset, so when you set the width of first colum not 0, you see only SubAss_ID in combo.

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

Similar Threads

  1. Replies: 4
    Last Post: 06-16-2015, 03:53 AM
  2. duplicate a record if the quantity is >1
    By snipe in forum Programming
    Replies: 3
    Last Post: 04-21-2014, 09:56 AM
  3. Replies: 1
    Last Post: 12-01-2013, 08:13 PM
  4. Lookup OnHand Quantity F/ Products Table
    By burrina in forum Forms
    Replies: 1
    Last Post: 11-30-2012, 11:22 PM
  5. Replies: 3
    Last Post: 08-02-2012, 11:37 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