Count Horizontally

[lhooker]

I know how to use the count statement that counts vertically, how can I count horizontally (only if the value is greater than 0) ?

CustID 1st 2nd 3rd 4th 5th 6th 7th Count
2233 7 3 0 0 2 8 0 4
5730 3 0 0 0 1 0 6 3
7590 0 0 0 4 0 0 1 2

I need to capture the value in the 'Count' column (as shown above).

Thanks ! ! !

 

[vbaInet]

Perhaps you want to look into a Crosstab query. However, you didn't show us the raw data, you just showed your expected result so I would guess Crosstab.

 

[Geotch]

Data:
CustID 1st 2nd 3rd 4th 5th 6th 7th Count
2233 7 3 0 0 2 8 0 4

Try this: (untested):
Count: iif([1st]>0,1,0)+iif(2nd]>0,1,0)+.....
This should give you a count of 5

 

[namliam]

Counting horizontally is usually a sign of a broken design, if you are still working on building this (and/or didnt inherit this or have a prescribed format to use) I suggest you revisit your design.

 

[lhooker]

Namliam,

I inherited this data. I just need to count columns that has a value greater than zero, then add one to the counter for record. If ideas ? I tried to use 'Crosstab', but could not get it to work. Thanks ! ! !

 

[Geotch]

Did you try my example?

 

[lhooker]

Geotz,

I tried your suggestion, but the counter does not clear after each record. See below for code. Thanks ! ! !

=Count(IIf([1st]>0,1,0))+(IIf([2nd]>0,1,0))+(IIf([3rd]>0,1,0))+(IIf([4th]>0,1,0))+(IIf([5th]>0,1,0))+(IIf([6th]>0,1,0))+(IIf([7th]>0,1,0))

 

[Geotch]

Take the word count out =(iif....

 

[vbaInet]

If that's really how your data is presented then you want to use Geotch's suggestion.

Edit: Late response. Carry on guys.

 

[lhooker]

VbaInet

The below text represents the data. If there is a value greater than zero, count it (i.e. add 1 to 'Count'). The last column ('Count') is what I'm trying to achieve.

CustID 1st 2nd 3rd 4th 5th 6th 7th Count
2233....7....3 ....0 ...0 ...2 ...8 ...0 ....4
5730....3 ...0 ....0 ...0 ...1 ...0 ...6 ....3
7590....0 ...0 ....0 ...4 ...0 ...0 ...1 ....2


Please note that periods (i.e. '.') are place holders for this question submission. Thanks ! ! !

 

[vbaInet]

Tidied up. Paste in your query:

CountField: IIf([1st]>0,1,0)+IIf([2nd]>0,1,0)+IIf([3rd]>0,1,0)+IIf([4th]>0,1,0)+IIf([5th]>0,1,0)+IIf([6th]>0,1,0)+IIf([7th]>0,1,0)

 

[lhooker]

Geotz,

That fixed it . . . Thanks ! ! !

 

[lhooker]

VbaInet,

I was busy testing Geotch's suggested and came up with the same thing you recommended. Anyway Thanks ! ! !

 

[vbaInet]

Thanks to Geotch!

 

[lhooker]

How can I assigned this 'Count' to 'Results' in the 'Record Source' table of this same form. :banghead:

 

[vbaInet]

I don't understand what you mean by Count to Results. What are you trying to say?

 

[lhooker]

VbaInet,

'Count' is the receiving field on my form for the previously defined 'Iif' statement you commented on (listed below).

=(IIf([1st]>0,1,0))+(IIf([2nd]>0,1,0))+(IIf([3rd]>0,1,0))+(IIf([4th]>0,1,0))+(IIf([5th]>0,1,0))+(IIf([6th]>0,1,0))+(IIf([7th]>0,1,0))


'Results' is the table used by 'Record Source' of this form. I want the results of the 'Iif' statement to be stored in the field 'CountOut' of table 'Results'.

 

[vbaInet]

You really don't need to store calculated values. Calculate it whenever you need it:

http://allenbrowne.com/casu-14.html

 

[lhooker]

VbaInet,

OK . . . How can I produce a report ? The table is missing this information.

 

[vbaInet]

Remember when we I mentioned you copy and paste what I wrote into a query? Otherwise, paste it into the Control Source of a textbox.