count problem

[dolfan1873]

i have a query that has a program field with criteria for (3) different programs, then i count the number of entries for each of the (3) programs to determine how many items are open for each program, works great until there are no items (null values) then i get no output for whichever program has no values.

i would like this to output a zero if it does not count any items.

is there a way

thanks

 

[Trevil]

Do you have TWO tables with a left join? With one table, I am able to GROUP BY on Program, Count(IDfield) and the null Programs are counted as well. Not sure which field you are counting and which field you are selecting on. Here's a sample with one table:
SELECT SomeTable.Program, Count(SomeTable.ID) AS CountOfID
FROM SomeTable
GROUP BY SomeTable.Workstation
HAVING (((SomeTable.Program)="PGM1")) OR (((SomeTable.Program)="PGM2")) OR (((SomeTable.Program) Is Null));

Good Luck,
Wayne

35+ years of 'progress' -- can't we all just go wire boards again?

 

[PHV]

Any chance you could post the SQL code of your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886