query counting issue

[den1jxt]

I have a problem and cannot find the answer. I am using a make table query
and my query is simple, I am counting the records from my table that are
greater than a number and it will inject into the new table. The problem I
am having is I need to have all departments return a value. In essance if
the department returns nothing and the query is blank I need to have a zero
inserted into the field. the query works great when the values are greater
than my criteria but when the field has data and is less than the criteria it
has to inject a zero value? Can anyone help me with this/

 

[Jeanette Cunningham]

Hi,
You can use a calculated field for the department, something like this:

Dept:IIf(Len(Department) > 0,[Department],0)

if IIf is new, check it out in the help.

Jeanette Cunningham

 

[KARL DEWEY]

First query use a totals query that pulls a list of all departments.
Then use it in another query left joined to your data table. This will list
all departments. Then use an IIF statement to add a zero if less than
criteria.

 

[den1jxt via AccessMonster.com]

Thank you very much. That helped me out a lot

 

[den1jxt via AccessMonster.com]

Thanks a bunch. It was very helpful