?
Solved

To modify existing Query in MS Access

Posted on 2009-05-15
1
Medium Priority
?
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 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 Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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