tracking iterations of different training classes

[Roger Ball]

my database tracks attendance at events with different number of
iterations. Event A may have only one iteration where Event B has 3.

tbl_Students
- StudentID
- L_Name
etc

tbl_Events
- EventID
- Event
- Iterations

If Event A has 1 iteration and Event B has 3 iterations I would like a
query to show:

A1 B1 B2 B3
Joe 1 1 0 1
Jill 0 1 1 1

where Joe attended (1) all events except B2 (0)

A pivot table where I could expand each event would be a super bonus.

Thanks for the help!

 

[Roger Ball]

my database tracks attendance at events with different number of
iterations.  Event A may have only one iteration where Event B has 3.

tbl_Students
 - StudentID
 - L_Name
etc

tbl_Events
 - EventID
 - Event
 - Iterations

If Event A has 1 iteration and Event B has 3 iterations I would like a
query to show:

           A1          B1       B2       B3
Joe       1            1         0          1
Jill        0             1        1          1

where Joe attended (1) all events except B2 (0)

A pivot table where I could expand each event would be a super bonus.

Thanks for the help!

Forgot the attendance table...

tbl_Attendance
- AttendanceID
- StudentID
- EventID
- Iteration
- StatusID (1 if attended, 0 if missed or still not taken)

 

[Allen Browne]

Create a crosstab query where:
- StudentID is a Row Heading
- [EventID] & [Iteration} is the Column Heading
- [StatusID] is the Value

You can include tbl_Students, and also add the [L_Name] as a row heading if
you wish.

I suggest you use Sum as the grouping under StatusID, as that would indicate
if a person has attended an iteration twice.

 

[Roger Ball]

Thanks, it worked.

I appreciate your contribution to the group over the many years. I've
learned a lot.