jet sql access subquery in from clause left outer join does not work ... help!!

[media.opslag]

Hi,

How can i get this to work in access / jet sql ??? Someone??

SELECT
tbl1.[field],
tbl2.[otherfield]

FROM [Table1] tbl1
left outer join [Table2] tbl2 on
tbl2.[id1] = tbl1.[id1] and
tbl2.[id2] = tbl1.[id2] and
tbl2.[somefield] =
(
SELECT MAX([somefield])
FROM [Table2] tbl2_temp
WHERE tbl2_temp.[id1] = tbl1.[id1] and
tbl2_temp.[id2] = tbl1.[id2]
)

It seems access doesn't have the possibility to put subqueries in from
clauses - left outer joins...

Thanx!
Gene.

 

[Tom Wickerath]

Is this what you might be looking for?
Note: Field is a reserved word, so I used "Field1" instead:


SELECT
tbl1.[field1],
tbl2.OtherField
FROM Table1 AS tbl1
LEFT JOIN Table2 AS tbl2 ON
(tbl1.id2 = tbl2.id2) AND
(tbl1.id1 = tbl2.id1)
WHERE (tbl2.SomeField)=
(SELECT MAX([somefield])
FROM [Table2] tbl2_temp
WHERE tbl2_temp.[id1] = tbl1.[id1] and
tbl2_temp.[id2] = tbl1.[id2]
)



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[TC]

Hard to say, unless you define what you mean by "not working" .

Is it returning no records? Too few records? Too many records? Is it
getting a runtime error? Causing Access to hang? ... (etc.)

TC (MVP Access)
htp://tc2.atspace.com