Results 1 to 9 of 9

How to make a query return field values that contain the query criteria in a join table

  1. #1
    MatP is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    5

    How to make a query return field values that contain the query criteria in a join table

    I apologize for the confusing title, I can't really think of a way to explain the issue. I've put in 15+ hours of web searching on this issue with no luck, so I decided to join this forum and see if I could get some guidance here.

    Here is what I have:
    - A Parts table
    - A Specification table
    - A join table to associate multiple specifications to every part

    What i ask myself in my day-to-day is, given one or many specifications, which are the parts that are linked to them?

    In other words, I would like to handpick a few specifications and build a query that would show me which parts are linked to all the picked specifications.



    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,707
    In the query ,if you join tSpec.Part = tParts.Part....
    it wont show you what you want?

    I think you need a 3rd table.
    spec table
    part table,
    project spec table

    the ProjectSpec would have specs from the tSpec table that were selected for that 1 project, THEN you join this to the tPart table.

  3. #3
    MatP is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    5
    See attachment.

    I have 3 tables. the third one being the join table between parts and specifications
    Attached Thumbnails Attached Thumbnails 2019-10-01_132803.png  

  4. #4
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    156
    Can you upload a zipped copy of your database?

  5. #5
    MatP is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    5
    I made a new database with the structure I have in the main one (see attached)

    So what i'm looking to achieve is, for example:

    pass SpecID 2 and 3 (MIL-A-8625 and DCMP-161) to a query and have it return PartID 2 (16131-1).
    Pass SpecID 2 (MIL-A-8625) and have the query return PartID 2, 3, 4, 5 (16131-1, 200-2417-101, 901-036-696-101, 74A400650-1)
    Attached Files Attached Files

  6. #6
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    156

    Queries

    Hi

    See query1 for SpecID 2 & 3

    See query2 for SpecID 2
    Attached Files Attached Files

  7. #7
    MatP is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    5
    Hey Mike,

    nice try. I see from the design view of the queries that you used the PartsID as an input. My issue is that PartsID is actually what I'm looking for given SpecID.

    Picture a baker with this database: He has a bunch of different cakes in a table and a bunch of different ingredients in another. A third table is used to define what ingredients should be used in what cakes. One day the baker sees he has a big surplus of cinnamon and flour. He then wants to query the database to see which of his cakes are made with both cinnamon and flour. This is my issue exactly, only I deal with parts and specs.

    There's likely a lengthy way to do it in VBA, but I just feel like this is something queries should be able to handle with grace. I just can't figure it out yet.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,303
    Here is a query to return the Parts that use/involve Spec 2 OR 3.

    Code:
    SELECT PartsT.PartName
    , PartSpecT.SpecID
    FROM PartsT INNER JOIN PartSpecT 
    ON PartsT.PartsID = PartSpecT.PartID
    WHERE ((PartSpecT.SpecID)in(2,3));

    This one identifies Parts that use SPEC 2 And SPEC 3

    Code:
    SELECT PartsT.PartsID
    , PartsT.PartName
    , PartSpecT.SpecID
    , PartSpecT_1.SpecID
    FROM (PartsT INNER JOIN PartSpecT ON 
           PartsT.PartsID = PartSpecT.PartID)
     INNER JOIN PartSpecT AS PartSpecT_1 ON
           PartsT.PartsID = PartSpecT_1.PartID
    WHERE (((PartSpecT.SpecID)=2) AND ((PartSpecT_1.SpecID)=3));

  9. #9
    MatP is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    5
    orange,

    big thanks for your help, you solved this thread.

    I had the "OR" case figured out, but the "AND" one was escaping me.

    Extrapolating, I assume if I had more than 2 inputs, I would simply modify the SELECT clause with additional PartSpecT_X.SpecID arguments, the FROM clause would have additional INNER JOIN clauses and the WHERE clause added AND arguments.

    This was a very informative experience. Thanks also to mike60smart and ranman256 for taking some time to look at this for me.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-05-2017, 02:50 AM
  2. Replies: 4
    Last Post: 08-01-2016, 06:41 AM
  3. Replies: 5
    Last Post: 03-15-2014, 10:24 AM
  4. Replies: 3
    Last Post: 01-21-2014, 12:28 AM
  5. Replies: 2
    Last Post: 11-21-2013, 09:49 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
  •  
Tech Forums: Microsoft Office Forums