Best way to add fields with a condition on each field

[Guest]

I have 26 fields mmse_01...mmse_26. I need to add up each of the fields if
the answer is <= 1 for each one. What is the best way to go about doing
this. An example would be helpful.

 

[Joseph Meehan]

I have 26 fields mmse_01...mmse_26. I need to add up each of the
fields if the answer is <= 1 for each one. What is the best way to
go about doing this. An example would be helpful.

Are you adding them in a report, form, query, table etc.?

 

[Guest]

Hi Pokdbz,

Your task would be incredibly easy if the database design was correct.
Having multiple fields that store similar data is not considered correct
database design. You are actually storing a one-to-many (or many-to-many)
relationship within a single table by doing so.

The best investment of your time, in my opinion, is to rework the design of
your database. While you can use VBA code to add up the values in the various
fields, you are working A LOT harder than you need to with such a design.
However, if you want to try with your current design, you can try using a
series of IIF statements or a custom VBA function. Here's an example to get
you started with IIF statements, which you would do in query design view:

Field: MyTotal: IIF(Nz([mmse_01]<=1,[mmse_01],0)) +
IIF(Nz([mmse_02]<=1,[mmse_02],0)).........IIF(Nz([mmse_26]<=1,[mmse_26],0))

You can press Shift F2 to open a window to help you enter this huge string.

Here are some resources to database design papers:

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have 26 fields mmse_01...mmse_26. I need to add up each of the fields if
the answer is <= 1 for each one. What is the best way to go about doing
this. An example would be helpful.

 

[Douglas J Steele]

You're not going to like the answer, but the best way is to normalize your
table so that you don't have 26 repeating fields!

You should have 2 tables: one containing everything it currently does except
the 26 fields in question, and a second one which contains the key of the
first table, plus a description field that will contain which mmse the row
represents, plus a field containing whatever value is contained in that
mmse_nn field now.

Unfortunately, I think you have too many fields to be able to simulate this
through a UNION query. With fewer fields, you could do something like:

SELECT Field1, Field2, "1" AS MMSE_DS, mmse_01 AS MMSE_Value
FROM MyTable
UNION
SELECT Field1, Field2, "2" AS MMSE_DS, mmse_02 AS MMSE_Value
FROM MyTable
UNION
SELECT Field1, Field2, "3" AS MMSE_DS, mmse_03 AS MMSE_Value
FROM MyTable
UNION
....
UNION
SELECT Field1, Field2, "26" AS MMSE_DS, mmse_26 AS MMSE_Value
FROM MyTable

 

[Guest]

All of the 26 fields are different questions. So each ID has 26 questions
asked and those are the ones that need to be added if <=1. Isn't that the
correct way to do it?

The total just needs to be put into a total field mmsetotal. Either by
using code or a query I was wondering what would be the easiest.

 

[Joseph Meehan]

All of the 26 fields are different questions. So each ID has 26
questions asked and those are the ones that need to be added if <=1.
Isn't that the correct way to do it?

The total just needs to be put into a total field mmsetotal. Either
by using code or a query I was wondering what would be the easiest.

Note the other responses about normalization. You are going to want to
make two tables, one just for the answers. Access does not work like a
spreadsheet.

Now about storing that total. It seems from your answer you want to
compute and store the result of a calculation. That is almost always a bad
idea. Access can compute it as needed faster than recalling it. If you
were to store it and, at some later time, one of the answers is corrected or
changed, it is very likely the total would not be changed. So it is likely
you will only total the results in a form displaying the results or more
likely in a report which will be easier to set up the calculation.