Join does not appear to be working

[mindbender]

I am running the following query and I am expecting all of the values from tbl_dispo to be returned and any matching records from tbl_main. I know there is a value of DEMO in the tbl_dispo and that tbl_main has no records that contain the disposition of Demo based on the criteria of Price_Plan_Group = Term Lease. I would like demo to be listed in the results crosstab even though there are no associated records.

This query I thought would work but it is not returning the demo field in the crosstab results....any ideas?

TRANSFORM Count(tbl_main.EXP_Q) AS CountOfEXP_Q
SELECT tbl_main.REP_NAME


FROM tbl_dispo LEFT JOIN tbl_main ON tbl_dispo.Disposition = tbl_main.Disposition
WHERE (((tbl_main.PRICE_PLAN_GROUP)="TERM LEASE") AND ((tbl_main.EXP_Q)="2") AND ((tbl_main.Disposition) Is Not Null) AND ((tbl_main.REP_NAME) Not Like "*UNASSIGNED*"))
GROUP BY tbl_main.REP_NAME
PIVOT tbl_dispo.Disposition;

[CJ_London]

if tbl_main.EXP_Q is a numeric datatype the you don't have quotes round the 2 - but I'm guessing this has been built in the query builder and it is actually text.

you appear to be excluding demo with this part of your where statement ((tbl_main.Disposition) Is Not Null)

[Cottonshirt]

I think you will find that this problem occurs where you have a join on a field that may contain nulls.

because Null does not have a value ACCESS cannot compare nulls, it cannot even determine whether one Null equals another Null. therefore, when your join field contains Nulls at either end ACCESS cannot determine whether these fields comply with whatever your join criteria are, and skips them.

one solution would be to run a query that extracts from tbl_main only those records where [disposition] is not Null, and then use that query as the recordsource for your main query.


good luck with your project,



Cottonshirt

[mindbender]

I think you will find that this problem occurs where you have a join on a field that may contain nulls.

because Null does not have a value ACCESS cannot compare nulls, it cannot even determine whether one Null equals another Null. therefore, when your join field contains Nulls at either end ACCESS cannot determine whether these fields comply with whatever your join criteria are, and skips them.

one solution would be to run a query that extracts from tbl_main only those records where [disposition] is not Null, and then use that query as the recordsource for your main query.


good luck with your project,



Cottonshirt

I created a query NOT NULL DISPOSITIONS and used that to join to the dispositions table but I still do not get the value of Demo returned in the results.

Here is the new query

SELECT [NOT NULL DISPOSITIONS].REP_NAME, [NOT NULL DISPOSITIONS].Disposition, Count([NOT NULL DISPOSITIONS].EXP_Q) AS CountOfEXP_QFROM tbl_dispo LEFT JOIN [NOT NULL DISPOSITIONS] ON tbl_dispo.Disposition = [NOT NULL DISPOSITIONS].Disposition
WHERE ((([NOT NULL DISPOSITIONS].PRICE_PLAN_GROUP)="TERM LEASE") AND (([NOT NULL DISPOSITIONS].EXP_Q)="2"))
GROUP BY [NOT NULL DISPOSITIONS].REP_NAME, [NOT NULL DISPOSITIONS].Disposition;

[Cottonshirt]

as Ajax told you in the second post, if the field [NOT NULL DISPOSITIONS].EXP_Q is a number field, you do not need to put quotes round it. you would need AND (([NOT NULL DISPOSITIONS].EXP_Q)=2))


Good luck with your project,



Cottonshirt

[mindbender]

as Ajax told you in the second post, if the field [NOT NULL DISPOSITIONS].EXP_Q is a number field, you do not need to put quotes round it. you would need AND (([NOT NULL DISPOSITIONS].EXP_Q)=2))


Good luck with your project,



Cottonshirt

I have done that but still no luck. I have done these many times before without issue but for some reason this one won't work.

Maybe if I trying explaining it again in case I missed something the first time.

I have 1 table, tbl_dispositions with 11 values and a query NOT NULL DISPOSITIONS that contains only the records from tbl_main where the disposition is not null. For EXP_Q field of 2 and PRICE_PLAN_GROUP of "Term Lease", only 10 of the values from tbl_dispo were used but I would like all 11 values to be returned in the crosstab results. I created a join between the two and selected return all records from tbl_dispo and only those from NOT_NULL_DISPOSITIONS that match.

When i run it I do not see the 11th value (Demo) in the results.

I really appreciate your help

TRANSFORM Count([NOT NULL DISPOSITIONS].EXP_Q) AS CountOfEXP_Q
SELECT [NOT NULL DISPOSITIONS].REP_NAME
FROM tbl_dispo LEFT JOIN [NOT NULL DISPOSITIONS] ON tbl_dispo.Disposition = [NOT NULL DISPOSITIONS].Disposition
WHERE ((([NOT NULL DISPOSITIONS].PRICE_PLAN_GROUP)="TERM LEASE") AND (([NOT NULL DISPOSITIONS].EXP_Q)=2))
GROUP BY [NOT NULL DISPOSITIONS].REP_NAME
PIVOT tbl_dispo.Disposition;

[CJ_London]

Maybe if I trying explaining it again in case I missed something the first time.

explanations only go so far - we understand the problem you are having so explaining again does not help.

1. Provide some example data
2. the outcome you expect to get from that example data
3. and the outcome you are actually getting.

suggest simplify your query to a simple select query as your example data

It doesn't help that your table names have spaces and use reserved words (Not and Null). Square brackets usually solve the problem but not always

[mindbender]

explanations only go so far - we understand the problem you are having so explaining again does not help.

1. Provide some example data
2. the outcome you expect to get from that example data
3. and the outcome you are actually getting.

suggest simplify your query to a simple select query as your example data

It doesn't help that your table names have spaces and use reserved words (Not and Null). Square brackets usually solve the problem but not always

1. sample db attached
2 I would like to see 11 rows retuned one being the disposDatabase1.accdbition of Demo with a 0 for it
3 I am only seeing the 10 values in the main table and not all of the potential values in the tbl_dispo
I made the not null query quickly and have since renamed it NN_DISPOSITIONS

[pbaldy]

Can't look at the sample right now, but the problem is you've got a left join but the criteria is on the right side table. That negates the join. Can you move the criteria to a query and use that for the right side instead of the table?

[mindbender]

Can't look at the sample right now, but the problem is you've got a left join but the criteria is on the right side table. That negates the join. Can you move the criteria to a query and use that for the right side instead of the table?

Thank you! That worked.
I appreciate all of your time.

[pbaldy]

Happy to help!

I probably should have added "left" to That negates the join. It's probably more accurate to say it changes the left join to an inner join.

[CJ_London]

I pointed that out in post #2