Advanced crosstab Query

C

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.
 
M

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
 
C

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Like to start using Access 1
Percent Help 2
Problem joining queries 5
allocated vs unallocated budget 1
Append Crosstab Query to Existing Table 0
SQL help 2
Mulit. Sums 3
SQL help - queries very slow 1

Top