Results 1 to 6 of 6
  1. #1
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68

    Idiot proof question.....

    Hi All,



    So I know a little bit about access but not heaps and I have to create a database etc for work….so hoping anyone can help me out with my question below in regards to table setup…..with idiot proof answers please

    Ok so if I have a table that lists all my raw materials for example (sugars, salts, flavourings etc) and a table that lists all my products example: (Mars Bar, Snickers, Kit Kat etc), what is the BEST WAY to set my tables etc up to:

    - list all the materials that exist in the product (could be 50+ for each product)
    - and also the other way, list all the products that a material is used in


    So that I can then create a query that says “show me all the materials that are used in Mars Bar”
    And also a query that says “show me all the products that 104.0003 malted sugar is used in”

    The materials have an ID that is the primary key so that’s their unique identifier (104.0001, 104.0002, 104.0003 etc) and the products have their codes that are their primary key and unique identifier (MB, KK, SK etc)
    There is over 400 materials.....

    I have just used the materials & products as an example…I don’t actually work for Cadbury or Mars….just used them…so sorry, no free chocolate haha

    Thanks everyone in advance

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    Since a product has many materials and a material can be used on multiple products, you have a many-to-many relationship which requires a junction table

    tblProductMaterials
    -pkProdMatID primary key, autonumber
    -fkMatID foreign key to tblMaterials
    -fkProdID foreign key to tblProducts

    With this table you can conduct searches either way as you described.

  3. #3
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68
    Thanks jzwp11!!

    So in that table I just to then enter in all the data for each Material?

    eg.

    ProdMatID MatID ProdID
    1 100.0001 KK
    2 100.0001 MB
    3 100.0001 SNK
    4 204.0003 KK

    So that the table says that MatID 100.0001 exists is KK, MB, & SNK??
    and Material 204.0003 exsists in KK? (sorry the formatting changed so hope you know what I mean)

    Thanks so far and thanks for the Welcome

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Correct. You can say query the table to find out in what products a particular material is used or you can find out which which materials are used in a particular product (i.e. KK uses both 100.0001 & 204.0003)

  5. #5
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68
    Thanks jzwp11.

    that works a treat thanks ALOT for your help!

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome; good luck with your project.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-19-2010, 09:16 AM
  2. Replies: 3
    Last Post: 02-09-2010, 03:50 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