Calculating a percentage in report

[Amer]

I have attendance sheet report. The values are: Absent, Late, Excused, Present.
I need to add a formula that calculates the percentage of attendance
according to Present value for each person.

How to do it?

 

[Allen Browne]

If you don't already have one, create a little table to hold the valid
values for your attendance field:

The table would have fields like this:
- AttendID Text Size 24 char. Primary key
- AttendValue Number Size Double
- SortOrder Number so you can sort them for your combo.
Save it as (say) tblAttend

Example data:
AttendID AttendValue SortOrder
====== ========= ========
Present 1 1
Absent 0 2
Late 0.5 3

You can then create a relationship to your existing table. Check the box for
Referential Integrity so that only valid values can be chosen.

On your form, you can use a combo that has this RowSource:
SELECT AttendID
FROM tblAttend
ORDER BY SortOrder, AttendID;

Now you can create a query that uses both tables, as the source for your
report. In the query output the fields you want, including AttendValue. In
the report, you can sum or average the AttendValue to get the results you
are after.

 

[Amer]

Thnaks for the help. But is it possible to keep the present format I have for
the values as text rather than numbers? Because I need this for my reports to
be easier to understand.

 

[John W. Vinson]

Thnaks for the help. But is it possible to keep the present format I have for
the values as text rather than numbers? Because I need this for my reports to
be easier to understand.

Allen's SUGGESTING that you keep the text - that's why AttendID is a 24
character *TEXT* primary key. The numbers are just used for sorting and
calculations.