Count of Yes Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a table in which there are 17 ([S1]-[S17]) fields that are Yes, No,
NA and NS as responses. At the end of these fields I have a text box that
requires a total of yes responses.

What do I need to enter in the SYes box to get a Count of Yes Values?

Thanks,
 
Kenny G said:
Hello,

I have a table in which there are 17 ([S1]-[S17]) fields that are Yes, No,
NA and NS as responses. At the end of these fields I have a text box that
requires a total of yes responses.

What do I need to enter in the SYes box to get a Count of Yes Values?

Thanks,
Click to expand...

If I understand correctly, you want the count of Yes values for all the 17
fields.

One way to accomplish this is to use DCount to get the count of Yes values
for each field ([S1] - [S17]), and then use DSum to sum them up and display
in the textbox on your form. You can check the MS Access Help for DCount and
DSum formats. There may be easier ways to do this.

Hope this helps.

-Amit
 
Kenny said:
I have a table in which there are 17 ([S1]-[S17]) fields that are Yes, No,
NA and NS as responses. At the end of these fields I have a text box that
requires a total of yes responses.

What do I need to enter in the SYes box to get a Count of Yes Values?
Click to expand...


This kind of unnormalized table is always going to cause you
trouble like this. A database's xomputational capabilities
are geared toward working with rows, not columns. To be
effective in creating databases, you should stop thinking in
a spreadsheet paradigm and start looking at data with
relational colored glasses ;-)

First, a table should not have related columns (i.e. it
rarely makes sense to add multiple columns together).

Second, you can not use an expression in a table field. In
fact you should not even store a calculated value in a table
field. Calculations should be done in a query (or a
form/report).

You can calculate what you want in a query using a caculated
field:

SYes: -(S1="Yes") - (S2="Yes") - . . .

The reason for the minus signs is that Access uses -1 for
True. But, if any of those Sx field could be Null, then
you'll have to wrap each term with the Nz function:

. . . - Nz((S2="Yes"), 0) - . . .

If you had normalized your data by placing the Sx data in a
single column in a separate table (with an appropriate
foreign key), this whole issue would be solved by joining
the two tables and simply using:

SYes: -Sum(S = "Yes)
 
Marsh,

Thank you for your response. Since I have just started on this project your
suggestions will be easy to incorporate.

Kenny G

Marshall Barton said:
Kenny said:
I have a table in which there are 17 ([S1]-[S17]) fields that are Yes, No,
NA and NS as responses. At the end of these fields I have a text box that
requires a total of yes responses.

What do I need to enter in the SYes box to get a Count of Yes Values?
Click to expand...


This kind of unnormalized table is always going to cause you
trouble like this. A database's xomputational capabilities
are geared toward working with rows, not columns. To be
effective in creating databases, you should stop thinking in
a spreadsheet paradigm and start looking at data with
relational colored glasses ;-)

First, a table should not have related columns (i.e. it
rarely makes sense to add multiple columns together).

Second, you can not use an expression in a table field. In
fact you should not even store a calculated value in a table
field. Calculations should be done in a query (or a
form/report).

You can calculate what you want in a query using a caculated
field:

SYes: -(S1="Yes") - (S2="Yes") - . . .

The reason for the minus signs is that Access uses -1 for
True. But, if any of those Sx field could be Null, then
you'll have to wrap each term with the Nz function:

. . . - Nz((S2="Yes"), 0) - . . .

If you had normalized your data by placing the Sx data in a
single column in a separate table (with an appropriate
foreign key), this whole issue would be solved by joining
the two tables and simply using:

SYes: -Sum(S = "Yes)
Click to expand...
 
Amit,

Many thanks for your prompt reply.

Kenny G

Amit said:
Kenny G said:
Hello,

I have a table in which there are 17 ([S1]-[S17]) fields that are Yes, No,
NA and NS as responses. At the end of these fields I have a text box that
requires a total of yes responses.

What do I need to enter in the SYes box to get a Count of Yes Values?

Thanks,
Click to expand...

If I understand correctly, you want the count of Yes values for all the 17
fields.

One way to accomplish this is to use DCount to get the count of Yes values
for each field ([S1] - [S17]), and then use DSum to sum them up and display
in the textbox on your form. You can check the MS Access Help for DCount and
DSum formats. There may be easier ways to do this.

Hope this helps.

-Amit
Click to expand...
 
Back
Top