Error 3612: Multi-level GROUP BY clause is not allowed in subquery

[Ronin]

Group i have this problem with this error, but i did not specify any grouping
in the subquery and i did try to specify one grouping in each where criteria
and still gives same error.


TRANSFORM First(tblType.Type) AS FirstOfType
SELECT tblMovies.MovieID, tblMovies.Title, tblMovies.Description, (SELECT
LName & ", " & FName AS Member FROM tblMember WHERE MemberID=(SELECT MemberID
FROM tblBarrowed WHERE MovieID=[MovieID]) AND isnull((SELECT DateReturned
FROM tblBarrowed WHERE MovieID=[MovieID]))) AS BarrowedBy
FROM (((tblMovies LEFT JOIN tblCatList ON tblMovies.MovieID =
tblCatList.MovieID) LEFT JOIN tblType ON tblCatList.TypeID = tblType.TypeID)
LEFT JOIN tblBarrowed ON tblMovies.MovieID = tblBarrowed.MovieID) LEFT JOIN
tblMember ON tblBarrowed.MemberID = tblMember.MemberID
GROUP BY tblMovies.MovieID, tblMovies.Title, tblMovies.Description
PIVOT tblType.TypeID;


Please help. I do not want to create a query within a query, but only one
query


Ronin

 

[aaron.kempf]

i would reccomend using Access Data Projects for queries that are this
complex.

ADP queries dont just 'crap out on you' like MDB queries do.

-Aaron

 

[Ronin]

Not trying to offend anyone... but doesn't ADP use a SQL Server database? i
just want a plain access database, but using VB 6 to connect using ado 2.6,
but i don't want to use accesss' built in queries.. i'm writing sql
statements in the vb 6 module interface.

 

[Ronin]

Group,

Thanks for any help, but i found a work around.

still using one select statement.

 

[Ronin]

oh... and no subquery