Best way to add fields with a condition on each field

P

pokdbz

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.
 
T

Tom Wickerath

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.
 
D

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
 
P

pokdbz

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.
 
Top