Complex Query

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.
 
I don't believe you can filter in a JOIN like that in access. You'd have to create a sub query filtering on Owner and OwnerCol first and join that query.
 
I don't believe you can filter in a JOIN like that in access. You'd have to create a sub query filtering on Owner and OwnerCol first and join that query.

I had made the assumption this would be the answer. It seems a great deal more work but I'll put in the effort. As an additional comment, I actually developed the query in both DBVisualizer and OpenOffice Database and it runs to completion in both. It just seems to be an implementation within Access impeding the execution.

Thanks
 
Access has some weird and arbitrary seeming preferences for queries generally, its bracketing rules will, eventually, drive you insane and there seems to be no set rule on when you can use inline SQL (sub) querying and when you have to farm something out to its own Query.
 
You normally filter an OUTER JOIN of two tables with an IN statement, but you can also do the filtering with a WHERE Statement. Something like this might be a little slow, but could work.
Code:
[B]SELECT[/B] site, asset, wonum, statusdate, description, workid, descriptionid, longtext
[B]FROM[/B] assets left outer join descripts
[B]WHERE[/B] ((workid = workid and owner='WORKORDER' AND OWNERCOL='DESCRIPTION')) AND 
    (site in (select distinct site from sites) and asset in (select distinct asset from sites))
 
I have a similar query problem I can't figure out. If anyone can help me with this I will be so greateful.
I am using Access 2003.

I have one table and the following fields are the ones I need:
StaffFac
SerCode
StaffTime

Basically I need the StaffFac = 62 or 6215 And SerCode 90000 or 90001 to equal 0 else give me StaffTime.

I also need Where SerCode = 90002 give me 0 else give me StaffTime to be inculded with the first statement. I have this one working.

I've tried several different IIf statements and actually I got the second one working it is the first one that is giving me headaches. I am trying to formulate this in a query that I actually need on a report.

If anyone can help me out with this I will appreciate it.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom