count problem

[dolfan1873]

here is the SQL from a query i have, this works fine until i have no records to count in one of the programs (e2cipt or modipt or psipt), it just eliminates that program. is there a way i can keep the program and make it come in with a 0(zero).

SELECT Count(Input_tbl.Program) AS CountOfProgram, Input_tbl.Program
FROM Input_tbl
WHERE (((Input_tbl.TypeDocument)="RTE" Or (Input_tbl.TypeDocument)="RCI") AND ((Input_tbl.DateComplete) Is Null))
GROUP BY Input_tbl.Program
HAVING (((Input_tbl.Program)="E2CIPT" Or (Input_tbl.Program)="MODIPT" Or (Input_tbl.Program)="PSIPT"))
ORDER BY Input_tbl.Program.

 

[lespaul]

put the HAVING criteria in the WHERE clause:

SELECT Count(Input_tbl.Program) AS CountOfProgram, Input_tbl.Program
FROM Input_tbl
WHERE Input_tbl.TypeDocument IN ("RTE", "RCI")
AND Input_tbl.DateComplete Is Null
AND Input_tbl.Program IN ("E2CIPT", "MODIPT", "PSIPT")
GROUP BY Input_tbl.Program
ORDER BY Input_tbl.Program.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[dolfan1873]

i tried it and i can't get it to work, it still drops one program if it has no records, i need it to appear in the results with a 0 so all 3 programs always appear in the results.

maybe i didn't type something correct, i had it working but it still dropped the program.

thanks

 

[lespaul]

you would have to left join into a list of the projects in order to get a count of records that don't exist.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[lespaul]

Maybe something like this:

SELECT Count(A.Program) AS CountOfProgram, A.Program
FROM Input_tbl A
LEFT JOIN (SELECT Program FROM Input_tbl WHERE Input_tbl.Program As Program IN ("E2CIPT", "MODIPT", "PSIPT")) B ON B.Program = A.Program

WHERE Input_tbl.TypeDocument IN ("RTE", "RCI")
AND Input_tbl.DateComplete Is Null
GROUP BY Input_tbl.Program
ORDER BY Input_tbl.Program.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual