?
Solved

access query statment

Posted on 2013-11-08
11
Medium Priority
?
203 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 2000 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
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…
Suggested Courses

765 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