Counting letters within a record?

[ayupchap]

Hi all



So I have creatde a database which is working great thanks to this forum! I wanted to add this functionality to it.

I have 6 different fields within a table I want to count the letter grdes in them. For example I have student A who has one of 3 letter grade options S,E or D. Now they have 5 fields that this garde can be in Number, Measurement, Geometry, Addition And subtraction.

How do I create a field that acts as a formula that will add all the D grades from each one? Do I use the calculated field? If so how do I get it to do what I want?

Thanks so much


Regards


Scott

[JoeM]

Sounds like you may have a design issue. Instead of having 5 different fields for one record (Number, Measurement, Geometry, Addition and Subtraction), a normalized design have 5 different records, where one of the records in something like "Category" or "Subject", and these 5 options would be options listed under their, and then you would have a single field to hold Letter Grade. Then it is pretty easy to count all the D's for each person (using an Aggregate Query).

Otherwise, you need to do a messy calculated field like:

Total_Ds: IIF([Number]="D",1,0) + IIF([Measurement]="D",1,0) + IIF([Geomtery]="D",1,0)  + IIF([Addition]="D",1,0)  + IIF([Subtraction]="D",1,0)

While this may not seem like a big deal, what if you had 20 of them? Or what if you had to add another Subject (then you would need to adjust all your formulas)?

With a well designed database, you would just be adding new records to an existing field, and wouldn't need to add new fields if you wanted to add more subjects. And it makes it much easier to work with too.

[alansidman]

Joe's comments are on target. To help you understand and build better designed data bases, look at this link:

http://r937.com/relational.html