Solved

To modify existing Query in MS Access

Posted on 2009-05-15
1
209 Views
Last Modified: 2013-11-28
ID      UserName      Day1      Day2      Day3      Day4      DateCompl1      DateCompl2      DateCompl3      DateCompl4      DayCompleted
1      G      43      57      0      0      3/26/2009      3/27/2009                  27-Mar-09
2      G      62      38      0      0      4/20/2009      4/21/2009                  21-Apr-09
3      T      29      46      25      0      4/8/2009      4/9/2009      4/10/2009            10-Apr-09
4      T      70      0      0      0      5/5/2009                        
in existing query you can see user productivity
name, day 1 count, day2 Count, day3 count, day4 count,
            date1, date2, date3, date4
i want to make all records apper on the single row:
name, count per day, date
Should be :
1      G      3/26/2009      43
2      G      3/27/2009      57
3      G      4/20/2009      62
4      G      4/21/2009      38
5      T      4/8/2009      29
6      T      4/9/2009      46
7      T      4/10/2009      25
8      T      5/5/2009      70
0
Comment
Question by:rfedorov
1 Comment
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24397656
Select ID, Username, Day1 as DayCount, DateCompl1 as DateComp
From tablename where Day1 <>0
Union all
Select ID, Username, Day2 as DayCount, DateCompl2 as DateComp
From tablename where Day2 <>0
Union all
Select ID, Username, Day3 as DayCount, DateCompl3 as DateComp
From tablename where Day3 <>0
Union all
Select ID, Username, Day4 as DayCount, DateCompl4 as DateComp
From tablename where Day4 <>0
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now