How to select values for different fields in a report

[Former Member]

Beginner needs help! I have five columns of linked data. I want to show the counts of the number of "true" values in each column. However when I set the first one to true none of the other rows which might be true for that instance when the first column is false show up anymore? Any help appreciated.

[Former Member · Accepted solution]

1st off how do you have your tables joined, suggest outter left joins.

create a formula

if (field) =true then 1 else 0

use manual running totals to calcualte your values

RESET

The reset formula is placed in a group header report header to reset the summary to zero for each unique record it groups by.

whileprintingrecords;

Numbervar X := 0;

CALCULATION

The calculation is placed adjacent to the field or formula that is being calculated.

(if there are duplicate values; create a group on the field that is being calculated on. If there are not duplicate records, the detail section is used.

whileprintingrecords;

Numbervar X := x + ; ( or formula)

DISPLAY

The display is the sum of what is being calculated. This is placed in a group, page or report footer. (generally placed in the group footer of the group header where the reset is placed.)

whileprintingrecords;

Numbervar X;

X

[Former Member · Accepted solution]

Sharon, thanks for your time in putting up the answer. But I'm probably a bit more of a beginner than you've creditted me as. I am working from only one table. Column 1 is date, column 2 is a true/false field for attendance by person 1 and column 3 is true/false for attendance by person 2 on the same date i.e. in the same row. I need to get a sum of all the true values in each of column 2 and column 3. Your answer probably does do that but if you have the time could you give me a step by step form dummies on this. Thanks Bob

[Former Member · Accepted solution]

Bob,

Most databases store TRUE/FALSE info as 1's & 0's... So simply right clicking the field and choosing Insert > Summary

and choosing SUM should do the trick. All true responses will be a 1 and therefore added to the summed total...

If, on the other hand you want a count of all records where BOTH T/F fields are true, then you'll need to throw an extra formula into the mix.

Create a formula like this...

IF {table.Column2} = TRUE AND {table.Column3} = TRUE THEN 1 ELSE 0

Then just just use the SUM summary on the formula.

HTH,

Jason

[Former Member · Accepted solution]

For a sum of all the true values:

IF {Table}.{Field2} THEN
(
    IF {Table}.{Field3} THEN
        2
    ELSE
        1
)
ELSE
    0