asked on

access query statment

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

mbizup

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

Rey Obrero (Capricorn1)

in the design view of your query, use Total query
use the Group By in your columns

MrMay

ASKER

mbizup that does not work... get error msg

MrMay

ASKER

capricorn1 I don't and never have used design view..... I prefer query

mbizup

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

MrMay

ASKER

mbizup... see attached... I'm getting this error.
Capture.JPG

mbizup

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.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrMay

ASKER

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?

Rey Obrero (Capricorn1)

that is correct...

MrMay

ASKER

thank you :-)