Solved

access query statment

Posted on 2013-11-08
11
198 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
[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
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39634590
that is correct...
0
 

Author Comment

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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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