multiple field query with count

[kfarwell]

I have 10 fields that represent individuals in car crashes. In each field, one of five possible answers might appear - A,B,C,K,N (Which represent injury code). I need to create a "total" field for each letter and by individual record - so for every crash number I need to know how many A injuries, B injuries etc. These total numbers are then used to determine total cost of each crash (that part I can do) I just cannot seem to get it to check each of the 10 fields for a specific injury and then give a total of that injury. Any help would be wonderful!

 

[dcx693]

The way your data is structured, you'll really need to create a custom function to calculate the result you need. I guess you could use one really huge IIF function, but I'm not even going to go there.

 

[dcx693]

I posted a simple custom function in this post: Calculated Field Expression

 

[neileg]

Your structure is not normalised. You should hold the individuals in a separate table with a ID that links them to the table of crashes. This way you have one record per individual.

A totals query based on the individuals table would give you the data you need.

 

[dcx693]

neileg, I agree with you, but I've gotten tired of trying to get people to restructure their databases properly.

 

[neileg]

Perhaps I'm not feeling so jaded! Or perhaps I'm not motivated to do my own job today....

Here's a quick view of how easy it could be