Include al records from- but limit to linked table (1 Viewer)

G

Gismo

Registered User.
Local time
Today, 21:00
Joined
Jun 12, 2017
Messages
1,298
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.

Code: 
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];

Capture.PNG
 
isladogs

isladogs

MVP / VIP
Local time
Today, 19:00
Joined
Jan 14, 2017
Messages
18,211
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
 
G

Gismo

Registered User.
Local time
Today, 21:00
Joined
Jun 12, 2017
Messages
1,298
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

isladogs

MVP / VIP
Local time
Today, 19:00
Joined
Jan 14, 2017
Messages
18,211
That's what I thought & my suggestion should give you exactly that

Code: 
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
 
G

Gismo

Registered User.
Local time
Today, 21:00
Joined
Jun 12, 2017
Messages
1,298
awesome, simple fix but overlooked that
 
isladogs

isladogs

MVP / VIP
Local time
Today, 19:00
Joined
Jan 14, 2017
Messages
18,211
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.

Code: 
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
 
Last edited:
You must log in or register to reply here.

Similar threads

G
Only update if today's date is not in the table being updated
Replies
1
Views
365
CJ_London
C
L
Multiple Linked Databases to a form - Trying to create a new record
Replies
8
Views
470
GPGeorge
GPGeorge
U
  • Question
How to copy data from PTQ to Access Table?
Replies
3
Views
402
Minty
Minty
D
Master/Child Field is Null when detail subform has no records
Replies
6
Views
957
Pat Hartman
P
B
VBA problem relating to SQL linked table
Replies
6
Views
530
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom