INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

bug in a left join in Access 2013

bug in a left join in Access 2013

(OP)
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!

RE: bug in a left join in Access 2013

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

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

combo

RE: bug in a left join in Access 2013

(OP)
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

RE: bug in a left join in Access 2013

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

RE: bug in a left join in Access 2013

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

RE: bug in a left join in Access 2013

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close