[AccessD] ...gurus only

...sigh ...I've probably tried at least 50 different qry approaches before 
trying to write code to do it ...I'll give this one a shot ...tks :)

William

----- Original Message ----- 
From: "Drew Wutka" <DWUTKA at marlow.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Friday, November 02, 2007 4:08 PM
Subject: Re: [AccessD] ...gurus only


> To do this with just queries isn't too difficult, though the series (by
> your definition) requires a little logic checking.  You could probably
> do this with just one query, but what I tested uses two.  A Union query,
> and then a totals query.  The Union query takes your W,X,Y, and Z fields
> and puts them in the same field:
>
> SELECT ALL W As Field, IIF(Not IsNull([W]),1,0) AS FieldHasData, ID
> FROM tblWilliam
> UNION ALL SELECT X As Field, IIF(Not IsNull([X]),1,IIF((Not IsNull([W]))
> AND (Not IsNull([Y]) OR Not IsNull([Z])),1,0)) AS FieldHasData, ID
> FROM tblWilliam
> UNION ALL SELECT Y As Field, IIF(Not IsNull([Y]),1,IIF((Not IsNull([W])
> OR Not IsNull([Y])) AND Not IsNull([Z]),1,0)) AS FieldHasData, ID
> FROM tblWilliam
> UNION ALL SELECT Z As Field, IIF(Not IsNull([Z]),1,0) AS FieldHasData,
> ID
> FROM tblWilliam;
>
> Note: My test table has ID as an AutoNumber, and then fields W,X,Y, And
> Z as long integers.  It's important to use the ALL statement in this
> thing, because Union queries default to not showing records with
> duplicate data.
>
> Next, we can now use a simple totals query to get the results you want:
>
> SELECT tblWilliam.ID, Sum(qryUnionedFields.FieldHasData) AS
> SumOfFieldHasData, 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;
>
> Note that the Max field is simple, it's just Maxing the values in the
> union query based on the ID field.  The series is summing that 'middle'
> field in the Union query, which, by the logic we put in the union query,
> shows a 1 if it needs to be counted in the series 'sum'.
>
> I'll send you the sample db I whipped this up in, offline.
>
> Drew
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
> Hindman
> Sent: Friday, November 02, 2007 2:46 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] ...gurus only
>
> ...ah me ...leave it to you to catch that one, no wonder I've been
> spinning
> in circles ...the fields are indeed long integers though I can convert
> them
> readily :)
>
> William
>
> 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.
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com