How can 8 fields be too many in a query??

[Lisa B]

I have created a query [CC final form] that should put
together/build fields [SocEm], [CogDev] and [PysDev] from
4 different queries. When I attemp to run the query, I get
the error "3190 -too many fields defined". I only have 8
fields defined... The query works if I leave out any one
of the 3 nested IIF statements... but I need all three so
I can group all results by [CC Coded].CNTRNAME and [CC
Coded].ROOM.

Any ideas or suggestions?
Thanks!
Lisa b

Here's the code:
SELECT [CC Coded].IDNO, [CC Coded].CNTRNAME, [CC
Coded].ROOM, [CC Coded].[age on Sept 1], [CC
Coded].Teacher, IIf([SEper] Is Not Null,[SEper],IIf
([SEper2] Is Not Null,[SEper2],IIf([SEper3] Is Not Null,
[SEper3],[SEper4]))) AS SocEm, IIf([CDper] Is Not Null,
[CDper],IIf([CDper2] Is Not Null,[CDper2],IIf([CDper3] Is
Not Null,[CDper3],[CDper4]))) AS CogDev, IIf([PDper] Is
Not Null,[PDper],IIf([PDper2] Is Not Null,[PDper2],IIf
([PDper3] Is Not Null,[PDper3],[PDper4]))) AS PysDev
FROM ((([CC Coded] LEFT JOIN [CC percents 3-6 and younger]
ON [CC Coded].AssNO = [CC percents 3-6 and younger].AssNO)
LEFT JOIN [CC percents 3-6 to 3-12] ON [CC Coded].AssNO =
[CC percents 3-6 to 3-12].AssNO) LEFT JOIN [CC percents 4-
0 to 4-5] ON [CC Coded].AssNO = [CC percents 4-0 to 4-
5].AssNO) LEFT JOIN [CC percents 4-6 and older] ON [CC
Coded].AssNO = [CC percents 4-6 and older].AssNO;

 

[John Spencer (MVP)]

And how many fields are in the other 4 queries? They count against the maximum
number of fields (255) you are allowed in a query.

You could be just getting a bad error message - you have an error, but the error
is not what is being reported (unlikely, but possible). Also, you might try
using the NZ function vice all the nested IIF statements.

SELECT [CC Coded].IDNO, [CC Coded].CNTRNAME,
[CC Coded].ROOM, [CC Coded].[age on Sept 1],
[CC Coded].Teacher,

NZ([SEper,NZ(SEper2,Nz(SEper3,SEper4))) as SocEm,


NZ([CDper],NZ([CDper2],Nz([CDper3],[CDper4]))) AS CogDev,

NZ([PDper] NZ([PDper2],Nz([PDper3],[PDper4]))) AS PysDev

FROM ((([CC Coded] LEFT JOIN [CC percents 3-6 and younger]
ON [CC Coded].AssNO = [CC percents 3-6 and younger].AssNO)
LEFT JOIN [CC percents 3-6 to 3-12] ON [CC Coded].AssNO =
[CC percents 3-6 to 3-12].AssNO) LEFT JOIN [CC percents 4-
0 to 4-5] ON [CC Coded].AssNO = [CC percents 4-0 to 4-
5].AssNO) LEFT JOIN [CC percents 4-6 and older] ON [CC
Coded].AssNO = [CC percents 4-6 and older].AssNO;


Also, you talk about GROUPING, but I don't see any grouping in the above statement.