0

I am developing an Employee Management System and here I need to make a JOIN on judges and postings tables but the MS Access says "JOIN Expression not supported". I can not understand where is error.

Under is my Full Query.

SELECT [a.emp_name], [b.des_name], FORMAT([c.dte_frm], 'dd.MM.yyyy') AS
dte_frm, iif([d.crt_nat] = 'court', [e.jdg_name]&', '&[f.dsg_name],
[d.crt_name]&' '&[d.crt_loc]) AS location, [a.emp_teh] AS tehsil,
[a.remarks] AS remarks
FROM employees a, designation b, courts d, postings c
LEFT JOIN judges e ON [c].crt_id = [e].crt_id
WHERE [c.des_id] = [b.des_id]
AND [a.emp_cod] = [c.emp_id]
AND [c.crt_id] = [d.crt_id]
AND [c.to_dte] = 1
AND [c.other_dist] = 0
ORDER BY [d.crt_sort], [a.emp_bps] DESC , [b.des_name] ASC

Please help me correcting this.

shandogar
  • 1
  • 1
    You are mixing two styles of `JOIN` together, which by itself is bad. You should use explicit joins everywhere IMO. Also, it is not clear what the relationship is for joining the `employees` and `designation` tables. It appears that they are being cross joined at the moment. – Tim Biegeleisen Mar 24 '16 at 05:20
  • 1
    Access has crazy way of joining with parenthesis and such. Try putting this in parenthesis: `(postings c LEFT JOIN judges e ON [c].crt_id = [e].crt_id)` – cha Mar 24 '16 at 05:22
  • 1
    When you use old style joins (i.e. add all tables in FROM clause and then join them in where clause) which here is referring as INNER JOIN. Try to use same style of joins as mentioned by @TimBiegeleisen. Also check [this post](http://stackoverflow.com/a/28685893/4879022) for explanation on conflict of INNER JOIN AND LEFT/RIGHT JOINS. – Techie Mar 24 '16 at 05:34

0 Answers0