Calculate Difference Between Values of Successive Groups

[Micron]

I need to spot occurrences where the sum of the detail records in group level 2 exceeds a value AND if the difference between successive group level 2's is less than 1.0 . I think the only way I will achieve this without a multitude of complex queries is with a report. I envision a report where group level 1 is Empl_ID and group 2 is Shift_Start (as dd/mm/yyyy HH:MM:SS PM). The detail records will be hours worked. I can use conditional formatting to flag the occurrences of TOTAL that exceed a value. However, I don't know how to approach the second part. If I put a control for date/time in the group header or footer, can I compare B minus A, C minus B, but not C minus A? Or would I need to link a function to the control where it subtracts n-1 from n (e.g. 7-6) assuming each level two header or footer has an index (n) property? I'm only guessing that a function or expression would be able to calculate since the latter group would load after the one whose value it will be trying to subtract. I have already tried to achieve the result in Excel with formulas, but don't see a way out. The aim is for the output to require as little manual manipulation as possible. The table below is my attempt to present a visual of the desired result in as small a space as possible; I realize it is not how the report layout would look, nor would the date/time have to be repeated on each row if it can go into the group header. Ideally, I would be able to add the values where the difference is less than 24 hours, but that might be a bit much to ask for. Thanks for any consideration!

EMPL_ID	this is group	level one
12345	SHIFT_START	11/22/2014 6:30:00 PM	2	this is group level two
		11/22/2014 6:30:00 PM	2
		11/22/2014 6:30:00 PM	8
		TOTAL	18
		11/23/2014 6:30:00 PM	2	diff between this and previous
		11/23/2014 6:30:00 PM	6	group level is >= 1 (24 hrs)
		11/23/2014 6:30:00 PM	10	so OK, no flag
		TOTAL	18
		11/24/2014 6:30:00 AM	6	diff between this and previous
		11/24/2014 6:30:00 AM	6	group level is < 24 hrs.
		11/24/2014 6:30:00 AM	6	so FLAG
		TOTAL	18

[ItsMe]

I do not know if I fully understand the objective. Sometimes, I will use a report's On Load event to execute some VBA. A typical scenario would be where I need to do additional evaluations of sums and other conditions, displaying the results in headers and footers. I will incorporate the report's query into the DAO Recordset and then, as I loop through the recordset, set values to variables declared in the Report Module's header. Then I will use the variables in various detail's On Format sub procedures.

[Micron]

I gave the scenario as much explanation as I thought I could, but that's always the way with these sorts of posts. Even though you think you've covered "soup to nuts", it's never clear to everyone. Note: using upper case to link this text to the 'fields' above, not to shout!
If TOTAL is greater than some number, I will use conditional formatting.
If the difference between two successive group level 2 values is less than 24 hours, I want to flag this somehow. I might use a calculated control in the group header/footer with conditional formatting. However, to achieve the calculation, I need to subtract the values of successive groups. Note that the difference between purple group level values is >= 24 hours, so the control would not flag anything. The difference between the successive red values is <24 hours, so I need to flag it.

Regarding your approach, I'm not familiar with the On Format event of a detail grouping, but I will check it out.

[ItsMe]

...However, to achieve the calculation, I need to subtract the values of successive groups...

Yeah, I think I have been there before. It seems like a subreport would work but then you don't really want to display all of that stuff. You just need another summary of the original recordset (with a slight twist). So, that is when I turn to DAO. And, instead of using the Report's Recordsource, I may take the query and add different where criteria. What I will attempt to do is apply formulas that a report might do on its own. So, I might even incorporate a second query object that does some calcs and join it to the original query (all within DAO).

[Micron]

Hopefully this layout will make it more clear as to what I'm trying to accomplish. ItsMe, I will give your suggestions some thought. If I could figure out how to get the first date value into the shift header and the next date value into the footer, I think I'd have it. Will have to see if there's a headers or footers collection I can enumerate through in a loop, or maybe do some magic on a temp table and open the report on it.

EMPL_ID					DIFF.
12345
	SHIFT_START
	11/22/2014 6:30:00 PM			41965.77083
		JOB_ID	2
		JOB_ID	2
		JOB_ID	8
		TOTAL	12
	11/23/2014 6:30:00 PM			41966.77083
					1 day
		JOB_ID	2
		JOB_ID	6
		JOB_ID	10
		TOTAL	18
	11/24/2014 6:30:00 AM			41967.27083
			6
			6
			6
		TOTAL	18		< 1day

[ItsMe]

... If I could figure out how to get the first date value into the shift header and the next date value into the footer, I think I'd have it...

One subreport for the first date and one subreport for the last date may be what is needed. If we are talking about getting different results for each detail, subreports may be the only way. In fact, the data that is currently your detail may need to be a subreport (child to the First date) and then a Sub Sub Report for the second date (child to what is now the detail).

Consider the fact that a report renders all at once. It is not like a form that can requery or have an event trigger another trip to the data. So, your solution for the report will have to be multiple queries. Each of these queries run simultaneously when the report loads. All of these queries will have to be grouped and sorted. So, nesting subreports may be necessary.

[Micron]

To keep it short and sweet, I only showed 3 dates to express the desired goal. For one year, there would be about 70k detail records and maybe 25k shift date values. A few too many sub reports, I think! I have already figured that if the recordset count is too high, I would not open the report but have deferrred that decision as it is the cart, and as we know, the horse comes first! I only had 5 minutes to read about the report header/footer On Format you suggested. Will check it out tomorrow when back at work as baseball game is over now (my team won).

[IrogSinta]

I couldn't figure out how your total hours worked comes out to be less than 24 when the total for each day is 18. So I assumed that you were really calculating the time difference of the Shift Start times. If this is the case then the following works:

Add an unbound textbox to the "Shift_Start" footer section of your report (right beside the Total) and call it txtFlag.
In the report's code behind, add the following declaration before any subprocedure:

Dim dtLastShift As Variant

In the OnFormat event of the EMPL_ID header, add the following:

dtLastShift = Empty

In the OnFormat event of the SHIFT_START footer, add the following:

    If IsEmpty(dtLastShift) Then
        Me.txtFlag = ""
        
    Else
        If DateDiff("n", dtLastShift, [SHIFT_START]) >= 1440 Then   '1440 minutes = 24 hours
            Me.txtFlag = "1 day"
        Else
            Me.txtFlag = "< 1day"
        End If
        
    End If
    dtLastShift = [SHIFT_START]

The above will work only in Print Preview, not in Report View.

Ron

[ItsMe]

In the report's code behind, add the following declaration before any subprocedure:

Dim dtLastShift As Variant

In the OnFormat event of the EMPL_ID header, add the following:

dtLastShift = Empty

Hmmm, I do not think I have ever tried anything like this. Is this forcing garbage collection of the variable declaration? Why not place the Dim statement in the Report's header?

[IrogSinta]

Do you mean to declare the variable on the OnFormat of the Report Header? Doing that will make the scope of the variable limited to the Header procedure. The variable dtLastShift is used by both the EMPL_ID header procedure and the SHIFT_START footer procedure so it needs to be global to the report. If you are referring to setting a variable that references an object to Nothing for garbage collection, that is not the purpose here. Setting dtLastShift = Empty just resets the Variant type variable as if it was initially declared. This is needed in the EMPL_ID header otherwise the first OnFormat event for each subsequent employee would end up using the last shift date from the previous employee.


Ron

[ItsMe]

No, that is not really what I was getting at. Thanks for specifying it does not have anything to do with garbage collection. My curiosity is more specific to the placement of the declaration and how that relates to its scope (relevant to the different format events).

[IrogSinta]

It is a variable declared at the module level so that its scope is visible to all the procedures within that module. Here's an excerpt from MS Support:

A variable that is recognized among all of the procedures on a module sheet is called a "module-level" variable. A module-level variable is available to all of the procedures in that module, but it is not available to procedures in other modules. A module-level variable remains in existence while Visual Basic is running until the module in which it is declared is edited. Module-level variables can be declared with a Dim or Private statement at the top of the module above the first procedure definition.
It is a module level variable.

Ron

[ItsMe]

OK, thanks for the info. I wanted to understand if there was a nuance that I am unfamiliar with. /hijack

[IrogSinta]

No problem :-)

Ron

[Micron]

Well, you guys have been busy while I was trying another tack! I thought I might get away with a new table with a sub-set of data (2 years back), then querying that with a query containing a nested sql statement (not a sub query) that gets the Min of the next related SHIFT_START, and I would put that later date in a group footer and subtract if from the group header. I've created a query like this before (not as bad as it sounds once you've done it) and it works OK. However, any version of the query I tried today just runs & runs...

"I couldn't figure out how your total hours worked comes out to be less than 24 when the total for each day is 18 "
If you look again at the table posted, the header for that column is "DIFF"; difference in time between shift starts. 41967.27083 - 41966.77083 is less than a full day. So your assumption is correct.
I will study the suggestions here and decide if this is all worth the effort. I don't even have a report yet because I thought if I can't figure out the date, it'll never work.
I'll keep monitoring this thread as I process the great suggestions. Thanks.