Prev: Finding the space in a text string and returning text on eithersi
Next: create dropdown based on all people Not selectedyet to work onWork
From: John Spencer on 12 Mar 2010 16:30
How many values do you have? And how is the data structured?

If you have many values, I would build a table of equivalence.

Letters
LetterValue

A 1
P 0
TA .5
....

Then IF your table structure was correct you could easily generate the sum

SELECT Sum(Equivalent.LetterValue) as TheSum
FROM SomeTable INNER JOIN Equivalent
ON SomeTable.LetterField = Equivalent.Letters

If you have fields like Week1, Week2, Week3 and Week4 and just a few letter
values then you can construct an expression like the following.
IIF(Week1 ='A',1,IIF(Week1='TA',.5,0))
+ IIF(Week2 ='A',1,IIF(Week2='TA',.5,0))
+ IIF(Week3 ='A',1,IIF(Week3='TA',.5,0))
+ IIF(Week4 ='A',1,IIF(Week4='TA',.5,0))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Celeste wrote:
> I would like to sum values across multiples fields but the values in the
> fields I need are letters. So, is it possible to assign a value to each
> letter and then take the sum?
> For example, if this was an attendance record.
>
> Week 1 = A
> Week 2 = P
> Week 3 = P
> Week 4 = TA
>
> And I would want to count the number of Absences (A) for these four weeks so
> I would need to assign A=1, P=0, and TA=.5 (since 2 Tardies = 1 Absence).
> Can I do this? And how?
 | 
Pages: 1
Prev: Finding the space in a text string and returning text on eithersi
Next: create dropdown based on all people Not selectedyet to work onWork