Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jan 2012
    Posts
    24

    Unanswered: How to associate data from 2 tables

    I have a table with a list of products in it. I have a couple of other tables that list the parts that go into the products. How can I associate the parts with the products? Something like:

    Table 1 Products A, B, C
    Table 2 Parts for product A
    Table 3 Parts for product B
    Table 4 Parts for product C

    I'd like to choose a product, ie A and have only those parts for Product A available to choose from.

    Hope this makes sense. I guess this would all happen in a form when I'd choose Product A, and the parts for Product A would load. Same when I'd choose Product B only those parts would load, etc.

    Thanks,
    Don.......

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is a flaw in the design of your database. All parts should be stored into a single table with a column indicating the concerned product.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    24
    Ok, I thought about that originally but the parts associated with the products are more than 500. So if I put the products and parts in the same table, two different columns or several different columns., can I then choose Product A and have only those parts for Product A available? If so how do I make that happen?

    Thanks again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a table for products
    and a 'gozinto' table (ie X of part A go into part B, call 'em say component and Assembly)
    component and assembly form the PK of the gozinto table
    component and assembly in the gozinto table are FK's to the PK in the products table

    finding parts that make an assembly
    query the gozinto table to find the components which comprise the assembly
    then for each component find what components comprise that assembly. Academic research suggests that you will probably find no more that 5 levels, but you could possibly build your query with more levels than that just in case your product blows that system.

    have a look at this to give an idea of how to explode the part tree
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2007
    Posts
    34
    Ok,
    So I understand some of what you are saying and I've read the reference you suggested. I'm still not there. Here's what I got now:

    One Table (with all the data) looks like this;
    ID Product Parts
    1 A NULL
    2 B NULL
    3 C NULL
    4 xx 1
    5 xz 1
    6 zz 2
    7 zx 2
    8 aa 3
    9 ab 4

    So how would I make my form load all the parts for A when I choose A and all the parts for B when I choose B?
    I'm not understand everything you've said and also I'm not understanding all that was in the document you refered me to.

    Don...........

  6. #6
    Join Date
    Oct 2007
    Posts
    34
    As I look at this after I posted the 111,222,333 etc are in a separate third column. ID is one column, Product is a second column, PartsID is the third colunm.

    Don.........

  7. #7
    Join Date
    Oct 2007
    Posts
    34
    Another question pertaining to the document you referred me to.
    Column "parentid" in this document shows the data to be text and numbers. The code shows this field as an "integer" but hows that possible with both text and numbers in the field?

    Thanks again,
    Don.........

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the implementation of the hierarchy is irrelevant, it is what it is. if your parts PK is alphanumeric then use alphanumeric in place of integer

    to display parts used I'd use a combo box
    prefill the combo box with DISTINCT items for the gozinto table that have a parentID or if you want top level assemblies, then only those parts that do not appear in the gozinto component (thats done by using queries) the query wizard should help you
    user selects the assembly (top level part), then you display all parts that go into that assembly
    how you manipulate the data is upto you
    you could only pull up the components that go into the selected assembly
    or you could explode each component assembly into its components recursively until all component parts are displayed

    so what you have is:-
    1,2 & 3 are top level assemblies
    1 is comprised of one each of 4 & 5
    4 is comprised of one 9
    2 is comprised of one each of 6 & 7
    3 is comprised of one of 8
    unless there is only ever a one to one match between a component and its final assembly/parent you'd need to have a quantity in the gozinto table

    an example:- a plastic box comprises a base and a cover, 4 screws to hold it together, 4 washers & 4 screw inserts

    product (ID,Description)
    2 Base Plate 100 x 100 x 90mm
    3 Coverplate 100 x 100 x 10
    4 M2 Cap Head Bolt, 25mm
    5 M2 Washer
    6 M2 Thread insert
    1 Plastic Box, 100 x 100 x 100
    The thread insert is fitted to the baseplate at the time of moulding
    7 Polymer

    gozinto (product, qtyr required, used in product)
    2 1 1 '1 baseplate required
    3 1 1 '1 coverplate required
    4 4 1 '4 each of Bolt
    5 4 1 'washer
    7 25 2 '25g of polymer required for baseplate
    6 4 2 '4 thread inserts used in the moulding process fitted to the baseplate
    7 5 3 '5g of polymer required to make coverplate

    so you have 3 assemblies (the finished product:1), the injection moulded baseplate and coverplate sub assemblies
    and the bolt and washer required to complete the box
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2007
    Posts
    34
    Ok, I've got my table structured as you recommended but I'm not understanding some other things.

    First, what's a gozinto table. I think I understand the meaning but I'm not sure how to create one.

    Second, I'm stuck on creating the query that is referenced in the document you refered. I'm not sure what goes where. I've been trying several different configurations of the SQL but I'm not getting it done. Ive got 3 columns, "ID", "Products", and "PartsID".

    I've got 30 products, 700 parts for the 30 products. I'd like to have the parts associated with a product load everytime the product is chosen.

    Not sure I'm making any sense that you might understand but I hope so.

    Thanks,
    Don........

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its just a table name, like products, persons, sales....
    Gozinto.....
    Goes in to....
    What part(s) goes in to what other part(s)

    Its a junction / intersection table but instead of referencing 2 different tables its referencing the same table. As the quantity used is an attribute of this junction its also stored here.

    Bear in mind that the same part may be used more than once in the same assembly, the same part may be used in more than one assembly within the top level assembly . Fasteners are a common example this. Also the same part may be used in different assemblies
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2007
    Posts
    34
    I'm still trying to get this to work. In your response of 1/9/14 @ 5:12 you suggested "try this" with a link. I've been trying to work off of that model but I keep getting a Syntax error (missing operator) in query expression........
    I copied the complete example in that document and called it Sample. I thought I typed something incorrectly so I copied and pasted the exact code into the sample database and I get the same error.
    Can you put me in the right direction to fix this?

    Thanks,
    Don...........

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    certainly....

    ....but what isn't working
    where are you getting your syntax error,pleae supply the actual SQL NOT the VBA that generates the SQL
    what is your data model/table & column design
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2007
    Posts
    34
    Ok I've attached the db to this post. It's basically the example that you referred me to a couple of posts back. However when I try to paste the SQL into the query I get the error message so here is the SQL:

    select root.name as root_name
    , down1.name as down1_name
    , down2.name as down2_name
    , down3.name as down3_name
    from categories as root
    left outer
    join categories as down1
    on down1.parentid = root.id
    left outer
    join categories as down2
    on down2.parentid = down1.id
    left outer
    join categories as down3
    on down3.parentid = down2.id
    where root.parentid is null
    order
    by root_name
    , down1_name
    , down2_name
    , down3_name

    This produces the error. The rest of the db should be attached. I thought if I copied this example you provided I could then figure out how to make mine query work.

    I've tried to upload a jpg of my table but the upload keeps failing. I only have three fields in the table:
    ID (as number, integer and primary key)
    name (as text)
    parentid (as text)


    Thanks again,
    Don.........

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    can see no sign of the db

    the syntax error is almost certainly going to be in the JOIN, as Access does JOINS its own way
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Jan 2012
    Posts
    24
    if you look at my last post you'll see the SQL for the query with the Join code. I also included the 3 table names in my table and what they are, "id, name, parentid".
    I wasn't able to attach the db nor a picture of any of the tables or queries so I just typed the code into my message.
    If you believe it's in the Join, what must I do to avoid the Syntax error?

    Thanks once more,

Posting Permissions

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