If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
report in access (Count)
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
report in access (Count)
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 . |
Thread Tools | |
Display Modes | |
|
|