Help in Access Report

[sjivanjee]

I have two table one stores student information and other one is for their attendance. In the attendance table their is a field called Attendance Status which has a value of P,A,Ex or L and Block which has a value of 1, 2, 3, 4 or 5.

I am creating a report based on two tables. My report has group by student full name and sub group by attendance - i need to count how many A, Ex and L are. 3 L is equal to one absent and then i also want to have total for that student for example John Smith has 3 A, 6 L or his total absent is 5

More Details
Student Table has the following fields:

Student ID Text
Full Name Text
Program Text

Attendance Table
Student ID Text
Attendance Status as Numeric
Block as Numeric

In the Form for Attendance which is a Sub Form of Student and I am using Radio Button Option as
P is 1
A is 2
Ex is 3
L is 4

Block is also as Radio Button
1
2
3
4
5
6

In my report i have group it by Full Name, and a sub group by attendance status. Any student who is late 3 times is equal to 1 absent so for example

John Smith is Absent 3, Late 6 so his total absent is 5

In my report i need to show all the absent, late and exused as a group under the student name

In a report i need for each student total of Absent, Excused or Late and then total of all

Thanks to all

[nico5038]

I would create a query to do the basic grouping and calculation.
Something like:

select studentID, StudentName, Attendance, Block, Attendance * Block as CalcField from tblStudent as a INNER JOIN tblAtendance as b ON (a.StudentID = b.StudentID)

Now the CalcField can be used in the report to sum the total value in the group footer.

Getting the idea ?

Nic;o)