Correlated SubQueries in Access

[News.Microsoft.com]

I know that Access does not support correlated subqueries so how can I make
this work?

update (emplsite inner join member on emplsite.employerno =
member.employerno
and emplsite.jobsite = member.jobsite) set emplsite.nummembers = (select
count(socsecno) from member )

Thanks
Bill

 

[John Vinson]

I know that Access does not support correlated subqueries so how can I make
this work?

And what makes you think Access doesn't support correlated
subqueries!? I use them often and they work fine. What you CAN'T do is
use a Count or any other Totals operator in an update query - even if,
as in this case, it should logically do so.

If you want to (redundantly, erroneously, and unwisely) store the
count of members sharing the employerno and jobsite into the
nummembers field, try using the DCount() function:

UPDATE Emplsite
SET emplsite.nummembers =
DCount("[socsecno]", "member",
"[employerno] = " & [employerno]
& " AND [jobsite] = '" & [jobsite] & "'")

Assuming that employerno is Number and jobsite is Text.

John W. Vinson[MVP]