How do I show all matched / unmatched records in a linked table?

[DAmoss]

Hi,

I'm trying to get my query to show all of my QUAL.[PCR Descriptions] that are contained within a linked table, even if my main [INDIVIDUALS Starter Info] table has no values matched against them. Can someone help me please and tell me where I'm going wrong?

Basically, I need to show the full QUAL.Value, QUAL.[PCR Description] list even if there are no values matched against QUAL.Value from the main table.

I hope that makes sense!

Here's my current SQL query which only gives me matching records:

SELECT QUAL.Value, QUAL.[PCR Description], Sum(IIf([GEND]="1",1,0)) AS Male, Sum(IIf([GEND]="2",1,0)) AS Female
FROM QUAL INNER JOIN [INDIVIDUALS Starter Info] ON QUAL.Value = [INDIVIDUALS Starter Info].QUAL
WHERE ((([INDIVIDUALS Starter Info].STATUS)="3"))
GROUP BY QUAL.Value, QUAL.[PCR Description], QUAL.SortNum
ORDER BY QUAL.SortNum;

Thanx in advance

 

[SkipVought]

Double click the link representing the joined QUAL fields and select the appropriate join option.

Rather than an INNER join, you want to LEFT OUTER join.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DAmoss]

SkipVought:

I tried that and it doesn't work, is it because I've got expressions in there? Here's my new SQL:

SELECT Sum(IIf([GEND]="1",1,0)) AS Male, Sum(IIf([GEND]="2",1,0)) AS Female, QUAL.[PCR Description]
FROM QUAL LEFT JOIN [INDIVIDUALS Starter Info] ON QUAL.Value = [INDIVIDUALS Starter Info].QUAL
GROUP BY QUAL.[PCR Description], [INDIVIDUALS Starter Info].STATUS
HAVING ((([INDIVIDUALS Starter Info].STATUS)="3"));

Cheers

 

[dhookom]

Try:

SELECT Sum(Abs([GEND]="1")) AS Male, 
Sum(Abs([GEND]="2")) AS Female, QUAL.[PCR Description]
FROM QUAL LEFT JOIN [INDIVIDUALS Starter Info] ON QUAL.Value = [INDIVIDUALS Starter Info].QUAL
WHERE Nz([INDIVIDUALS Starter Info].STATUS,"3")= "3"
GROUP BY QUAL.[PCR Description];

Duane
Hook'D on Access
MS Access MVP

 

[DAmoss]

dhookom:

Unfortunately that doesn't work either! I think my problem is the [STATUS] = "3", this is correctly filtering out the unwanted rocords, but it's also filtering out my linked table list as well!

Anyone else got any other suggestions? I'm beginning to think I should create a Pivot table instead.

 

[dhookom]

Try this which should be the same. These assume GEND and STATUS are both text fields. If they aren't you must remove the quotes.

SELECT Sum(Abs([GEND]="1")) AS Male, 
Sum(Abs([GEND]="2")) AS Female, QUAL.[PCR Description]
FROM QUAL LEFT JOIN [INDIVIDUALS Starter Info] ON QUAL.Value = [INDIVIDUALS Starter Info].QUAL
WHERE [INDIVIDUALS Starter Info].STATUS= "3" Or 
[INDIVIDUALS Starter Info].STATUS Is Null
GROUP BY QUAL.[PCR Description];

Duane
Hook'D on Access
MS Access MVP

 

[DAmoss]

dhookom:

Thanks for your efforts, but it's still not working.

I managed to get a pivotable working which showed all the correct linked table descriptions with the correct values and blanks where there was no value.

I just can't seem to do the same in a standard query. i think I might have to resort to programming to get what I want in a report.

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DAmoss]

PHV,

Unfortunately, pointing me to an article about how to create SQL joins doesn't actually help, I have tried all the various combinations but no avail.

If you look at my previous examples you will see that I've tried LEFt JOIN which does indeed bring in the whole linked table. But when I have expressions in there the whole answer list restricts to just the ones whose STATUS = 3, the NULL's do not show, that is my problem!

Have you looked at my SQL code above and have you spotted where I've gone wrong or will it just not work, that is what i'm asking?

Cheers

 

[dhookom]

In my experience if you allow for Nulls as per my suggestion, you will get all records on the "LEFT" side.

Duane
Hook'D on Access
MS Access MVP

 

[DAmoss]

dhookom:

I think we are at cross purposes here, none of my actual records containing the field (STATUS] have any null values in them. It is the linked table with all my possible Statuses that I want completeley listed with any totals pulled from my main table.

As I saId previously I tried your code changes and it didn't work.

Thank you for your time.

 

[dhookom]

show all of my QUAL.[PCR Descriptions]

It is the linked table with all my possible Statuses that I want completeley listed

We were attempting to answer the first specification. It looks like you have changed this at some point.

Duane
Hook'D on Access
MS Access MVP

 

[DAmoss]

Sorry, but the specification has never changed as per my original opening line ...

I'm trying to get my query to show all of my QUAL.[PCR Descriptions] that are contained within a linked table

but regardless ... I have now solved my own problem, here is the (rough) solution that actually works, I just need to change the query names to something better:

SELECT QUAL.[PCR Description], Query1.Male, Query1.Female
FROM Query1 RIGHT JOIN QUAL ON Query1.[PCR Description] = QUAL.[PCR Description]
GROUP BY QUAL.[PCR Description], Query1.Male, Query1.Female, Query1.[PCR Description];

The solution was to create a query with my answers in and then add to another query which showed all my linked table descriptions ... jobs a goodun!

dhookom:
Thanks for your help, you made me rethink my problem again and again without giving up!

 

[DAmoss]

Here is my final solution that i am now using:

I created the following sub-query containing just the required field values.

PCR Qst 4-8-3 Economically inactive sub list
SELECT QUAL.[PCR Description], StartersLeavers.GEND
FROM QUAL LEFT JOIN StartersLeavers ON QUAL.Value = StartersLeavers.QUAL
GROUP BY QUAL.[PCR Description], StartersLeavers.GEND, StartersLeavers.STATUS
HAVING (((StartersLeavers.STATUS)="3"));

I then included the above sub-query into another query connecting them both via the [PCR description]:

PCR Qst 4-8-3 Economically inactive full list
SELECT QUAL.[PCR Description], Sum(IIf([GEND]="1",1,0)) AS Male, Sum(IIf([GEND]="2",1,0)) AS Female
FROM [PCR Qst 4-8-3 Economically inactive sub list] RIGHT JOIN QUAL ON [PCR Qst 4-8-3 Economically inactive sub list].[PCR Description] = QUAL.[PCR Description]
GROUP BY QUAL.[PCR Description];

This gives me a final solution that includes all my linked table descriptions and it also puts zero's in the blank values as well.

Cheers!