report in access (Count)

[M.K]

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

 

[Duane Hookom]

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/Forums/en-US/addbuz/threads?

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


.