It isn't really clear what you would expect to return in the Count(X) as
Years. You could create a totals query that groups by Well and Year. Then
create another totals query that groups by Well and counts the number of
years. Add this to your crosstab and join the Well columns and group by the
CountOfYears.

--
Duane Hookom
MS Access MVP


"Krzysztof Naworyta" wrote in message
...
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