Solved

access query statment

Posted on 2013-11-08
11
191 Views
Last Modified: 2013-11-08
Hello, I needs some help with an access query statment.

This is my query statement
******
select TaskID (Select count(*)  from  Tasks where TaskType=0 AND ScheduleStart between #10/27/2013# AND #11/02/2013#)  as TotalPlannedSchedule, (Select count(*)  from  Tasks where TaskType=0 AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013#) as TotalComplete,   (select count(*) as CompletedONTime From Tasks where TaskType=0  AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013# AND Completed  <= ScheduleEnd) as CompletedONTime from Tasks ;

It does work correct. As you can see from attached pic1, I am getting the correct numbers, but I'm getting it repeated many many times. I just want it to show one row (as in pic2).

Any suggestions on how to modify the code.
pic1.JPG
pic2.JPG
0
Comment
Question by:MrMay
  • 5
  • 3
  • 3
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39634343
select TaskID (Select count(*)  from  Tasks where TaskType=0 AND ScheduleStart between #10/27/2013# AND #11/02/2013#)  as TotalPlannedSchedule, (Select count(*)  from  Tasks where TaskType=0 AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013#) as TotalComplete,   (select count(*) as CompletedONTime From Tasks where TaskType=0  AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013# AND Completed  <= ScheduleEnd) as CompletedONTime from Tasks
GROUP BY (Select count(*)  from  Tasks where TaskType=0 AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013#),  (select count(*) as CompletedONTime From Tasks where TaskType=0  AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013# AND Completed  <= ScheduleEnd)

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39634348
in the design view of your query, use Total query
use the Group By in your columns
0
 

Author Comment

by:MrMay
ID: 39634453
mbizup  that does not work... get error msg
0
 

Author Comment

by:MrMay
ID: 39634458
capricorn1  I don't and never have used design view..... I prefer query
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634466
I missed a comma.  You might also need to substitute * with TaskID

Try this:

select TaskID, (Select count(TaskID)  from  Tasks where TaskType=0 AND ScheduleStart between #10/27/2013# AND #11/02/2013#)  as TotalPlannedSchedule, (Select count(*)  from  Tasks where TaskType=0 AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013#) as TotalComplete,   (select count(TaskID) as CompletedONTime From Tasks where TaskType=0  AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013# AND Completed  <= ScheduleEnd) as CompletedONTime from Tasks
GROUP BY (Select count(TaskID)  from  Tasks where TaskType=0 AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013#),  (select count(TaskID) as CompletedONTime From Tasks where TaskType=0  AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013# AND Completed  <= ScheduleEnd)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:MrMay
ID: 39634501
mbizup... see attached... I'm getting this error.
Capture.JPG
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634548
Can you repost your query or better yet, post a sample database with just the needed tables and query?

Your original posting does not look syntactically correct... possibly a problem when copying it into this question.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39634555
try this, copy and paste



select distinct (Select count(*)  from  Tasks where TaskType=0 AND ScheduleStart between #10/27/2013# AND #11/02/2013#) AS TotalPlannedSchedule, (Select count(*)  from  Tasks where TaskType=0 AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013#) AS TotalComplete, (select count(*) as CompletedONTime From Tasks where TaskType=0  AND Status=2 AND ScheduleStart between #10/27/2013# AND #11/02/2013# AND Completed  <= ScheduleEnd) as CompletedONTime
FROM tasks;
0
 

Author Comment

by:MrMay
ID: 39634572
yes that worked capricorn1... so it looks like i was missing the distinct ... just so i understand this.... Distinct went through all the rows and since they were all the same it dropped everything that repeated?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39634590
that is correct...
0
 

Author Comment

by:MrMay
ID: 39634595
thank you :-)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

18 Experts available now in Live!

Get 1:1 Help Now