Include al records from- but limit to linked table

[Gismo]

Hi All,

Please could you assist?
I have 2 tables linked by option 2 joint properties, then linked to a 3rd table by option 1 (Only include where equal).
The query works fine but shows all records not matching.
The 3rd table is where i need to limit the result, if there is no record in 3rt tbl, it should not display in query.

SELECT [SB/AD Master].Category, [SB/AD Master].[Aircraft Type], [SB/AD Master].Series, [SB/AD Master].Index, [SB/AD Master].[SB/AD No], [SB/AD Master].Applicable, [SB/AD Master].Description, [SB/AD Master].[Code / Status], [SB/AD Master].[Revision Status], [SB/AD Master].[Repetition Hours], [SB-AD History].Frequency
FROM [Aircraft Type] INNER JOIN ([SB/AD Master] LEFT JOIN [SB-AD History] ON ([SB/AD Master].Index = [SB-AD History].Index) AND ([SB/AD Master].Series = [SB-AD History].Series) AND ([SB/AD Master].[Aircraft Type] = [SB-AD History].Type) AND ([SB/AD Master].[SB/AD No] = [SB-AD History].[SB/AD No])) ON ([Aircraft Type].Series = [SB/AD Master].Series) AND ([Aircraft Type].[Aircraft Type] = [SB/AD Master].[Aircraft Type])
WHERE ((([SB/AD Master].Applicable)=True) AND (([SB-AD History].Frequency) Is Null) AND (([SB-AD History].Type) Is Null) AND (([SB-AD History].Series) Is Null) AND (([SB-AD History].[SB/AD No]) Is Null) AND (([SB-AD History].Index) Is Null) AND (([SB-AD History].[Aircraft Registration]) Is Null) AND (([SB-AD History].[Revision Status]) Is Null))
ORDER BY [SB/AD Master].Category, [SB/AD Master].[Aircraft Type], [SB/AD Master].Series, [SB/AD Master].[SB/AD No];

 

[isladogs]

Your description is a little confusing
Sounds like you need to change filter criteria to: Is Not Null.

As mentioned in a previous thread, you really need to sort out your table structure. Add PK field in each table. Remove all special characters

 

[Gismo]

Sorry, let me try again.
I am listing Master file without matching History.
Master file and history file link works great.
but I want to add the Aircraft type File to only show aircraft listed in the type file, if not I dont want to list it.

 

[isladogs]

That's what I thought & my suggestion should give you exactly that

SELECT [SB/AD Master].Category, [SB/AD Master].[Aircraft Type], [SB/AD Master].Series, [SB/AD Master].Index, [SB/AD Master].[SB/AD No], [SB/AD Master].Applicable, [SB/AD Master].Description, [SB/AD Master].[Code / Status], [SB/AD Master].[Revision Status], [SB/AD Master].[Repetition Hours], [SB-AD History].Frequency
FROM [Aircraft Type] INNER JOIN ([SB/AD Master] LEFT JOIN [SB-AD History] ON ([SB/AD Master].Index = [SB-AD History].Index) AND ([SB/AD Master].Series = [SB-AD History].Series) AND ([SB/AD Master].[Aircraft Type] = [SB-AD History].Type) AND ([SB/AD Master].[SB/AD No] = [SB-AD History].[SB/AD No])) ON ([Aircraft Type].Series = [SB/AD Master].Series) AND ([Aircraft Type].[Aircraft Type] = [SB/AD Master].[Aircraft Type])
WHERE ((([SB/AD Master].Applicable)=True) AND (([SB-AD History].Frequency) Is Not Null) AND (([SB-AD History].Type) Is Not Null) AND (([SB-AD History].Series) Is Not Null) AND (([SB-AD History].[SB/AD No]) Is Not Null) AND (([SB-AD History].Index) Is Not Null) AND (([SB-AD History].[Aircraft Registration]) Is Not Null) AND (([SB-AD History].[Revision Status]) Is Not Null))
ORDER BY [SB/AD Master].Category, [SB/AD Master].[Aircraft Type], [SB/AD Master].Series, [SB/AD Master].[SB/AD No];

Having said that you have so many joins and so many field filtered as not null you may not get all the records you want.
Having PK fields and joining tables by the PK would make this simple

 

[Gismo]

awesome, simple fix but overlooked that

 

[isladogs]

You're welcome but an even simpler solution may be to change the outer joins to inner joins and remove all the Is Not Null filters.

SELECT [SB/AD Master].Category, [SB/AD Master].[Aircraft Type], [SB/AD Master].Series, [SB/AD Master].Index, [SB/AD Master].[SB/AD No], [SB/AD Master].Applicable, [SB/AD Master].Description, [SB/AD Master].[Code / Status], [SB/AD Master].[Revision Status], [SB/AD Master].[Repetition Hours], [SB-AD History].Frequency
FROM [Aircraft Type] INNER JOIN ([SB/AD Master] INNER JOIN [SB-AD History] ON ([SB/AD Master].Index = [SB-AD History].Index) AND ([SB/AD Master].Series = [SB-AD History].Series) AND ([SB/AD Master].[Aircraft Type] = [SB-AD History].Type) AND ([SB/AD Master].[SB/AD No] = [SB-AD History].[SB/AD No])) ON ([Aircraft Type].Series = [SB/AD Master].Series) AND ([Aircraft Type].[Aircraft Type] = [SB/AD Master].[Aircraft Type])
WHERE [SB/AD Master].Applicable=True
ORDER BY [SB/AD Master].Category, [SB/AD Master].[Aircraft Type], [SB/AD Master].Series, [SB/AD Master].[SB/AD No];

You may need to add/remove a bracket if it errors
All being well you will get the same results.
The reason I'm being cautious is you have four joined fields but seven fields specified as not null