Count help- across not down 
Author Message
 Count help- across not down

Thanks to all that responded previously, but let me clarify my situation.

Using Crystal Report V 4.5 with a Access-report writer database. (it's .MDB)
I have 9 fields (Event Attended) which have either "YES", "NO", or blank.
I need to write a formula that gives me a TOTAL of all the YES.

But I need the count PER RECORD (Not per event)---
So if Joe Smith showed up to 4 of 9 events the number I want to see is 4.

I tried Count (field 1, field 2, field3) but it seems to only give me the
right "count" when all fields have "YES"; a blank seems to throw it off.
I've changed the Global setting to "Convert Null to Default".  Should I try
writing IF...THEN...ELSE statements, (IF field 1="YES", 1 else 0) and doing
a SUM of that (but sum looks for a number, and it's a logical field, not a
number, right?)

I'm so close
--
Joe Meehan
The Guardians



Tue, 01 May 2001 03:00:00 GMT  
 Count help- across not down
Joe,
        Try using VB (or VBA, whatever). It is not as nice as ONE SQL statement
but I am pretty sure there is no easy way to do it in SQL; except of course
with the transform pivot statement to produce a crosstab, but then that is
verry messy because you will have to many different fields (there would be
one field for each person). This is nice if you don't have many people or
if you don't intend on having either.

Hope this helps.

William



Tue, 01 May 2001 03:00:00 GMT  
 Count help- across not down
Maybe you could use part of the following. Each record as 20 fields which
must be checked for correctness and then counted. I have two recordsets one
with the correct answers and the other with the answers choosen. This runs
through the answers choosen and determine the number of correct answers and
puts the total correct in the field of the answers recordset. You could
simply compare the field to a yes/no and eliminate part of the process. I
don't know if this will help you at all.

Private Sub butWinners_Click()
    Dim intWinners(20) As Integer
    Dim i As Integer
    Dim db As Database
    Dim qry As QueryDef
    Dim rst As Recordset
    Dim para As Parameter
    Dim intpoints As Integer
    If hNoWeek = False Then
        MsgBox ("You must choose a week first.")
        Exit Sub
    End If
    Set db = CurrentDb
    Set qry = db.QueryDefs("qryWinningTeams")
    qry.Parameters("[Enter week Number]") = Me.lstWeeks
    Set rst = qry.OpenRecordset
    For i = 1 To 20
        intWinners(i) = rst.Fields(2)
        rst.MoveNext
    Next i
    rst.Close
    Set qry = db.QueryDefs("qryWeeksPicks")
    qry.Parameters("Enter week Number]") = Me.lstWeeks
    Set rst = qry.OpenRecordset
    Do Until rst.EOF
        intpoints = 0
        For i = 1 To 20
            If rst.Fields(i + 1) = intWinners(i) Then
                intpoints = intpoints + 1
            End If
        Next i
        With rst
            .Edit
            !NumberofWinners = intpoints
            .Update
        End With
        rst.MoveNext
    Loop
    rst.Close
    DoCmd.OpenReport "rptRanking", acViewPreview, , "WeekID = " &
Me.lstWeeks
End Sub

Quote:

>Thanks to all that responded previously, but let me clarify my situation.

>Using Crystal Report V 4.5 with a Access-report writer database. (it's
.MDB)
>I have 9 fields (Event Attended) which have either "YES", "NO", or blank.
>I need to write a formula that gives me a TOTAL of all the YES.

>But I need the count PER RECORD (Not per event)---
>So if Joe Smith showed up to 4 of 9 events the number I want to see is 4.

>I tried Count (field 1, field 2, field3) but it seems to only give me the
>right "count" when all fields have "YES"; a blank seems to throw it off.
>I've changed the Global setting to "Convert Null to Default".  Should I try
>writing IF...THEN...ELSE statements, (IF field 1="YES", 1 else 0) and doing
>a SUM of that (but sum looks for a number, and it's a logical field, not a
>number, right?)

>I'm so close
>--
>Joe Meehan
>The Guardians




Tue, 01 May 2001 03:00:00 GMT  
 Count help- across not down
It sounds from your description that you have a table that has 9 fields in
it.  Possiblly you are displaying the 9 fields on a detail line in the
report.  If you are they would appear like this:
YES   NO   YES   null   nulll  YES   NO  YES   YES

This is my assumption from your description.

One of thing I dislike most about Crystal is it's treatment of Null values
in fields.

A formula such as

(If {table.Field1} = "YES" then 1 else 0) + (If {table.Field2} = "YES" then
1 else 0)

will produce a Null Results if any of the {table.Fieldx} values are null.
That might be why your formula only works when all the fields are YES.  (I
would assume it should work when they are all "NO" also.)  If there is a
possibility that a field could be null, you need to test it BEFORE you can
use that field in a formula.  To illustrate this look at the follow
formulas.  They look logically similar but in Crystal they are not.
========================================================
Formula 1:  If {table.Field1} = "NO" or isnull({table.Field1}) then 0 else 1

is different from

Formula 2:  If isnull({table.Field1}) or {table.Field1} = "NO" then 0 else 1
========================================================
The second formula will work because it checks for nulls first.

The formula you want should look something like this.

(If isnull({table.Field1}) or {table.Field1} = "NO" then 0 else 1)
+
(If isnull({table.Field2}) or {table.Field2} = "NO" then 0 else 1)
+
(If isnull({table.Field3}) or {table.Field3} = "NO" then 0 else 1)
+
(If isnull({table.Field4}) or {table.Field4} = "NO" then 0 else 1)
+
(If isnull({table.Field5}) or {table.Field5} = "NO" then 0 else 1)
+
(If isnull({table.Field6}) or {table.Field6} = "NO" then 0 else 1)
+
(If isnull({table.Field7}) or {table.Field7} = "NO" then 0 else 1)
+
(If isnull({table.Field8}) or {table.Field8} = "NO" then 0 else 1)
+
(If isnull({table.Field9}) or {table.Field9} = "NO" then 0 else 1)

Crystal is also case sensitive so you may need to add
Uppercase({table.fieldx}) = "NO"

Quote:

>Thanks to all that responded previously, but let me clarify my situation.

>Using Crystal Report V 4.5 with a Access-report writer database. (it's
.MDB)
>I have 9 fields (Event Attended) which have either "YES", "NO", or blank.
>I need to write a formula that gives me a TOTAL of all the YES.

>But I need the count PER RECORD (Not per event)---
>So if Joe Smith showed up to 4 of 9 events the number I want to see is 4.

>I tried Count (field 1, field 2, field3) but it seems to only give me the
>right "count" when all fields have "YES"; a blank seems to throw it off.
>I've changed the Global setting to "Convert Null to Default".  Should I try
>writing IF...THEN...ELSE statements, (IF field 1="YES", 1 else 0) and doing
>a SUM of that (but sum looks for a number, and it's a logical field, not a
>number, right?)

>I'm so close
>--
>Joe Meehan
>The Guardians




Tue, 01 May 2001 03:00:00 GMT  
 Count help- across not down
Access treats Yes as (-1), but calculates a Null if any value in the
expression evaluates to null.

If you can do the work inside Access first, you can use the Absolute Value
function (?Abs) and the NullToZero function (Nz), something like:
      TotalCount = Abs(Nz(Field1) + Nz(Field2) + ...)

good luck

jeff



Tue, 01 May 2001 03:00:00 GMT  
 Count help- across not down
Try this formula in Excel:

=COUNTIF(B2:J2,"yes")



Quote:

>Thanks to all that responded previously, but let me clarify my
>situation.

>Using Crystal Report V 4.5 with a Access-report writer database.
>(it's .MDB)
>I have 9 fields (Event Attended) which have either "YES", "NO", or
>blank.
>I need to write a formula that gives me a TOTAL of all the YES.

>But I need the count PER RECORD (Not per event)---
>So if Joe Smith showed up to 4 of 9 events the number I want to see
>is 4.

>I tried Count (field 1, field 2, field3) but it seems to only give
>me the
>right "count" when all fields have "YES"; a blank seems to throw it
>off.
>I've changed the Global setting to "Convert Null to Default".
>Should I try
>writing IF...THEN...ELSE statements, (IF field 1="YES", 1 else 0)
>and doing
>a SUM of that (but sum looks for a number, and it's a logical
>field, not a
>number, right?)

>I'm so close
>--
>Joe Meehan
>The Guardians




Fri, 04 May 2001 03:00:00 GMT  
 Count help- across not down
Joe,
did you get my email about how I  use a union select query to accomplish the
exact task which you reference?

select field1 FROM table
where field 1 = "YES"

union select field2 FROM table
WHERE field2 ="YES"

union select field3 FROM table
WHERE field3 = "YES"; etc....

A second query is then based on the first query whereby the records can be
counted.

Quote:

>Thanks to all that responded previously, but let me clarify my situation.

>Using Crystal Report V 4.5 with a Access-report writer database. (it's
.MDB)
>I have 9 fields (Event Attended) which have either "YES", "NO", or blank.
>I need to write a formula that gives me a TOTAL of all the YES.

>But I need the count PER RECORD (Not per event)---
>So if Joe Smith showed up to 4 of 9 events the number I want to see is 4.

>I tried Count (field 1, field 2, field3) but it seems to only give me the
>right "count" when all fields have "YES"; a blank seems to throw it off.
>I've changed the Global setting to "Convert Null to Default".  Should I try
>writing IF...THEN...ELSE statements, (IF field 1="YES", 1 else 0) and doing
>a SUM of that (but sum looks for a number, and it's a logical field, not a
>number, right?)

>I'm so close
>--
>Joe Meehan
>The Guardians




Sat, 05 May 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Count across the page rather than down

2. Columnar Report: Not-So-Far-Down, then Across

3. Count down timing help please

4. Count across fields in a table

5. Populating a db grid down, rather than across

6. printing across instead of down

7. Table Design: "Across or Down?"

8. Drop Down List Box - Drop Down portion does not always disappear after Click event

9. count down and disable a date field

10. Drop-down selection not working - help!

11. count down clock

12. count down

 

 
Powered by phpBB® Forum Software