Multiple results with Count

[Syed Zeeshan Haider]

Hello Everybody,
In Access 2003, I am trying to get multiple results in a query using Count
with the same column and table as my source of data. So far I have been
unsuccessful in bringing two results of Count onto a same query.

For example: I have a list of amounts paid to different employees in a
column. I want to count the rows base on two different criteria (i) rows
with amounts more than $1000 each and (ii) rows with amounts less than $500.
I want to get the results of these two criteria in same query but in two
different fields with distinct names.

What would be the structure of SQL for this kind of calculation? Any ideas
how to work this out?

 

[Allen Browne]

Type an expression like this in the Field row:
Over1000: IIf([Amount] > 1000, 1, 0)

In the Total row under this field, choose Sum.

 

[Syed Zeeshan Haider]

Fantastic! Thank you!

Type an expression like this in the Field row:
Over1000: IIf([Amount] > 1000, 1, 0)

In the Total row under this field, choose Sum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Everybody,
In Access 2003, I am trying to get multiple results in a query using
Count with the same column and table as my source of data. So far I have
been unsuccessful in bringing two results of Count onto a same query.

For example: I have a list of amounts paid to different employees in a
column. I want to count the rows base on two different criteria (i) rows
with amounts more than $1000 each and (ii) rows with amounts less than
$500. I want to get the results of these two criteria in same query but
in two different fields with distinct names.

What would be the structure of SQL for this kind of calculation? Any
ideas how to work this out?