Syntax error in from clause

[Vispon]

Hi Guys,

I've recently returned to access (having not used it for a few years) and am stuck on how to fix this syntax error. My SQL ability's quite limited so a friend sent me over the query I needed but it was written for postgres not access. I've made a couple of updates but whenever I run it I see 'Syntax error in FROM clause'.

Any help would be most appreciated

select rh_pk, rh_company, rh_addr1, rh_pcode, ploc.loc_pcode, cnt_name, cnt_status, cnt_expires, cnt_addr,
concatenate(locs.loc_pcode + ' : ' + locs.loc_vehicles + ', ' + locs.loc_trailers) as depots, cnt_pk, cnt_licence, cnt_postcode
from public_rh_contacts 
join public_vo_locations ploc on rh_pcode = ploc.loc_pcode
join public_vo_contacts on (rh_pcode = cnt_postcode and loc_pcode is null) or loc_cntfk = cnt_pk
join public_vo_locations locs on cnt_pk = locs.loc_cntfk
where rh_pcode like 'RG2 0%'
group by rh_pk, rh_company, rh_addr1, rh_pcode, ploc.loc_pcode, cnt_name, cnt_status, cnt_expires, cnt_addr, cnt_pk, cnt_licence, cnt_postcode
order by rh_pk, cnt_name

 

[vbaInet]

Let's see the sql your friend wrote.

 

[Vispon]

In honesty I only actually changed the concatinate action. The original SQL (that works in pgAdmin) is as follows.

select rh_pk, rh_company, rh_addr1, rh_pcode, ploc.loc_pcode, cnt_name, cnt_status, cnt_expires, cnt_addr,
concatenate(locs.loc_pcode || ' : ' || locs.loc_vehicles || ', ' || locs.loc_trailers) as depots, cnt_pk, cnt_licence, cnt_postcode
from public_rh_contacts 
join public_vo_locations ploc on rh_pcode = ploc.loc_pcode
join public_vo_contacts on (rh_pcode = cnt_postcode and loc_pcode is null) or loc_cntfk = cnt_pk
join public_vo_locations locs on cnt_pk = locs.loc_cntfk
where rh_pcode like 'RG2 0%'
group by rh_pk, rh_company, rh_addr1, rh_pcode, ploc.loc_pcode, cnt_name, cnt_status, cnt_expires, cnt_addr, cnt_pk, cnt_licence, cnt_postcode
order by rh_pk, cnt_name

 

[vbaInet]

Try this:

select rh_pk, rh_company, rh_addr1, rh_pcode, ploc.loc_pcode, cnt_name, cnt_status, cnt_expires, cnt_addr,
    locs.loc_pcode & " : " & locs.loc_vehicles & ", " & locs.loc_trailers as depots, cnt_pk, cnt_licence, cnt_postcode
from public_rh_contacts 
    INNER join public_vo_locations ploc on rh_pcode = ploc.loc_pcode
    INNER join public_vo_contacts on (rh_pcode = cnt_postcode and loc_pcode is null) or loc_cntfk = cnt_pk
    INNER join public_vo_locations locs on cnt_pk = locs.loc_cntfk
where rh_pcode like 'RG2 0*'
group by rh_pk, rh_company, rh_addr1, rh_pcode, ploc.loc_pcode, cnt_name, cnt_status, cnt_expires, cnt_addr, cnt_pk, cnt_licence, cnt_postcode
order by rh_pk, cnt_name

 

[Vispon]

Thanks for the reply VBA, that's got me somewhere but now I'm encountering a new error message:

Syntax error (missing operator) in query expression 'rh_pcode = ploc.loc_pcode
INNER join public_vo_contacts on (rh_pcode = cnt_postcod'.

 

[vbaInet]

Can you tell me what tables these fields belong to?
rh_pcode
cnt_postcode
loc_pcode
loc_cntfk
cnt_pk

It might just be easier if you explain what you're trying to do.