How do I count the number of "yes" boxes?

[Renee Clough]

I am helping plan a conference and would like to use Access to manage/track
the registrations and mailings. I have managed to get the names and
addresses for the people we are inviting entered into a table. I have also
added to the table a number of columns with yes/no options (examples: are
they coming, are they bringing a spouse, have they paid yet). I would like
to know how to get queries that will count the yes or no answers (examples:
how many people are coming, how many people are having Wednesday dinner). I
don't know much about Access so any suggestions will need to be rather
specific and preferably describe how to use a wizard to do this. Thanks in
advance for any help you can give.

 

[tina]

you can write a Totals query to count the Yes answers of each Yes/No column.
getting a count of the No answers takes a bit more doing - but you won't
need those unless you're wanting a statistical report (such as 60%
responded, 22% are bringing a spouse, etc).

for the Totals query:
1. open a new query in Design view and add the table with the Yes/No
columns.
2. add each Yes/No field to the design "grid".
3. from the menu bar, click View | Totals.
4. in the design grid, change each Yes/No field name to an expression. for
example, let's say the first Yes/No field in the grid is named Paid. replace
the name Paid with the following, as

TotalPaid: Sum(Abs([Paid]))

in the Total row, change the default Group By to Expression.

the expression above "sees" each Yes answer as 1 and each No answer as 0,
and adds them all up.

do the same for each additional Yes/No field.

hth

 

[Jeanette Cunningham]

Hi Renee
If you have all the data in the one table and you just want to count how
many boxes are ticked, given that you are very new to Access, I would
suggest putting your table in to an Excel spreadsheet. In the spreadsheet
you will be very easily be able to count up how many checkboxes have been
checked.

In Access with all your data in the one table, there aren't any wizards or
easy ways to count up the checkboxes. There is a fair bit to learn if you
want to start from scratch to build this database.

However there are Access database templates on Microsoft.com.
I seem to remember that there is a template for event management, you could
probably use that fairly easily.

If you wish to build your database from scratch in Access, I suggest you get
a book or 2 that explain the basics of access databases.

Jeanette Cunningham

 

[Jeanette Cunningham]

Hi again Renee,
Here is a possibility.
--Go to the forms tab from the database window.
--Create form by using wizard
--In the dropdown box under Tables/Queries: choose the table with your info
--Under the dropdown is a list of the fields like LastName, FirstName, Paid,
WedsLunch, etc click the button with 2 arrows to the right of this list
--click Next
--choose Datasheet from the next list that opens
--choose standard or any colour scheme you like from the next list
--type a name like frmEvent in the name box
--click Finish

When the form opens with all your lists in it
--right click in a column that you want to count all the yes for
--make sure that you have the mouse in a yes when you choose Filter by
Selection
--the form will roll up to show only the records with yes
--down the bottom you will see the count of how many yes

Jeanette Cunningham