How to use countif formula within a table in Access?

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

Guest

In excel you can use a countif formula to count the number of say "yes" and
"no" in a row and display that count. How can i do this in access and if i
can do it, is there also a way to import it through a pre-existing excel
spreadsheet?
 
You can generally use an expression like:
Sum(Abs(Gender="F"))
The expression Gender="F" will return either True/-1 or False/0. The Abs()
function converts -1 to 1. Sum() totals the 1s and 0s.
 
where do i do this in design view so that i can apply this formula to all
rows with data on them?
 
Use this type of expression in a totals query or header or footer of a
report or form. Where do you want to see this? Don't say "table" since you
shouldn't be viewing tables in a robust user interface and you shouldn't
store calculated values.
 
What if i didn't want it on the header or footer, what if i wanted to see it
at the end of the row but i'm only counting certian fields in that row, not
all of them? And lets say i wanted to see it in a report in a table, how
would i go about doing that?
 
Place the calculation in a report footer section. Your control source would
be something like:
=Sum(Abs(Gender="F"))
To get something like this to display in a datasheet (shouldn't ever be a
requirment) would require a union query.
 
Hi,

What your suggesting would give me totals for a certian field (column) where
as I am looking for row totals. Let me clarify: If i've got a survey and i
want to record replies, my field names would be something like: NAME, M/F,
QUESTION1, QUESTION2, QUESTION3 ... at the end of the row, i want the last
field (column) to total the number of yesses and noes from each person
surveyed. I'm not interested in the column totals, just row totals.
 
Back
Top