Count with a condition

[Senthil]

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

 

[Douglas J. Steele]

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

 

[Jeff Boyce]

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


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