Grouping Aggregate Query with multiple Criteria

[subhankarmc]

My table consists of DeviceID, Identification_Number, Amt.

A query needed which will display the count of Amt=0 and count of Amt>0 and Amt<10, Count of Amt>10 and Amt<100, Count of Amt>100 and Amt<1000.

[twinnyfo]

See your earlier post concerning your fortunate-ness in my response. Again, I assume you have no idea where to start.

What you are looking for is called a "CrossTab Query, which is similar to a Pivot Table in Excel. It aggregates values across various fields in your Table and can categorize based upon the criteria your provide. I can't image that you would have stumbled upon this solution all by yourself if you are new to SQL. Here is what I have that may suit your needs:

TRANSFORM Count(TableName.DeviceID) AS CountOfDeviceID 
SELECT "Device Count" AS DeviceCount 
FROM TableName 
GROUP BY "Device Count" 
PIVOT IIf([Amt]=0,"0",IIf([Amt]>0 And [Amt]<=10,"1-10",IIf([Amt]>10 And [Amt]<=100,"11-100",IIf([Amt]>100 And [Amt]<=1000,"101-1000",">1000"))));

You will have to replace "TableName" with the name of your table.

The key in this query is the expression after the "PIVOT" Statement. This splits up the values of your table based upon the Amt value in your table, provides a "heading" for that value and then counts hte number of entries.

Hope this hepps!