Training Database

  • Thread starter Thread starter Amer
  • Start date Start date
A

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%

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


tina said:
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


Amer said:
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
Click to expand...
Rate:
0%

...
Click to expand...
Click to expand...
 
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

???

tina said:
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


Amer said:
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%

...
Click to expand...
Click to expand...
 
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


Amer said:
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

???

tina said:
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


Amer said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
Rate:
0%
...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top