Alexandra504 said:
I have a querry with 15 fields. The data in fields 1 to 14 are etiher YES or
blank field. The last field (field 15) is the totals field. I need a formula
to put in the Totals field to count all the YES in the 14 previous fields.
Any suggestions?
Yes, two:
1) Do not store repeating information in fields. Store it in separate
rows with a field to indicate which which response/question the yes/no
pertains to.
2) Do not store calculated values in tables.
These are two tenets of relational database design. See Google "database
normalization" (including the quotes) for lots of good reading on this
topic.
What you describe fits well into a spreadsheet, not in a relational
database such as Access.
Is this really a query, or are you describing a table you want to update?
You might get what you want like so:
SELECT
(
IIF(FIELD1 = TRUE,1,0) +
IIF(FIELD2 = TRUE,1,0) +
IIF(FIELD3 = TRUE,1,0) +
... ad nauseam ...
IIF(FIELD14 = TRUE,1,0)
)
AS FIELD15
FROM MYTABLE;
One line for each question, plus overhead. Add one line for each
additional question you might come up with later.
Now, wouldn't you rather do this?
SELECT SUM(IIF(QUESTION_RESPONSE=TRUE,1,0))
FROM MYTABLE
GROUP BY QUESTION_ID;
Inherently scalable. Does not change when new questions are added.
Are you with me?
HTH
