Results 1 to 4 of 4
  1. #1
    trevt is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    8

    INNER JOIN SELECT DISCREET issue

    Good day folks,


    Two tables: Budget (Bud_Tag_Number, Bud_FIWP....)
    TO_Tracking (Tag_Number, CheckSheet...)
    Multiple occurrences of Bud_Tag_Number/Bud_FIWP combinations.
    One occurrence of Tag_Number/CheckSheet combinations, but multiple Tag_Number occurrences.

    I want to show all of the Tag_Number/CheckSheets where the Same Tag Numbers in Budget have a Bud_FIWP value the same as a textbox on a form.

    It works without the SELECT DISTINCT after the JOIN, but you get as many duplicates as Tag_Numbers with the FIWP value. (If there are 3 instances of Tag_Number in Budget where Bud_FIWP works, I get Tag_Number/CheckSheet 3 times as well)

    Let me know if I described that wrong... lots of other moving parts in this one.

    Basically I need to SELECT all the Fields FROM TO_Tracking JOINED to a DISTINCT List of Tag_Numbers in Budget WHERE Bud_FIWP = Text30

    Code:
    Me.RecordSource = "SELECT * " _
                & " FROM TO_Tracking INNER JOIN SELECT DISTINCT Budget.Bud_Tag_Number FROM Budget WHERE Budget.Bud_FIWP = '" & Me.Text30 & "' ON Budget.Bud_Tag_Number = TO_Tracking.Tag_Number" _
                & " ORDER BY TO_Tracking.Tag_Number ASC, CheckSheet ASC"
    Thanks for any help on this one.

    Trev
    Last edited by trevt; 09-17-2017 at 10:54 AM. Reason: Ooops. Tab Enter...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    The nested SELECT has to be within parentheses and use an alias name.

    "SELECT TO_Tracking.*" _
    & " FROM TO_Tracking INNER JOIN (SELECT DISTINCT Bud_Tag_Number FROM Budget WHERE Bud_FIWP = '" & Me.Text30 & "') AS Q1" _
    & " ON Q1.Bud_Tag_Number = TO_Tracking.Tag_Number" _
    & " ORDER BY Tag_Number ASC, CheckSheet ASC"

    Can use Access query builder to help construct. First build and save the inner query. Then build outer query that references the first query object. Switch to SQL view and copy/paste the SQL of the first query between parens in front of the first query name and add the AS word. This should help you get the correct structure to use in VBA. Then can delete the first query object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trevt is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    8
    Thanks June7! It was the alias I couldn't see in all my Googling.

    You rock!

    Trev

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Inner Join SQL Issue/SQL Format
    By RAOtto01 in forum Queries
    Replies: 2
    Last Post: 12-11-2015, 05:53 PM
  2. Issue with LEFT JOIN
    By Tortelloni in forum Queries
    Replies: 9
    Last Post: 07-27-2014, 12:56 PM
  3. Query join issue?
    By ajkosby in forum Queries
    Replies: 4
    Last Post: 07-16-2013, 05:03 AM
  4. OUTER and INNER JOIN Issue
    By riaarora in forum Queries
    Replies: 1
    Last Post: 09-02-2012, 08:13 AM
  5. when (select statement within inner join should be used)?
    By learning_graccess in forum Programming
    Replies: 1
    Last Post: 05-17-2012, 01:54 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