All

I have an Excel spreadsheet (2007) with some data over different tabs that needs to be queries with an ADODB-recordset. My problem is that the query needs an "inner join" and a "right join" at same time.

Very simplistic example of what I need
The first tab is called Outages and contains an overview of different outages at different locations.
The header column has fields: ID, Site, OutageType_id, TimeInSeconds, Datestamp, Log_id

The second tab is called OutageTypes wich contain an overview of all possible outages.
Fields are: ID, OutageType

The third tab is called Log and contains some text from a log.
Fields are: ID, Log

I now need a SQL-string where OutageTypes!ID got an outer join with rsOutages!OutageType_id AND where Outages!log_id got an inner join with Log!ID together with a SUM-function on the "TimeInSeconds"-field for each site.

So at the end: I need an overview of all Outagetypes (whether they have happened or not) together with the overview of outages that did happen. If I could have used Access, I had to create two seperate queries which would give next result
Expr1000 Type Site TotalSeconds Datestamp Log
Cable failure Cable failure B 10 22/03/2012 Unknown failure
Cable failure Cable failure B 25 12/03/2012 A duplicate message has generated
Cable failure Cable failure A 60 28/03/2012 System error on machine A
Cable failure Cable failure A 40 27/03/2012 Error 1025 on machine
Router failure Router failure A 10 22/03/2012 Not Accessible
Outlet failure Outlet failure
NIC failure NIC failure
Other failure Other failure .

In my Excel VBA-code, I started with something like:
strsql = "SELECT O.Site, Sum(O.TimeInSeconds) AS TotalSeconds, O.Datestamp, L.Log, L.OutageType_id " & _
"FROM tblLog as L INNER JOIN tblOutages as O ON L.ID = O.Log_ID " & _
" GROUP BY O.Site, O.Datestamp, L.Log, O.OutageType_id;"
set rsLogs = new adodb.recordset
rslogs.open strsql,cn,db

Now I should create a rsOutagesTypes-recordset which uses the rsLogs-recordset to make the outer join, but it seems you can't use an existing recordset in the SQL-string.

So, is there any solution? Or is there a way to put this into 1 big SQL-query?

Regards
Ino