Solved

To modify existing Query in MS Access

Posted on 2009-05-15
1
191 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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

762 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

10 Experts available now in Live!

Get 1:1 Help Now