Using Functions in Queries/Assigning Values to Non-numerical Entri

[Celeste]

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?

 

[John Spencer]

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

 

[Marshall Barton]

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

No need to translate all the letters to a number. It might
be a little messy, but you can use things like:

NumAbsences: Switch([Week 1]="A",1, [Week 1]="TA",.5,
True,0) + Switch([Week 2]="A",1, [Week 2]="TA",.5, True,0) +
Switch([Week 3]="A",1, [Week 3]="TA",.5, True,0) +
Switch([Week 4]="A",1, [Week 4]="TA",.5, True,0)

The BIGGER PROBLEM is you having fields like Week 1, Week 2,
Week 3 and Week 4. That is a spreadsheet and should not be
done in a real database. Instead you should have a separate
table with the week information, the table would have the
week number, the attendence code and a foreign key back to
the individual's record in your current table. Then you
could use Coubt, Sum etc. to calculate the totals