What is the Access SQL for a subquery?

[BigAndy]

What is the Access SQL for a subquery that counts records from another table?

In SQL Server the following statemant works fine, can anyone help me make this work from an Access SQL statement?

SELECT U.Name, NumOfItems = (SELECT Count(*) FROM Item I WHERE I.UserIndex = U.UserIndex)
FROM User U

Thanks
Andy

 

[Psychpt]

TRY:

SELECT U.Name, NumOfItems AS (SELECT Count(*) FROM Item I WHERE I.UserIndex = U.UserIndex)
FROM User U

HTH

PsychPt@Hotmail.com

 

[Psychpt]

sorry, that was backwards

SELECT U.Name, (SELECT Count(*) FROM Item I WHERE I.UserIndex = U.UserIndex) AS NumOfItems
FROM User U

PsychPt@Hotmail.com