tmb wrote:
| I have a crosstab query with Well Name as row heading , site visit
| Dates as column headings (formatted as "yyyy"), and averaged water
| level measurements data as the values. The column headings range from
| 2004-2010 and some wells have no measurements during some of these
| years.
|
| I need to create a dataset from the crosstab that shows total number
| of years for which each well has data. My attempts at changing the
| column headings and values around to accomplish this have been
| futile. Please help.

TRANSFORM Avg(water_level) AS x
SELECT well, count(x) as years
FROM MyTable
GROUP BY well
PIVOT Year([visit_date]);


--
KN