I would try:
TRANSFORM Val(Nz(Count(Column2),0)) as TheValue
SELECT Column1
FROM CSVFile
GROUP BY Column1
PIVOT Column2 IN
("Saturday","Sunday","Monday","Tuesday","Wednesday ","Thursday","Friday");
--
Duane Hookom
Microsoft Access MVP
NOTE: These public News Groups are ending June 1st. Consider asking
questions at
http://social.answers.microsoft.com/...ddbuz/threads?
"PieterLinden via AccessMonster.com" wrote:
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
.