asked on

Using Sub-Query's

I have query that used a sub query written in access...

ive extracted the sql code of the sub query and made an ADO recordset...

I could use a temporary table, but this isnt the most efficient way. How do i use this recordset in the main query?

Thanks in advance

TimCottee

You should look at the SHAPE syntax for a hierarchical way to access data. Have a look here for a starter: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefmsdatashape.asp

ASKER CERTIFIED SOLUTION

PaulHews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

djdidge

ASKER

hi...ive just got back from holiday so im taking a look now ;o)

djdidge

ASKER

The original ACCESS QUERY that uses a sub query
"Students live with no future bookings"

SELECT STUDENTS.S_REF, STUDENTS.S_SURNAME
FROM STUDENTS LEFT JOIN [students with future bookings] ON STUDENTS.S_REF = [students with future bookings].S_REF
WHERE ((([students with future bookings].S_REF) Is Null))
GROUP BY STUDENTS.S_REF, STUDENTS.S_SURNAME;

"Students with future bookings"

SELECT STUDENTS.S_REF
FROM (BOOKINGS INNER JOIN SESSIONS ON BOOKINGS.B_SESS_ID = SESSIONS.SESS_ID) INNER JOIN STUDENTS ON BOOKINGS.B_S_REF = STUDENTS.S_REF
WHERE (((SESSIONS.SESS_DATE)>Now()))
GROUP BY STUDENTS.S_REF, STUDENTS.S_STATUS
HAVING (((STUDENTS.S_STATUS)="L"));

The query "Students with future bookings" is used not only in the where but also in the left join. Im not sure how to tackle it.

i_jester69

Just replace the name of the query

SELECT * FROM Table1 LEFT JOIN Query1
ON Yadda yadda
WHERE Query1.Field = 'FF'

With this

SELECT * FROM Table1 LEFT JOIN
(
SQL FOR Query1
) AS Query1
ON Yadda Yadd
WHERE Query1.Field = 'FF'