Prev: output to pdf-select pages
Next: Assign batch # to rcds so report can be recreated.
From: M.K on 29 May 2010 11:06
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
From: PieterLinden via AccessMonster.com on 29 May 2010 12:19
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/Forums.aspx/access-reports/201005/1

From: Duane Hookom on 4 Jun 2010 15:07
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?


"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/Forums.aspx/access-reports/201005/1
>
> .
>
 | 
Pages: 1
Prev: output to pdf-select pages
Next: Assign batch # to rcds so report can be recreated.