Greater than 0 query

  • Thread starter Thread starter CLay
  • Start date Start date
C

CLay

I have a table that looks like the following:
DOCNUM DATE CATEGORY N RN RT RW
123456 03032004 Viol 0 0 0 1
123555 05052002 Drug 0 0 5 0
111111 01012000 Viol 1 0 0 0
666555 06062003 Other 0 0 1 0
565662 09122000 Viol 0 0 0 2
888888 12122002 Prop 0 0 0 4

The N, RN, RT, and RW columns will have a number in only
ONE of the columns. I need to make a query that will
count the number of DOCNUM's for each group (N, RN, RT,
and RW) that have a number >0 in it. I then need to use
this query for a report. So, for the above example, my
query result would be:
N 1
RN 0
RT 2
RW 3

N has one record with a value of >0, RN has 0, RT has 2,
and RW has 3 records with a value of >0.

Any suggestions on how I can set this query up -- all at
once, instead of creating 4 diff't queries to check one
by one?

Thank You,
Clay
 
You could do something like this - create a query with 4
fields.
cntN, cntRN, etc.

Create an expression for each like
cntN:iif(N>0,1,0)

Change this to a totals query, and change the "group by"
to "sum"
 
Back
Top