Running Sum Query (again)

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
"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?"

A single query the answer is probably No. Maybe through a series of
queries, and a mix of VBA code you could.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

BruceM said:
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?
Click to expand...
 
It is good to know that attempting to do this in a single query is probably
futile. Any ideas on how to get started using multiple queries? To take a
different angle on this, maybe a vendor would want to rank customers by total
sales over a time period (where each sale is a separate record). Could that
be done?

[MVP] S.Clark said:
"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?"

A single query the answer is probably No. Maybe through a series of
queries, and a mix of VBA code you could.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

BruceM said:
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?
Click to expand...
Click to expand...
 
Back
Top