CrossTab Query with Min

[Juan Robledo]

I have crosstab query without TRANSFORM (for asp.net) and i have one column
that calculate the Minimun of one field (PBh) as follows
"IIf(Count([CatB])<8,0,Min([PBh])) AS Peor", but what i really need is
calculate the minimun of a range of data (between [CT]=24 AND [CT]=30) .
How can i do this?


The sqltring is:

SELECT CatB, NombreCompleto, Sum(PBh) AS Parc,
IIf(Count([CatB])<8,0,Min([PBh])) AS Peor, [Parc]-[Peor] AS Neto,
Sum(IIf([CT]=24,[PBh],Null)) AS column1, Sum(IIf([CT]=25,[PBh],Null)) AS
column2, Sum(IIf([CT]=26,[PBh],Null)) AS column3,
Sum(IIf([CT]=27,[PBh],Null)) AS column4, Sum(IIf([CT]=28,[PBh],Null)) AS
column5, Sum(IIf([CT]=29,[PBh],Null)) AS column6,
Sum(IIf([CT]=30,[PBh],Null)) AS column7, Sum(IIf([CT]=31,[PBh],Null)) AS
column8
FROM cAuxRk
GROUP BY cAuxRk.CatB, cAuxRk.NombreCompleto
ORDER BY cAuxRk.CatB, Sum(cAuxRk.PBh) DESC;

 

[S.Clark]

Either use a Subquery, or Base this query on a query that performs the
desired limitation ahead of time.

 

[John Spencer]

Perhaps what you want is the following.

IIf(Count([CatB])<8,0,Min(IIF(Ct Between 24 and 30,[PBh],Null))) AS Peor

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Juan Robledo]

That's right.
Now it works perfect.

Thanks a lot.

Perhaps what you want is the following.

IIf(Count([CatB])<8,0,Min(IIF(Ct Between 24 and 30,[PBh],Null))) AS Peor

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have crosstab query without TRANSFORM (for asp.net) and i have one
column that calculate the Minimun of one field (PBh) as follows
"IIf(Count([CatB])<8,0,Min([PBh])) AS Peor", but what i really need is
calculate the minimun of a range of data (between [CT]=24 AND [CT]=30) .
How can i do this?


The sqltring is:

SELECT CatB, NombreCompleto, Sum(PBh) AS Parc,
IIf(Count([CatB])<8,0,Min([PBh])) AS Peor, [Parc]-[Peor] AS Neto,
Sum(IIf([CT]=24,[PBh],Null)) AS column1, Sum(IIf([CT]=25,[PBh],Null)) AS
column2, Sum(IIf([CT]=26,[PBh],Null)) AS column3,
Sum(IIf([CT]=27,[PBh],Null)) AS column4, Sum(IIf([CT]=28,[PBh],Null)) AS
column5, Sum(IIf([CT]=29,[PBh],Null)) AS column6,
Sum(IIf([CT]=30,[PBh],Null)) AS column7, Sum(IIf([CT]=31,[PBh],Null)) AS
column8
FROM cAuxRk
GROUP BY cAuxRk.CatB, cAuxRk.NombreCompleto
ORDER BY cAuxRk.CatB, Sum(cAuxRk.PBh) DESC;