oracleblue
New member
- Local time
- Today, 01:26
- Joined
- Mar 6, 2012
- Messages
- 2
Hi - I am helping a customer do this and since I've not used Access in years, I'm having to adjust to the differences between other tools and Access.
Background: I have two ODBC link table data sources that I am trying to tie together with a table I've created to drive the selection.
1. SITES - created table with site numbers and assets being requested in the report
2. ASSETS - ODBC link table source with keys of SITE and ASSET. It also contains a column WORKID. There will be a selection of other reporting columns but at this point they are not relevant.
3. DESCRIPTS - ODBC link table source with key of WORKID (tied to ASSETS).
In building the query in the design view, I cannot make it work because it tells me I can't do it without creating a query joining two tables and using that query as one of the new query data sources. I had to use the SITES and ASSETS tables to build that query and use it as part of the 2nd query with DESCRIPTS. I make the necessary joins and it returns an error message from the ODBC data source saying it's an invalid query. OK. I get that sort of.
My next query is a pure SQL query that I developed in DBVisualizer and it works perfectly returning the expected number or rows and all the corresponding data. That query is:
select site,
asset,
wonum,
statusdate,
description,
workid,
descriptionid,
longtext
from assets
left outer join descripts on (workid = workid and owner='WORKORDER' AND OWNERCOL='DESCRIPTION')
where (site in (select distinct site from sites) and asset in (select distinct asset from sites))
When running the query in Access 2010, it comes back with an error message "Join expression not supported" and highlights the "workid=workid and owner='WORKORDER' and OWNERCOL='DESCRIPTION'".
Is this too complex for Access? Is there a way to make this work?
Thanks in advance.
Background: I have two ODBC link table data sources that I am trying to tie together with a table I've created to drive the selection.
1. SITES - created table with site numbers and assets being requested in the report
2. ASSETS - ODBC link table source with keys of SITE and ASSET. It also contains a column WORKID. There will be a selection of other reporting columns but at this point they are not relevant.
3. DESCRIPTS - ODBC link table source with key of WORKID (tied to ASSETS).
In building the query in the design view, I cannot make it work because it tells me I can't do it without creating a query joining two tables and using that query as one of the new query data sources. I had to use the SITES and ASSETS tables to build that query and use it as part of the 2nd query with DESCRIPTS. I make the necessary joins and it returns an error message from the ODBC data source saying it's an invalid query. OK. I get that sort of.
My next query is a pure SQL query that I developed in DBVisualizer and it works perfectly returning the expected number or rows and all the corresponding data. That query is:
select site,
asset,
wonum,
statusdate,
description,
workid,
descriptionid,
longtext
from assets
left outer join descripts on (workid = workid and owner='WORKORDER' AND OWNERCOL='DESCRIPTION')
where (site in (select distinct site from sites) and asset in (select distinct asset from sites))
When running the query in Access 2010, it comes back with an error message "Join expression not supported" and highlights the "workid=workid and owner='WORKORDER' and OWNERCOL='DESCRIPTION'".
Is this too complex for Access? Is there a way to make this work?
Thanks in advance.