B
BruceM
I have a training records database that consists mostly of three tables: an
Employees table (tblEmployees), a Training Session table
(tblTrainingSession), and a junction table(tblEnrollment). Most training is
on the job, so when training takes place a form is used to create a new
record in tblTrainingSession. Attendees are listed in tblEnrollment, which
contains fields related to the PKs of the other two tables. A subform is
used to enter attendance.
tblEmployee
EmployeeID (PK)
Name, etc.
tblTrainingSession
SessionID (PK)
Subject
Department, etc.
tblEnrollment
EnrollmentID (PK)
EmployeeID (FK)
SessionID (FK)
Date
Date is in the junction table because different shifts, etc. means that not
everybody is trained the same day.
I have a report based on a query (qryTrainingSession) that combines the
three tables. It is sorted by Employee last name and grouped by Employee ID.
I have added a hidden running sum text box (txtSumHours) to the Detail
section for training hours, and a text box to the group footer, which has as
its Control Source =[txtSumHours]. All is well, except I find I need to sort
by total training time. However, I cannot sort by a calculated control, but
only by a field in the underlying record source query. I have tried
searching newsgroups, etc. to find a way to do add a running sum field to a
query. It seems it is possible, but the example I found based on the
Northwind sample is for Access 97, and does not behave properly. (It would
have helped had they kept the example simple.) The other answers I found in
newsgroup postings did not quite get me there either.
Here's some background on the query (qryTimeSort). It is based on the three
tables already mentioned. I clicked View > Total when I constructed it.
On-the-job training is calculated at one fourth of the actual time spent, so
I have a calculated field OJT_factored: IIf([OJT] =
True,[TrainingTime]/4,[TrainingTime]). Total is set to Sum for that field.
Another field is RunningSum: DSum"[OJT_factored]","qryTimeSort"). Total is
set to Expression. The trouble is that the Running Sum seems to be the total
for all training for everybody. Even when I set the criteria to limit it to
one employee's records, the running sum is for all records. When I attempt
to build a report based on the query Access freezes.
I would like, if possible, to get on the right track with this. If I am
asking too much of Access, please let me know. I would like to automate the
process, but if sorting 100 records by hand a few times a year is easier I
will do that. By the way, Northwind is no help. A running Sum query is
discussed at the MS web site, but the article is about Access 97 (I have
2000), and is so far from what I need to do, and so filled with extraneous
details, that I cannot convert it to my needs.
Employees table (tblEmployees), a Training Session table
(tblTrainingSession), and a junction table(tblEnrollment). Most training is
on the job, so when training takes place a form is used to create a new
record in tblTrainingSession. Attendees are listed in tblEnrollment, which
contains fields related to the PKs of the other two tables. A subform is
used to enter attendance.
tblEmployee
EmployeeID (PK)
Name, etc.
tblTrainingSession
SessionID (PK)
Subject
Department, etc.
tblEnrollment
EnrollmentID (PK)
EmployeeID (FK)
SessionID (FK)
Date
Date is in the junction table because different shifts, etc. means that not
everybody is trained the same day.
I have a report based on a query (qryTrainingSession) that combines the
three tables. It is sorted by Employee last name and grouped by Employee ID.
I have added a hidden running sum text box (txtSumHours) to the Detail
section for training hours, and a text box to the group footer, which has as
its Control Source =[txtSumHours]. All is well, except I find I need to sort
by total training time. However, I cannot sort by a calculated control, but
only by a field in the underlying record source query. I have tried
searching newsgroups, etc. to find a way to do add a running sum field to a
query. It seems it is possible, but the example I found based on the
Northwind sample is for Access 97, and does not behave properly. (It would
have helped had they kept the example simple.) The other answers I found in
newsgroup postings did not quite get me there either.
Here's some background on the query (qryTimeSort). It is based on the three
tables already mentioned. I clicked View > Total when I constructed it.
On-the-job training is calculated at one fourth of the actual time spent, so
I have a calculated field OJT_factored: IIf([OJT] =
True,[TrainingTime]/4,[TrainingTime]). Total is set to Sum for that field.
Another field is RunningSum: DSum"[OJT_factored]","qryTimeSort"). Total is
set to Expression. The trouble is that the Running Sum seems to be the total
for all training for everybody. Even when I set the criteria to limit it to
one employee's records, the running sum is for all records. When I attempt
to build a report based on the query Access freezes.
I would like, if possible, to get on the right track with this. If I am
asking too much of Access, please let me know. I would like to automate the
process, but if sorting 100 records by hand a few times a year is easier I
will do that. By the way, Northwind is no help. A running Sum query is
discussed at the MS web site, but the article is about Access 97 (I have
2000), and is so far from what I need to do, and so filled with extraneous
details, that I cannot convert it to my needs.