Get count of total numbers over a value 1

[dreampolice]

I am trying to fetch total number of values in a column that are greater than 1 in my Access 2004.

For example the below would show that fieldOne has 1 value greater than the value 1 and fieldTwo has 2 values greater than the value 1:

fieldOne    fieldTwo
1           3
2           1
1           3

The below is not working because it only fetches the first one (fieldOne) that has values over 1.

select count(fieldOne) as myTot,
count(fieldTwo) as myTot2
from theTable
where
fieldOne > 1
or
fieldTwo > 1

 

[dhookom]

Try this SQL:

Select Sum(Abs([FieldOne]>1)) as CountOne,
Sum(Abs([FieldTwo]>1)) as CountTwo
FROM theTable;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dreampolice]

Thank you.