A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Crosstab column data count



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 04:52 AM posted to microsoft.public.access.queries
TMB
external usenet poster
 
Posts: 4
Default 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  
Old May 12th, 2010, 06:31 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 12th, 2010, 01:23 PM posted to microsoft.public.access.queries
Krzysztof Naworyta
external usenet poster
 
Posts: 80
Default 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  
Old May 13th, 2010, 05:19 AM posted to microsoft.public.access.queries
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default 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  
Old May 13th, 2010, 11:47 AM posted to microsoft.public.access.queries
Krzysztof Naworyta
external usenet poster
 
Posts: 80
Default 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  
Old May 14th, 2010, 02:41 PM posted to microsoft.public.access.queries
Krzysztof Naworyta
external usenet poster
 
Posts: 80
Default 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  
Old May 14th, 2010, 03:36 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.