Counting yes/no fields

[Greg]

I am creating a form that inspectors use to rate houses. On it I created each
item they are inspecting as a field with a yes/no box. The inspector will
inspect several houses by different builders. I need to be able to create a
summary sheet that will count the number of yes's and if possible divide that
by the total number of entries for any particular builder.

 

[Allen Browne]

Greg, you can could the number of Yes answers in a field named Field1, and
divide by the total by putting this into the ControlSource of a text box in
the Form Footer section:
=-Sum([Field1]) / Count("*")

There's a more fundamental design problem here though. Over time, one
builder will construct multiple houses. One house could have multiple
inspections (different inspections, or a repeat if it failed one.) One
inspection has multiple questions. You are therefore going to need at least
these tables:
Builder
House
Inpsection
Question
InspectionAnswer

The Inspection Answer will not have lots of yes/no check boxes. It will have
fields:
InspectionID which inspection is this answer part of.
QuestionID which question is being answered in this row.
Answer the value of the answer to this question in this
inspection.
The interface will consist of a main form bound to the Inspection table, and
a subform bound to the InspectionAnswer table (one answer per row in the
subform.)

If that does not make any sense to you, take a look at Duane Hookom's
example of how to do a survey database at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

 

[Greg]

There will only be one inspection per house but there will be multiple lots
per communities and multiple communities per builder. I am going to take a
training class because I need some general instruction on database design.

I would like to automatically calculate the number of good check marks on
each sheet and assign a percentage to a field. Then I would like to create
something that will display the average score for each crew. Also, I would
like to see a sheet with crew names along the left and each inspection item
along the top. Then have the average percent correct for each item for each
crew.

I hope that this makes sense. I would really appreciate any help.
Thank you,
--
Greg

Greg, you can could the number of Yes answers in a field named Field1, and
divide by the total by putting this into the ControlSource of a text box in
the Form Footer section:
=-Sum([Field1]) / Count("*")

There's a more fundamental design problem here though. Over time, one
builder will construct multiple houses. One house could have multiple
inspections (different inspections, or a repeat if it failed one.) One
inspection has multiple questions. You are therefore going to need at least
these tables:
Builder
House
Inpsection
Question
InspectionAnswer

The Inspection Answer will not have lots of yes/no check boxes. It will have
fields:
InspectionID which inspection is this answer part of.
QuestionID which question is being answered in this row.
Answer the value of the answer to this question in this
inspection.
The interface will consist of a main form bound to the Inspection table, and
a subform bound to the InspectionAnswer table (one answer per row in the
subform.)

If that does not make any sense to you, take a look at Duane Hookom's
example of how to do a survey database at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a form that inspectors use to rate houses. On it I created
each
item they are inspecting as a field with a yes/no box. The inspector will
inspect several houses by different builders. I need to be able to create
a
summary sheet that will count the number of yes's and if possible divide
that
by the total number of entries for any particular builder.

 

[Allen Browne]

You can count the number of Yes/No values in a field by summing it, and
negating the results. Access uses -1 for True, and 0 for false, so the
count of Yes answers is:
= - Sum([MyYesNoField])

Once you have a correct relational design, where all the answers are in the
column of a related table, you can use a crosstab query to output the
results in the way you are currently thinking of them.

All the best for the class. Sounds like a good idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

There will only be one inspection per house but there will be multiple
lots
per communities and multiple communities per builder. I am going to take a
training class because I need some general instruction on database design.

I would like to automatically calculate the number of good check marks on
each sheet and assign a percentage to a field. Then I would like to create
something that will display the average score for each crew. Also, I would
like to see a sheet with crew names along the left and each inspection
item
along the top. Then have the average percent correct for each item for
each
crew.

I hope that this makes sense. I would really appreciate any help.
Thank you,
--
Greg

Greg, you can could the number of Yes answers in a field named Field1,
and
divide by the total by putting this into the ControlSource of a text box
in
the Form Footer section:
=-Sum([Field1]) / Count("*")

There's a more fundamental design problem here though. Over time, one
builder will construct multiple houses. One house could have multiple
inspections (different inspections, or a repeat if it failed one.) One
inspection has multiple questions. You are therefore going to need at
least
these tables:
Builder
House
Inpsection
Question
InspectionAnswer

The Inspection Answer will not have lots of yes/no check boxes. It will
have
fields:
InspectionID which inspection is this answer part of.
QuestionID which question is being answered in this row.
Answer the value of the answer to this question in this
inspection.
The interface will consist of a main form bound to the Inspection table,
and
a subform bound to the InspectionAnswer table (one answer per row in the
subform.)

If that does not make any sense to you, take a look at Duane Hookom's
example of how to do a survey database at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a form that inspectors use to rate houses. On it I created
each
item they are inspecting as a field with a yes/no box. The inspector
will
inspect several houses by different builders. I need to be able to
create
a
summary sheet that will count the number of yes's and if possible
divide
that
by the total number of entries for any particular builder.