Count with a condition

I have table called " aug_base " with the following fields
Bill_period,day_1,day_2,day_3,day_4,day_5,day_6 upto day_60

I want to take report bill_period wise count of day_1,day_2,day_3 upto day_60

But in this count should be greater than 100 in each column.

For example :

bill_period day_1 day_2 day_3 day_4
CH1 748 748 748 88
CH1 800 80 80 80
CH1 280 280 85 85

Report should be
bill_period day_1 day_2 day_3 day_4
CH1 3 2 1 1

I'll ignore the fact that your table isn't properly designed: that you
should never have repeating groups like that.

Try:

SELECT bill_period, Sum(IIf([day_1] 100, 1, 0) AS CountOfDay_1,
Sum(IIf([day_2] 100, 1, 0) AS CountOfDay_2, ...
FROM MyTable
GROUP BY bill_period

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Senthil" wrote in message
...
I have table called " aug_base " with the following fields
Bill_period,day_1,day_2,day_3,day_4,day_5,day_6 upto day_60

I want to take report bill_period wise count of day_1,day_2,day_3 upto
day_60

But in this count should be greater than 100 in each column.

For example :

bill_period day_1 day_2 day_3 day_4
CH1 748 748 748 88
CH1 800 80 80 80
CH1 280 280 85 85

Report should be
bill_period day_1 day_2 day_3 day_4
CH1 3 2 1 1

I won't ignore the table design.

You have a spreadsheet, not a relational database table.

Consider exporting what's in the "table" to Excel and doing the
(spreadsheet) calculation in a spreadsheet.

NOTE: If you want to get the best use of Access' relationally-oriented
features/functions, you can't feed it 'sheet data.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Senthil" wrote in message
...
I have table called " aug_base " with the following fields
Bill_period,day_1,day_2,day_3,day_4,day_5,day_6 upto day_60

I want to take report bill_period wise count of day_1,day_2,day_3 upto
day_60

But in this count should be greater than 100 in each column.

For example :

bill_period day_1 day_2 day_3 day_4
CH1 748 748 748 88
CH1 800 80 80 80
CH1 280 280 85 85

Report should be
bill_period day_1 day_2 day_3 day_4
CH1 3 2 1 1