Filter Crosstab results

  • Thread starter Thread starter Cagney
  • Start date Start date
C

Cagney

I have a result set from a Crsosstab Query that gives me several
thousand records. I want to narrow the recordset to show ONLY records
that have data in MULTIPLE columns. If there is data in ONLY ONE
column I don't want to see those records. This seems like it should be
simple, but for the life of me i can't figure it out. Any ideas or
suggestions?
Merry Christmas!
 
Cagney said:
I have a result set from a Crsosstab Query that gives me several
thousand records. I want to narrow the recordset to show ONLY records
that have data in MULTIPLE columns. If there is data in ONLY ONE
column I don't want to see those records. This seems like it should be
simple, but for the life of me i can't figure it out. Any ideas or
suggestions?
Merry Christmas!
Click to expand...

Simple?? Not simple at all.
The first thought I have is to create a new query based on the crosstab
query, like this:
select * from crosstab_query
where iif([field1] is null,0,1) + ... + iif([fieldN] is null,0,1) > 1

You probably won't like the performance, but I can't think of anything else
at the moment.
 
Perhaps you can use
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

TRANSFORM Sum(X) As Total
SELECT Y
FROM SomeTable
WHERE ECount("Z","SomeTable","Y=""" & [Y] & """",True)>1
GROUP BY Y
PIVOT Z

Or use a query that looks like
TRANSFORM Sum(X) As Total
SELECT SomeTable.Y
FROM SomeTable INNER JOIN
(SELECT Y, Count(Z) as TheCount
FROM (SELECT Distinct Y, Z FROM SomeTable)) as ZCount
ON SomeTable.Y = ZCount.Y
WHERE ZCount.TheCount > 1
GROUP BY SomeTable.Y
PIVOT Z





John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Cagney wrote:

| I have a result set from a Crsosstab Query that gives me several
| thousand records. I want to narrow the recordset to show ONLY records
| that have data in MULTIPLE columns. If there is data in ONLY ONE
| column I don't want to see those records. This seems like it should be
| simple, but for the life of me i can't figure it out. Any ideas or
| suggestions?

TRANSFORM Sum(FieldValue) AS x
SELECT FieldRow, Count(x) AS z
FROM table
GROUP BY FieldRow
PIVOT FieldColumn;


and now you can filter field x
x>1, x<12, x between 3 and 5 ...
 
Krzysztof Naworyta wrote:


(...)
| TRANSFORM Sum(FieldValue) AS x
| SELECT FieldRow, Count(x) AS z
| FROM table
| GROUP BY FieldRow
| PIVOT FieldColumn;
|
|
| and now you can filter field x
| x>1, x<12, x between 3 and 5 ...


you can filter field Z, not X.
 
Perhaps you can use
     ECount() - an extended DCount()
at:
     http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

TRANSFORM Sum(X) As Total
SELECT Y
FROM SomeTable
WHERE ECount("Z","SomeTable","Y=""" & [Y] & """",True)>1
GROUP BY Y
PIVOT Z

Or use a query that looks like
TRANSFORM Sum(X) As Total
SELECT SomeTable.Y
FROM SomeTable INNER JOIN
     (SELECT Y, Count(Z) as TheCount
     FROM (SELECT Distinct Y, Z FROM SomeTable)) as ZCount
ON SomeTable.Y = ZCount.Y
WHERE ZCount.TheCount > 1
GROUP BY SomeTable.Y
PIVOT Z

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a result set from a Crsosstab Query that gives me several
thousand records. I want to narrow the recordset to show ONLY records
that have data in MULTIPLE columns. If there is data in ONLY ONE
column I don't want to see those records. This seems like it should be
simple, but for the life of me i can't figure it out. Any ideas or
suggestions?
Merry Christmas!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks very much for the help all.
I was in the midst of writing some code that workded ... up to a
point.
Then John suggested Allen Brownes ECOUNT funtion and that worked like
a charm!
It did exactly what I wanted.
Thanks again and Merry Christmas!
 
Back
Top