View Single Post
  #2  
Old May 29th, 2010, 05:19 PM posted to microsoft.public.access.reports
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default report in access (Count)

M.K wrote:
If i have data in CSV file and it show like belwo

Column1 Column2
ABCD Saturday
ABCD Saturday
EFGH Sunday
ABCD Saturday
MMM Friday
ABCD Monday
EFGH Monday

I want report show like

Saturday Sunday Monday Tuesday Wednesday Thursday
Friday
ABCD 3 0 1 0 0
0 0
EFGH 0 1 1 0 0
0 0
MMM 0 1 1 0 0
0 1

BR


You don't have enough columns for a crosstab, so you have to add a "fake" one.


first query (qXTBSrc)
SELECT GroupingDayOfWeek.Grouping, GroupingDayOfWeek.DayOfWeek, 1 AS MyCount
FROM GroupingDayOfWeek;

crosstab:
TRANSFORM Sum(qXTBSrc.MyCount) AS SumOfMyCount
SELECT qXTBSrc.Grouping, Sum(qXTBSrc.MyCount) AS [Total Of MyCount]
FROM qXTBSrc
GROUP BY qXTBSrc.Grouping
PIVOT qXTBSrc.DayOfWeek;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201005/1