G
Guest
This is a repost of sorts, as my original posting was too convoluted.
I have a training records database. It consists of an employees table
(tblEmployees), a training sessions table (tblSession), and a junction table
(tblEnrollment) since each employee may attend many sessions and each session
may be attended by many employees. It's pretty standard in that way. I can
post details as needed.
I can make a report, group by employee, and do a running sum of training
time for each employee, but cannot do anything with that running sum (such as
sort or use it in an expression). To solve this I am attempting a query for
calculating the total. I clicked View > Totals to produce a Totals row in
the design grid. This defaults to Group By unless I select something else
(such as Expression). The row cannot be left empty. Here is what I have so
far (stripped of fields that are not relevant to the question at hand). The
query is named qryHours. Explanations and questions will follow.
SELECT DISTINCTROW tblEnrollment.EmployeeID, tblEnrollment.SessionID,
tblSession.TrainingTime, qryFindTraining.OJTfactored,
DSum("[OJTfactored]","[qryFindTraining]") AS RunSum
FROM tblSession INNER JOIN ((tblEmployees INNER JOIN qryFindTraining ON
tblEmployees.EmployeeID = qryFindTraining.EmployeeID) INNER JOIN
tblEnrollment ON tblEmployees.EmployeeID = tblEnrollment.EmployeeID) ON
tblSession.SessionID = tblEnrollment.SessionID
GROUP BY tblEnrollment.EmployeeID, tblEnrollment.SessionID,
tblSession.TrainingTime, qryFindTraining.OJTfactored;
On-the-job training (OJT) counts as one fourth of the actual hours logged.
OJTfactored is a calculation in a query to adjust for this. The sum of hours
worked is based on this calculated field.
As it stands, DSum (in the calculated field named "RunSum") calculates
OJTfactored for all training sessions combined. If I add criteria to the
DSum expression (such as "[tblEnrollment].[EmployeeID] = 1234" the
calculation is based on 1234's records only. If I also restrict EmployeeID
to 1234 (in the query's criteria), the query yields information specific to
that employee. DSum produces the correct result in any case. Sometimes,
however, the query returns each field replicated four times. The hours still
add up correctly, but each session is listed four times for each employee.
This does not always happen. If I group by EmployeeID and sort by RunSum,
the first report shows up correctly; the rest seem to have each record four
times. If I base a report on the query, group the report by Employee ID, and
add the field RunSum (the DSum expression) to the group header or footer it
still contains the total for all training sessions combined unless I specify
criteria in the query design as described above.
Is it possible to construct a query to replicate Running Sum Over Group in a
report, except with a result that can be used in a calculation, in an
expression, or for sorting?
I have a training records database. It consists of an employees table
(tblEmployees), a training sessions table (tblSession), and a junction table
(tblEnrollment) since each employee may attend many sessions and each session
may be attended by many employees. It's pretty standard in that way. I can
post details as needed.
I can make a report, group by employee, and do a running sum of training
time for each employee, but cannot do anything with that running sum (such as
sort or use it in an expression). To solve this I am attempting a query for
calculating the total. I clicked View > Totals to produce a Totals row in
the design grid. This defaults to Group By unless I select something else
(such as Expression). The row cannot be left empty. Here is what I have so
far (stripped of fields that are not relevant to the question at hand). The
query is named qryHours. Explanations and questions will follow.
SELECT DISTINCTROW tblEnrollment.EmployeeID, tblEnrollment.SessionID,
tblSession.TrainingTime, qryFindTraining.OJTfactored,
DSum("[OJTfactored]","[qryFindTraining]") AS RunSum
FROM tblSession INNER JOIN ((tblEmployees INNER JOIN qryFindTraining ON
tblEmployees.EmployeeID = qryFindTraining.EmployeeID) INNER JOIN
tblEnrollment ON tblEmployees.EmployeeID = tblEnrollment.EmployeeID) ON
tblSession.SessionID = tblEnrollment.SessionID
GROUP BY tblEnrollment.EmployeeID, tblEnrollment.SessionID,
tblSession.TrainingTime, qryFindTraining.OJTfactored;
On-the-job training (OJT) counts as one fourth of the actual hours logged.
OJTfactored is a calculation in a query to adjust for this. The sum of hours
worked is based on this calculated field.
As it stands, DSum (in the calculated field named "RunSum") calculates
OJTfactored for all training sessions combined. If I add criteria to the
DSum expression (such as "[tblEnrollment].[EmployeeID] = 1234" the
calculation is based on 1234's records only. If I also restrict EmployeeID
to 1234 (in the query's criteria), the query yields information specific to
that employee. DSum produces the correct result in any case. Sometimes,
however, the query returns each field replicated four times. The hours still
add up correctly, but each session is listed four times for each employee.
This does not always happen. If I group by EmployeeID and sort by RunSum,
the first report shows up correctly; the rest seem to have each record four
times. If I base a report on the query, group the report by Employee ID, and
add the field RunSum (the DSum expression) to the group header or footer it
still contains the total for all training sessions combined unless I specify
criteria in the query design as described above.
Is it possible to construct a query to replicate Running Sum Over Group in a
report, except with a result that can be used in a calculation, in an
expression, or for sorting?