TOTAL COLUMNS

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I would like to total SOME yes/no results in my query.
I have a query with names, date of birth, grade which don't need totals.
Then several yes/no fields which I need totals for.
 
It sounds like you want details of with names, date of birth, grade and
totals. You need a report to do that. Sum the totals in a footer.
 
Actually, I need the names etc. standard and then I have about 12 columns
(fields) that were set up in the table as yes/no fields. These I need
totalled. So of 47 records, 32 yes in the first column, 27 yes in the 2nd
column etc. I need the columns totalled vertically.
 
Actually, I need the names etc. standard and then I have about 12 columns
(fields) that were set up in the table as yes/no fields. These I need
totalled. So of 47 records, 32 yes in the first column, 27 yes in the 2nd
column etc. I need the columns totalled vertically.
Click to expand...

You can take advantage of the fact that Access stores Yes/No fields as
numbers: No is zero, and Yes is -1.

You cannot (easily) have both individual rows and a total in a Query or
(especially) a Table, but it's really easy to do on a Form or Report. Make the
Form or Report footer visible, and put a textbox in it with a Control Source

=Sum(Abs([yesnofield]))

The Abs() will convert the -1 to +1; the Sum will add up all the 1s.

I should add that your table design is perhaps less than ideal. What happens
if you need to add another item to your checklist? Redesign your table,
rewrite all your queries, redesign your forms, rebuild all your reports? OUCH!
If you in fact have a many to many relationship - each name has many checklist
items, each checklist item can pertain to many names - you should consider a
three table structure: your names table; a table of checklist items (your
current yes/no fieldnames, 12 rows at present); and a third table relating
them.
 
Back
Top