Translate SQL query

[Ray S.]

I have a SQL query that I need to translate so that MS Access can understand
and run it. The query works fine. It gives the desired results, but I can't
use it in MS Access at all. I keep getting a bunch of errors as I try to
"translate" it into something that Access will understand. Here's the query.
I'd appreciate if anyone can tell me why Access won't run it and what has to
be changed so that it will. For example, I know that || ' ' || is not
recognizable in Access - it must be changed to &""&. Also, I know that
something like cc.description cc_desc must be "translated" into
cc.description AS cc_desc. Can anyone help me with this? It's pretty complex,
and I really can't give you the tables (they're all named here).

I'm especially having trouble with the sub-joins:

SELECT cc.cc_nbr,
cc.description cc_desc,
fa.description functional_area,
m.description serv_type_2008,
e1.first_name || ' ' || e1.last_name cc_mgr,
e2.first_name || ' ' || e2.last_name cc_mgr_mgr
FROM ibc_cost_center cc
INNER JOIN ibc_cc_manager cm ON cc.id = cm.cost_center_id
INNER JOIN ibc_employee e1 ON e1.id = cm.employee_id
LEFT JOIN ibc_job_position jp1 ON jp1.id = e1.job_position_id
LEFT JOIN ibc_job_position jp2 ON jp2.id = jp1.manager_job_position_id
LEFT JOIN ibc_employee e2 ON jp2.id = e2.job_position_id
LEFT JOIN ibc_functional_area fa ON fa.id = cc.functional_area_id
LEFT JOIN
(SELECT st.description, cc2.id cost_center_id
FROM ibc_service_type_cc stcc
inner join ibc_cost_center cc2 ON cc2.id = stcc.cost_center_id
inner join ibc_service_type st ON st.id= stcc.service_type_id
inner join ibc_fiscal_year fy ON fy.id = st.fiscal_year_id
WHERE fy.year = 2008 ) m ON cc.id = m.cost_center_id
ORDER BY cc.cc_nbr

 

[mscertified]

Divide and conquer!
Start with just the SELECT and inner joins, get that working, then add the
left joins one at a time until you get the whole thing working.
Make the last subselect a separate query.

-Dorian

 

[Sylvain Lafontaine]

Last time I've checked, it was very hard to build an Access query with
multiple Left joins. If I remember correctly, you have to put them in a
particular order and surround them with parenthesis. You might also be
obligated to put some of them under another query and use this query as a
View. With complex queries in Access, it's also a good idea to always put
the semi-comma at the end of the query; otherwise you might get a syntax
error. If I were you, I would use the graphical query designer to build
this query correctly but I won't be surprised if you are not capable of
translating this query to Access: the JET engine is very weak in comparaison
to SQL-Server when it comes to multiples Left Join and sub-queries.

Finally, if you are accessing a SQL-Server through ODBC linked tables, then
instead of translating this query, you should use a passthrough query
instead.

 

[John Spencer]

PERHAPS the following will workSELECT cc.cc_nbr,
cc.description as cc_desc,
fa.description as functional_area,
m.description as serv_type_2008,
e1.first_name & ' ' & e1.last_name as cc_mgr,
e2.first_name & ' ' & e2.last_name as cc_mgr_mgr
FROM (((((((ibc_cost_center as cc
INNER JOIN ibc_cc_manager cm ON cc.id = cm.cost_center_id)
INNER JOIN ibc_employee e1 ON e1.id = cm.employee_id)
LEFT JOIN ibc_job_position jp1 ON jp1.id = e1.job_position_id)
LEFT JOIN ibc_job_position jp2 ON jp2.id = jp1.manager_job_position_id)
LEFT JOIN ibc_employee e2 ON jp2.id = e2.job_position_id)
LEFT JOIN ibc_functional_area fa ON fa.id = cc.functional_area_id)
LEFT JOIN
(SELECT st.description, cc2.id cost_center_id
FROM (((ibc_service_type_cc stcc
inner join ibc_cost_center cc2 ON cc2.id = stcc.cost_center_id)
inner join ibc_service_type st ON st.id= stcc.service_type_id)
inner join ibc_fiscal_year fy ON fy.id = st.fiscal_year_id)
WHERE fy.year = 2008 ) m ON cc.id = m.cost_center_id)
ORDER BY cc.cc_nbr


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================