Crosstab column data count
in [Queries]
|
From: tmb on 11 May 2010 23:52 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.
From: KARL DEWEY on 12 May 2010 01:31 Post your crosstab SQL. -- Build a little, test a little. "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.
From: Krzysztof Naworyta on 12 May 2010 08:23 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
From: Duane Hookom on 13 May 2010 00:19 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" <k.naworyta(a)datacomp.com.pl> wrote in message news:hse6kk$7ss$1(a)news.onet.pl... > 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
From: Krzysztof Naworyta on 13 May 2010 06:47
Duane Hookom wrote: | It isn't really clear what you would expect to return in the Count(X) | as Years. You can get any aggregate value from pivotet columns, by using its alias: TRANSFORM Avg(water_level) AS x SELECT well , count(x) , avg(x) , sum(x) FROM MyTable GROUP BY well PIVOT Year([visit_date]); You can see that it is different than: TRANSFORM Avg(water_level) AS x SELECT well , count(water_level) , avg(water_level) , sum(water_level) FROM MyTable GROUP BY well PIVOT Year([visit_date]); -- KN |