Solved

access query statment

Posted on 2013-11-08
11
188 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
Comment Utility
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
Comment Utility
in the design view of your query, use Total query
use the Group By in your columns
0
 

Author Comment

by:MrMay
Comment Utility
mbizup  that does not work... get error msg
0
 

Author Comment

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

Expert Comment

by:mbizup
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

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

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
that is correct...
0
 

Author Comment

by:MrMay
Comment Utility
thank you :-)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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