1

Currently debugging a piece of code to fit into an Access SQL query.

Code is as follows:

select tk, tk2, count(*), count(a2.tk) / count(*) as ratio
from ((select distinct a.TK, a2.TK as tk2
       from access3 as a join
            access3 as a2 
            on (a.DocumentNo = a2.DocumentNo)
       where a.TK <> a2.TK
      ) tt left join
      access3 as a
      on (a.tk = tt.tk)
     ) left join
     access3 as a2
     on (a2.tk = tt.tk2 and a2.DocumentNo = a.DocumentNo)
group by tk, tk2;

I looked it up and found this post

"join expression not supported" in Access

So I tried putting in the parentheses. When I hit run, it takes me to line

(a2.tk = tt.tk2 and a2.DocumentNo = a.DocumentNo)

And highlights it. So something about this line it doesn't like.

I think I understand how to rewrite the join code, but how do you rewrite the left join code to satisfy Access's picky requirements?

Or, is there a better program to use where I can just connect to Access and copy and paste the code in? I tried the free version of DBeaver but I assume it's too much to handle as it brings back 200 lines of query (That are wrong) that took 5 minutes to load, and when I scroll down it re-queries and pulls in more data that is also wrong.

Tried a sample size in DB Fiddle and it works perfectly, so the code works, just DBeaver is not doing it properly for some reason.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
bohzwah
  • 77
  • 7
  • Despite the fact that it highlighted a particular line, any time I see nested queries with errors in Access, it is ALWAYS a good idea to run each nested query separately to ensure they run independently as you expect before debugging other errors. Understanding how to code is aside from the point... since Access often has stupid bugs that are difficult to find and even more difficult in nested queries. (By "bug" I mean that Access often enforces very particular requirements about parenthesis, etc. that it should be able to interpret but doesn't.) – C Perkins Aug 09 '19 at 22:55

1 Answers1

2

Change join to inner join - MS Access cannot interpret join automatically as an inner join.

You may also need table qualifiers prefixing your fields tk & tk2, as there appears to be multiple fields with these names across the tables you are referencing:

e.g. select tt.tk, tt.tk2, count(*), count(a2.tk) / count(*) as ratio

Lee Mac
  • 15,615
  • 6
  • 32
  • 80