Group Records by Cumulative Value

[JNew]

Greetings,

I'm probably making this more difficult than it needs to
be. I'm trying to separate records into two different
groups based on the cumulative value in a field. Here's an
example:

Original Records:

Name Day HoursWorked
Joe Mon 15
Joe Tue 15
Joe Wed 15
Joe Thu 10

Desired output for Report:

Regular Time:
Name Day Hours Total
Joe Mon 15 15
Joe Tue 15 30
Joe Wed 10 40

Overtime:
Name Day Hours Total
Joe Wed 5 5
Joe Thu 10 10

Besides the obvious fact that Joe is milking the clock,
I'm unable to develop the queries and/or functions that
creates the running sum AND separates records based on the
value of the running sum.

I'm hoping someone in this group can point me in the right
direction. I've used a running sum function I found in the
MS Knowledge Base, but I'm unable to group the records
based on the results of the function.

Any assistance will be appreciated.

jn

 

[Ted Allen]

Hi,

It sounds like you are currently able to get the
following info in your query:

Name Day Hours Total
Joe Mon 15 15
Joe Tue 15 30
Joe Wed 15 45
Joe Thu 10 55

I would try adding two calculated fields to your query,
one for regular Total and one for OT Total.

For RT_Total, use:
RT_Total: = iif([Total]>40,40,[Total])

For OT_Total use:
OT_Total: = iif([Total]>40,[Total]-40,0)

Then, you could either total these side by side in your
report, or if you want to group as you had listed you
could just insert two subreports.

The data source for the first subreport (RT) would be
based on a query using the first query where [Total] -
[Hours] < 40 (all records where the worker had not reaced
40 hours before the start of the shift). But, on the
subreport, you would only insert and total the RT field.

Similarly, the data source for the second subreport would
include all records where OT_Total>0, and you would
insert and total the OT_Total field.

I'm sure there may be a better way to do it, but that's
what comes to mind.

Post back if I misunderstood the problem or if it doesn't
work.

-Ted Allen

 

[Tom Ellison]

Dear JNew:

In order to create this running sum, there must be some way your
system can tell which day comes first. Mon, Tue, Wed, Thu do not sort
alphabetically in that order. You say this is in the "Original
Records" but somehow I don't think so.

The solution would be a correlated subquery, or else a report column
with a running total. I cannot help more till I know how your design
is resolving Day into something that sorts.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

The easiest way is probably to do the running total in
the report itself instead of in a query.

To get it into a query, I'd probably do an initial
crosstab query by name as the row heading and across Day
as the column heading summing HoursWorked as the Value.
Once everything is on one record this way, you can
calculate the cumulative and overtime totals in new
columns. It's a bit of work because you have to repeat
the calculations for each day but really wouldn't take
long. Then you can format that single detail record per
name in the report exactly how you want it.