If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Crosstab column data count
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. |
#2
|
|||
|
|||
Crosstab column data count
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. |
#3
|
|||
|
|||
Crosstab column data count
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 |
#4
|
|||
|
|||
Crosstab column data count
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 |
#5
|
|||
|
|||
Crosstab column data count
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 |
#6
|
|||
|
|||
Crosstab column data count
Krzysztof Naworyta 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) as c1 | , avg(x) as a1 | , sum(x) as s1 | | FROM MyTable | GROUP BY well | PIVOT Year([visit_date]); Columns c1, a1, s1 are equal to c2,a2,s2: SELECT well , Count(x) AS c2 , Avg(x) AS a2 , Sum(x) AS s2 FROM ( SELECT well , Year([date1]) AS y , Avg(water_level) AS x FROM MyTable GROUP BY well , Year([date1]) ) tmp GROUP BY well; -- KN |
#7
|
|||
|
|||
Crosstab column data count
Indeed, if you aggregate on the alias of the TRANSFORMed expression, the
result is "as if" you would have aggregated HORIZONTALLY in the crosstab. Here, a given row, a given well, may not have a value for each PIVOT YEAR(visit), and for those "cells", a null is supplied and then, the horizontal aggregate COUNT on these columns does not count the year where there is no data, returning the number of year with some data, for the given well. So you get a DISTINCT COUNT. It is a short cut which allows, in ONE query, to aggregate over aggregate (a vertical aggregate and next, an horizontal one over the vertical aggregated just done, assuming you are in a visual representation). You can even remove the generated fields from the result by adding an IN(NULL) to the PIVOT expression., and you will be left with the 'horizontal' final aggregate. (From initial observations reported by Steve Dassin, in my best knowledge). Vanderghast, Access MVP |
Thread Tools | |
Display Modes | |
|
|