Counting order values below a certain amount

[jcmeyer5]

I have a table I created with a make-table query. It has 4 fields. Order Number - Ship Method - Catalog - Amount

I want my table to look like

Ship Method - Catalog - count of orders $25.00 and under - count orders $25.01 to $50.00 - count of orders $50.01 to $100.00 - count of orders $100.01 and over

Try as I might, it wont work. Help me please.

Key field is Order Number/Ship Method combined.

 

[r937]

[tt]select ShipMethod
, Catalog
, sum(iif(Amount <= 25,1,0))
as &quot;Count of orders $25.00 and under&quot;
, sum(iif(Amount > 25,
and Amount <= 50,1,0))
as &quot;Count orders $25.01 to $50.00&quot;
, sum(iif(Amount > 50,
and Amount <= 100,1,0))
as &quot;Count orders $50.01 to $100.00&quot;
, sum(iif(Amount > 100,1,0))
as &quot;Count of orders $100.01 and over&quot;
from yourtable
group
by ShipMethod
, Catalog[/tt]

rudy

http://r937.com/

 

[jcmeyer5]

Ahhhh... muchos gracias!!

I was able tyo modify that to get to where I wanted (I needed $100 increments up to $10,000).

Thanks! I gotta get me an SQL guide or something!!