bug in a left join in Access 2013 1

[PizMac]

I have simplified my problem to the basics - 2 queries from a simple table - here is the table:-
Input
ID ctry
1821 China
1822
1823 China

query 1: (IM)
SELECT Input.ID, Input.Ctry, 6 AS Pts
FROM [Input]
WHERE (((Input.[Ctry]) Is Not Null));
result:
ID Ctry Pts
1821 China 6
1823 China 6

query 2 (Input+IM)
SELECT IM.Pts, Input.ID, IM.ID
FROM [Input] LEFT JOIN IM ON Input.ID = IM.ID;
result:
Pts Input.ID IM.ID
6 1821 1821
6 1822
6 1823 1823

In Access 2003 the Pts field on record 1822 was null (correct) but on 2013 it is 6 - INCORRECT !!!!!!!! - I have recoded my system to work around this but has anyone else come across this - it is surely A BUG !!! Microsoft have passed me from pillar to post and ended up saying I need to pay for support - not impressed after 2 hours on the phone!

 

[combo]

Same rule in access 2016. Fixed by adding grouping (sums in visual designer) in query 1 (IM):
[pre]SELECT Input.ID, Input.Ctry, 6 AS Pts
FROM [Input]
GROUP BY Input.ID, Input.Ctry, 6
HAVING (((Input.Ctry) Is Not Null));[/pre]

"6" in GROUP BY is the expression for [Pts].

combo

 

[PizMac]

thanks Combo - much neater than my solution which involves an extra query etc.. - just a bit worried that this bug might be elsewhere in my systems as well - and you don't know without re-testing all the bits and bobs

 

[dhookom]

I have raised this issue with fellow MS Access MVPs and expect it will get escalated. I'm not sure there will be any response in the near future but at least it will be documented.

Thanks for posting.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

I checked this topic in access 2003 environment, in my case the query result is exactly the same as in 2013-2016 versions, i.e. Pts for record with ID=1822 is null if IM query has GROUP BY clause, 6 if WHERE. Strange.

combo

 

[PizMac]

I also checked my original in 2003 and it was correct (i.e. null)