Count Crosstab Resultant Columns

M

Mark Myers

I have created a crosstab query that correctly returns daily data for a period of some days (prior to today). Assume that today is 8/22/09.

Part 8/15/09 8/16/09 8/18/09 8/19/09
1234 14 12 17
2345 6 4
3456 1 99

I next need to get a COUNT of the columns (in this case 4) in order to get average daily demand.

In the above case I used Now()-7. We were closed on August 17th, and the 20th and 21st was a weekend, so the actual days is four. I just need to know how to get "4" as the dynamic number of columns in my crosstab.

EggHeadCafe - Software Developer Portal of Choice
Dr. Dotnetsky?s Cool .NET Tips & Tricks No. 23
http://www.eggheadcafe.com/tutorial...afbd-eb6c2ade7a5f/dr-dotnetskys-cool-net.aspx
 
V

vanderghast

TRANSFORM ...
SELECT ..., COUNT(*)
FROM ...


Note that this COUNT(*) will also count the NULLs while an average would
rather divide, here, by 3, for part 1234, not by 4. To get the count
without the nulls:


TRANSFORM ... AS theCell
SELECT ..., COUNT(theCell)
FROM ...



Vanderghast, Access MVP




in message news:[email protected]...
 
Top