[AccessD] ...gurus only (Better Solution)

That'll teach me to do this stuff without a smoke break all afternoon.
Just realized there's an even easier way to do this.  Instead of all
that logic in the union query, we can get the same results
mathematically.

Here's the new Union Query:

SELECT ALL W As Field, IIF(Not IsNull([W]),1,null) AS FieldHasData, ID
FROM tblWilliam
UNION ALL SELECT X As Field, IIF(Not IsNull([X]),2,null) AS
FieldHasData, ID
FROM tblWilliam
UNION ALL SELECT Y As Field, IIF(Not IsNull([Y]),3,null) AS
FieldHasData, ID
FROM tblWilliam
UNION ALL SELECT Z As Field, IIF(Not IsNull([Z]),4,null) AS
FieldHasData, ID
FROM tblWilliam;

Note that all the goofy logic is gone.  Instead, if that field is not
null, it gets a value equal to it's position (1 through 4, equating to
W,X,Y,and Z).  If it doesn't have a value, then FieldHasData is Null.

With that in mind, instead of summing that field, we make the Series
field be the Max-Min of FieldHasData + 1.

Here's the new totals query:

SELECT tblWilliam.ID, Max([FieldHasData])-Min([FieldHasData])+1 AS
Series, tblWilliam.W, tblWilliam.X, tblWilliam.Y, tblWilliam.Z,
Max(qryUnionedFields.Field) AS MaxOfField
FROM tblWilliam INNER JOIN qryUnionedFields ON tblWilliam.ID =
qryUnionedFields.ID
GROUP BY tblWilliam.ID, tblWilliam.W, tblWilliam.X, tblWilliam.Y,
tblWilliam.Z
ORDER BY tblWilliam.ID;

That way, if you need to add or remove fields, all you have to do is add
another line to the union query (and add the field to the totals query),
and the only 'check' would be to make sure that the 'true' part of the
IIF statement is sequenced (1, 2, 3,...etc). 

Next time you have a problem like this, start with 'Drew, I'm trying
too..... now, go smoke, and tell me how to do that. ' ;)

Drew

The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.