If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Count with a condition
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 |
#3
|
|||
|
|||
Count with a condition
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 |
Thread Tools | |
Display Modes | |
|
|