INNER JOIN SELECT DISCREET issue

[trevt]

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

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

[June7]

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.

[trevt]

Thanks June7! It was the alias I couldn't see in all my Googling.

You rock!

Trev

[June7]

More about subqueries http://allenbrowne.com/subquery-01.html