Advanced crosstab Query

[Crazyhorse]

I have a crosstab which has a issuer_name for the row. The column headers
are 7 different funds.

Each issuer has a price for each fund. A issuer can be in all 7 funds or
maybe 1 fund or 3 funds. That part is good.

If the price is in 4 of the 7 funds and all of the 4 prices are the same in
all the funds then I would want a "GOOD" in a new column.

But if the price is wrong in one of the 4 funds then I would want a "BAD".
Remember there are still 3 null values and that is fine because the issuer
is not in that fund. This is where I need the nulls to be canceled out or not
read.

I was thinking a complexed IIF statement but I think I will have to write a
custom function for this.

Thanks in advance.

 

[Michel Walsh]

You should not work with the crosstab at all, but with the table (query)
used to make the crosstab. I don't know what you mean by "if the price is
wrong in one of the four funds", and I don't know if 4 is just an example
from something more complex, but a total query can still help much better
than the crosstab.


SELECT issuer, COUNT(fund)
FROM table
WHERE fund = DMAX( "fund", "table", "issuer=" & issuer )
GROUP BY issuer


(again, on the original table, NOT on the crosstab) give you, for each
issuer, how many funds are equal to the maximum fund value, for that issuer.


Vanderghast, Access MVP

 

[Crazyhorse]

Sample

EXTRA COL Fund A Fund B Fund C Fund D
IssueName Good 22.18 22.18 22.18 22.18
IssueName2 Bad 18.14 18.08
IssueName3 Bad 16.14 16.13 16.14

 

[Michel Walsh]

TRANSFORM SUM(fundAmount) AS theCell
SELECT issueName, iif(MAX(theCell) = MIN(theCell), "GOOD", "BAD")
FROM tableName
GROUP BY issueName
PIVOT fundName



should do.



Vanderghast, Access MVP