Solved

To modify existing Query in MS Access

Posted on 2009-05-15
1
223 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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 …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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

14 Experts available now in Live!

Get 1:1 Help Now