To modify existing Query in MS Access

Posted on 2009-05-15
Medium Priority
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
Question by:rfedorov
1 Comment
LVL 77

Accepted Solution

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

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 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