Training Database

[Amer]

I have a database for our training programs. I need to create a report to
summarize the attendance and test result for each trainee.

I will go through more details for the database now:

1. Trainees table: includes basic details about trainees (IDs, names,
jobs,...)
2. Training Programs table: for the details about training programs in terms
of type, targeted audience, isntructors, topic, ...
3. Tests table: for tests to be made under each one of the Training
Programs. This means there is a relation between this table and the Training
Programs table (ProgramdID field in both tables).
4. Attendance table: for entering attendance status data on daily basis for
each trainee. So there are two relations here: one between Attendance and
Trainee tables; and one netween Attendance and Training Program table (that
is AttendanceID, TraineeID and ProgramID).

As I indicated earlier, when entering the attendance status data I do the
following:
- Choose the ProgramID
- Choose the TraineeID
- Enter the session date
- Choose the status from combo box showing: Absent, Present.

Now, when entering these data I will get a long list showing something like
this:

ProgramID TraineeID Date Status
ELT 4167 3/26/09 Present
ELT 3125 3/26/09 Absent
ALT 4167 2/13/09 Present
....

The report I am looking for should count the number of "Present" days and
also count the number of "Absent" days for each trainee in a specific
training program and claculate the average attendance rate based on the total
number of sessions.

By the word "summary" I meant having a report like:

-TraineeID: 4167
ProgramID: ELT Absent Days: 0 Present Days: 1 Attendance Rate:
100%
ProgramID: ALT Absent Days: 0 Present Days: 1 Attendance Rate:
100%

-TraineeID: 3125
ProgramID: ELT Absent Days: 1 Present Days: 0 Attendance Rate: 0%

....

 

[tina]

you should be able to get the data summary you need using a Totals query.
GroupBy the TraineeID, then ProgramID, then Count the statuses to get a
total days, and use an Expression to find days present, as

TotalPresent: Sum(IIf(Status = "Present", 1, 0))

and another Expression to calculate days Absent, as

TotalAbsent: Sum(IIf(Status = "Absent", 1, 0))

and another to calculate attendance rate, as

AttendanceRate: Sum(IIf(Status = "Present", 1, 0))/Count([Date])

then base the report on the Totals query.

hth

 

[tina]

and btw, i forgot to mention: if you really have a field in a table, and/or
a control in a form, called "Date", recommend you change it, since that is a
Reserved word in Access. for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

if you're not able to do that, make sure you surround the name with brackets
(like this: [Date]) in queries, expressions, and VBA code, so Access
doesn't mistake it for the Reserved word.

hth

you should be able to get the data summary you need using a Totals query.
GroupBy the TraineeID, then ProgramID, then Count the statuses to get a
total days, and use an Expression to find days present, as

TotalPresent: Sum(IIf(Status = "Present", 1, 0))

and another Expression to calculate days Absent, as

TotalAbsent: Sum(IIf(Status = "Absent", 1, 0))

and another to calculate attendance rate, as

AttendanceRate: Sum(IIf(Status = "Present", 1, 0))/Count([Date])

then base the report on the Totals query.

hth

I have a database for our training programs. I need to create a report to
summarize the attendance and test result for each trainee.

I will go through more details for the database now:

1. Trainees table: includes basic details about trainees (IDs, names,
jobs,...)
2. Training Programs table: for the details about training programs in terms
of type, targeted audience, isntructors, topic, ...
3. Tests table: for tests to be made under each one of the Training
Programs. This means there is a relation between this table and the Training
Programs table (ProgramdID field in both tables).
4. Attendance table: for entering attendance status data on daily basis for
each trainee. So there are two relations here: one between Attendance and
Trainee tables; and one netween Attendance and Training Program table (that
is AttendanceID, TraineeID and ProgramID).

As I indicated earlier, when entering the attendance status data I do the
following:
- Choose the ProgramID
- Choose the TraineeID
- Enter the session date
- Choose the status from combo box showing: Absent, Present.

Now, when entering these data I will get a long list showing something like
this:

ProgramID TraineeID Date Status
ELT 4167 3/26/09 Present
ELT 3125 3/26/09 Absent
ALT 4167 2/13/09 Present
...

The report I am looking for should count the number of "Present" days and
also count the number of "Absent" days for each trainee in a specific
training program and claculate the average attendance rate based on the total
number of sessions.

By the word "summary" I meant having a report like:

-TraineeID: 4167
ProgramID: ELT Absent Days: 0 Present Days: 1 Attendance Rate:
100%
ProgramID: ALT Absent Days: 0 Present Days: 1 Attendance Rate:
100%

-TraineeID: 3125
ProgramID: ELT Absent Days: 1 Present Days: 0 Attendance

Rate:

0%

...

 

[Amer]

Thank you for the help.

Still I need to verfiy if I should add separate columns for the formulas
that calcualte the Present and Absent days as well as the column for the
Status. Should they look like the following:

Status TotalPresent TotalAbsent
tabAttendance
Count Expression Expression

???

you should be able to get the data summary you need using a Totals query.
GroupBy the TraineeID, then ProgramID, then Count the statuses to get a
total days, and use an Expression to find days present, as

TotalPresent: Sum(IIf(Status = "Present", 1, 0))

and another Expression to calculate days Absent, as

TotalAbsent: Sum(IIf(Status = "Absent", 1, 0))

and another to calculate attendance rate, as

AttendanceRate: Sum(IIf(Status = "Present", 1, 0))/Count([Date])

then base the report on the Totals query.

hth

I have a database for our training programs. I need to create a report to
summarize the attendance and test result for each trainee.

I will go through more details for the database now:

1. Trainees table: includes basic details about trainees (IDs, names,
jobs,...)
2. Training Programs table: for the details about training programs in terms
of type, targeted audience, isntructors, topic, ...
3. Tests table: for tests to be made under each one of the Training
Programs. This means there is a relation between this table and the Training
Programs table (ProgramdID field in both tables).
4. Attendance table: for entering attendance status data on daily basis for
each trainee. So there are two relations here: one between Attendance and
Trainee tables; and one netween Attendance and Training Program table (that
is AttendanceID, TraineeID and ProgramID).

As I indicated earlier, when entering the attendance status data I do the
following:
- Choose the ProgramID
- Choose the TraineeID
- Enter the session date
- Choose the status from combo box showing: Absent, Present.

Now, when entering these data I will get a long list showing something like
this:

ProgramID TraineeID Date Status
ELT 4167 3/26/09 Present
ELT 3125 3/26/09 Absent
ALT 4167 2/13/09 Present
...

The report I am looking for should count the number of "Present" days and
also count the number of "Absent" days for each trainee in a specific
training program and claculate the average attendance rate based on the total
number of sessions.

By the word "summary" I meant having a report like:

-TraineeID: 4167
ProgramID: ELT Absent Days: 0 Present Days: 1 Attendance Rate:
100%
ProgramID: ALT Absent Days: 0 Present Days: 1 Attendance Rate:
100%

-TraineeID: 3125
ProgramID: ELT Absent Days: 1 Present Days: 0 Attendance Rate: 0%

...

 

[tina]

yes, assuming that you're showing me a mock-up of the query Design window.
and that's yes, *except* that the expression goes on the line with the
calculated field's title, as i posted earlier:

TotalPresent: Sum(IIf(Status = "Present", 1, 0))
TotalAbsent: Sum(IIf(Status = "Absent", 1, 0))

hth

Thank you for the help.

Still I need to verfiy if I should add separate columns for the formulas
that calcualte the Present and Absent days as well as the column for the
Status. Should they look like the following:

Status TotalPresent TotalAbsent
tabAttendance
Count Expression Expression

???

you should be able to get the data summary you need using a Totals query.
GroupBy the TraineeID, then ProgramID, then Count the statuses to get a
total days, and use an Expression to find days present, as

TotalPresent: Sum(IIf(Status = "Present", 1, 0))

and another Expression to calculate days Absent, as

TotalAbsent: Sum(IIf(Status = "Absent", 1, 0))

and another to calculate attendance rate, as

AttendanceRate: Sum(IIf(Status = "Present", 1, 0))/Count([Date])

then base the report on the Totals query.

hth

I have a database for our training programs. I need to create a report to
summarize the attendance and test result for each trainee.

I will go through more details for the database now:

1. Trainees table: includes basic details about trainees (IDs, names,
jobs,...)
2. Training Programs table: for the details about training programs in terms
of type, targeted audience, isntructors, topic, ...
3. Tests table: for tests to be made under each one of the Training
Programs. This means there is a relation between this table and the Training
Programs table (ProgramdID field in both tables).
4. Attendance table: for entering attendance status data on daily

basis
for

each trainee. So there are two relations here: one between Attendance and
Trainee tables; and one netween Attendance and Training Program table (that
is AttendanceID, TraineeID and ProgramID).

As I indicated earlier, when entering the attendance status data I do the
following:
- Choose the ProgramID
- Choose the TraineeID
- Enter the session date
- Choose the status from combo box showing: Absent, Present.

Now, when entering these data I will get a long list showing something like
this:

ProgramID TraineeID Date Status
ELT 4167 3/26/09 Present
ELT 3125 3/26/09 Absent
ALT 4167 2/13/09 Present
...

The report I am looking for should count the number of "Present" days and
also count the number of "Absent" days for each trainee in a specific
training program and claculate the average attendance rate based on

the
total

number of sessions.

By the word "summary" I meant having a report like:

-TraineeID: 4167
ProgramID: ELT Absent Days: 0 Present Days: 1 Attendance Rate:
100%
ProgramID: ALT Absent Days: 0 Present Days: 1 Attendance Rate:
100%

-TraineeID: 3125
ProgramID: ELT Absent Days: 1 Present Days: 0 Attendance

Rate:
0%

...