Calculate Difference Between Values of Successive Groups

[IrogSinta]

The report is pretty simple to create and the code modifications I gave are simple. Give it a try.

[Micron]

IrogSinta, thanks so much for your help. It almost works perfectly but it needs a bit of tweaking and maybe I have to review the practicality of what has resulted.
What I need to improve on the result is:
- the first txtFlag of a start time footer always displays <1 (I think I can figure this one out by testing for some condition on the variable that sets it)
- sometimes the flag is <1 for 1st footer on a new page (not always) even if the time span is several days. No idea what the trigger is or how to deal with it at the moment.

There are a couple of issues not related to how the records are rendered.
1) A years worth of data creates a 5,000 page report, so I want to think about how to minimize that beyond what page formatting can provide. Perhaps suppressing the rendering of a record or a whole Shift Start section is possible if the flag is >1? Should it be <1, management may have to open the timesheet application to verify the record because the record used for comparison would not be visible if it was suppressed by the previous comparison. However, if I cannot resolve the false positives I'm getting, they would be checking out a lot of records for which there is no issue.
2) I may need a piece of code that prompts to ensure no one accidentally sends 100+ pages to the printer. There doesn't seem to be an On Print event for a report, just for each section, so I will have to check that out too. Your section code runs again for the entire report if I choose to print just 10 pages, so preparing to print takes at least a minute before any pages are sent to the printer.

If this does not turn out to be practical, at least I tried and learned some things in the process!

[IrogSinta]

- the first txtFlag of a start time footer always displays <1

Sounds like you did not add dtShift = Empty in the OnFormat event of the EMPL_ID header section.

- sometimes the flag is <1 for 1st footer on a new page (not always)

I would need to see some sample data that does this.

As for your 5000 page report, one thing you can do is to get rid of the detail section completely.

Ron

[Micron]

[Sounds like you did not add dtShift = Empty in the OnFormat event of the EMPL_ID header section.] Correct! Doing so took care of that issue. I used debug.print to allow me to grab the pertinent parts of the record without displaying any confidential data and pasted some of it here. My comments in CAPS.

000780;;10/1/2013 6:30:00 AM; FIRST EMPL_ID HEADER - NO INCORRECT FLAG ANYMORE
000780;10/1/2013 6:30:00 AM;10/2/2013 6:30:00 AM;1 day
000780;10/2/2013 6:30:00 AM;10/4/2013 6:30:00 AM;1 day
000780;10/4/2013 6:30:00 AM;10/7/2013 6:30:00 AM;1 day
DATA GAP TO SHORTEN MY REPLY.....
000780;10/10/2013 6:30:00 AM;10/11/2013 6:30:00 AM;1 day
000780;10/11/2013 6:30:00 AM;10/15/2013 6:30:00 AM;1 day
000780;10/15/2013 6:30:00 AM;10/16/2013 6:30:00 AM;1 day
........
000780;10/21/2013 6:30:00 AM;10/22/2013 6:30:00 AM;1 day
000780;10/22/2013 6:30:00 AM;10/23/2013 6:30:00 AM;1 day 10/23 RECORD (SHIFT_ST) IS ON NEXT PAGE
000780;10/23/2013 6:30:00 AM;10/23/2013 6:30:00 AM;< 1day THERE IS ONLY ONE 10/23 RECORD IN REPORT AND QUERY SO THIS COMPARISON NOT VALID
000780;10/23/2013 6:30:00 AM;10/24/2013 6:30:00 AM;1 day
....................
000780;11/8/2013 6:30:00 AM;11/11/2013 6:30:00 AM;1 day
000780;11/11/2013 6:30:00 AM;11/12/2013 6:30:00 AM;1 day
000780;11/12/2013 6:30:00 AM;11/13/2013 6:30:00 AM;1 day 11/13 IS ON NEXT PAGE
000780;11/13/2013 6:30:00 AM;11/13/2013 6:30:00 AM;< 1day THERE IS ONLY ONE 11/13 RECORD IN REPORT AND QUERY SO THIS COMPARISON NOT VALID
000780;11/13/2013 6:30:00 AM;11/14/2013 6:30:00 AM;1 day
000780;11/14/2013 6:30:00 AM;11/18/2013 6:30:00 AM;1 day
000780;11/18/2013 6:30:00 AM;11/19/2013 6:30:00 AM;1 day
000780;11/19/2013 6:30:00 AM;11/21/2013 6:30:00 AM;1 day
AND SO ON.........
*******code********
Private Sub SHFT_ST_HDR_Format(Cancel As Integer, FormatCount As Integer)
If IsEmpty(dtLastShift) Then
Me.txtFlag = ""
Else
If DateDiff("n", dtLastShift, [SHIFT_ST_EST]) >= 1440 Then '1440 minutes = 24 hours
Me.txtFlag = "1 day"
Else
Me.txtFlag = "< 1day"
End If
End If
Debug.Print CONTRCTR_EMPL_ID & ";" & dtLastShift & ";" & SHIFT_ST_EST & ";" & Me.txtFlag
dtLastShift = [SHIFT_ST_EST]
End Sub
************************

Probably 95% of the detail sections only contain one row showing the name, job id & other pertinent info. If it were possible to move that info into the group header or footer, it would get rid of a row but I do not think that would work.
P.S. Came back to add that after shrinking report sections a bit, anything that was at the top of a page and was <1 when it should not be, became OK after it changed position in another report run.
While using debug, I notice that when I get to the record that is at the top of a page that I have printed out and is being compared to the last record on the previous page, the comparison >=1440 works as expected. However, the comparison repeats and the two values being compared are exactly the same, thus the result is <1 day. I think something is needed in a page header format sub, or I need another If Then to bypass the comparison if the Shift Start is exactly the same.
Could the Retreat Event be messing this up? I do believe it runs.

[IrogSinta]

I added a check in order to skip the process if the dates are the same:

    If [SHIFT_START] <> dtLastShift Then
        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]
    End If

As for trying to lessen the number of pages by only showing those that are <1day, you'll be better off, running a process to loop through a recordset of the table and flagging a new field. Then just create a simple report from that table (no need for any code behind).

Ron
Ron

[Micron]

I see you edited your post about "keep together" - seems we think alike, but you apparently have more experience! Last night I was thinking a check against the same date OR changing a page section property would solve it. However, it would not be the Shift Start section since the group was not being split, it is just not calculated correctly. I incorporated your START / dtLastShift comparison and it seems to work! I really appreciate your assistance and I learned a few things. THANKS!!

I have tried to reduce the records at the query stage but whatever query version I run just runs away and never comes back. I modeled this after a similar issue where I needed the next earlier date than the source table record date and that one works reasonably well. If you have any comment on my approach (below) for that, I would certainly appreciate it. If I cannot solve it there, the other thought I had was maybe it was possible to do a vba update on a temp table using the same approach for the report (setting a flag in the table) and opening the report on the <1's only, but ideally, to include the one record before as well. I have not done that before. Here's the query approach I took where I try to get the next date from an ODBC table that is the next date greater than each record in my source table. "SOME_TABLE" is the table name I have wiped out in the image. I had to cover whatever field and table names I thought I needed to stay out of trouble:

[IrogSinta]

Personally, I think the nested query approach would take long since you apparently have a lot of records (a year's data having over 5000 pages in a report). A single pass through a recordset of the table to flag the ones you need to show gets my vote.

Ron

[Micron]

With respect to the successful version I copied, it gets the date I need from over 7 million records in about 60 seconds so something is right with it. However, I can't duplicate the performance. If I take the approach that I append the records to a temp table then run code to update two fields, I think this will work better than what I have. I'm going to try (I don't know the field positions yet for a, b, x or y):

Sub SetDurationFlags()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim dteLastShift As Date, dteShiftStart As Date

Set db = CurrentDb
Set rs = db.OpenRecordset("tblXsHrsWrkd", dbOpenDynaset)

If rs.RecordCount = 0 Then

MsgBox "No records were retrieved."
GoTo exitHere

End If

rs.MoveFirst
Do While Not rs.EOF

dteLastShift = rs.Fields(a)
rs.MoveNext
dteShiftStart = rs.Fields(b)
If DateDiff("n", dteLastShift, dteShiftStart) < 1440 Then

rs.Fields(x) = True
rs.MovePrevious
rs.Fields(y) = True
rs.Move (2)

End If

Loop

exitHere:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

[IrogSinta]

7 million records in 1 minute for a nested query is pretty good, so maybe you should stick with that.

[Micron]

When I said "it runs away" I meant the new query just stops running after a long time, so no chance of using it. Not sure why, except maybe it's the table design as it is a different domain. One of the tables has 100+ fields in it and a hell of a lot of them are empty. Who does that?

[IrogSinta]

100+ fields in it and a hell of a lot of them are empty. Who does that?

Sadly, I know of a few :-)

[IrogSinta]

Perhaps you can post the nested SQL string you made.

[Micron]

I think it necessary to change source table and field names with aliases, so I did a find/replace on them. Looks accurate enough. The red part is what is in the query design grid for the field SHIFT_ST_EST.

SELECT tblXsHrsWrkd.*, tblXsHrsWrkd.SHIFT_ST_EST
FROM (tblXsHrsWrkd INNER JOIN ODBC_TABLE ON (tblXsHrsWrkd.TIMSHT_NO = ODBC_TABLE.TIMSHT_NO) AND (tblXsHrsWrkd.EMPL_ID = ODBC_TABLE.EMPL_ID) AND (tblXsHrsWrkd.DEPT = ODBC_TABLE.DEPT) AND (tblXsHrsWrkd.PO = ODBC_TABLE.PO)) INNER JOIN F ON ODBC_TABLE.TIMSHT_NO = F.TIMSHT_NO
WHERE (((tblXsHrsWrkd.SHIFT_ST_EST)=(SELECT MIN(SHIFT_ST_EST) FROM F AS T WHERE T.TIMSHT_NO=tblXsHrsWrkd.TIMSHT_NO AND T.SHIFT_ST_EST>tblXsHrsWrkd.SHIFT_ST_EST)));

[Micron]

Summary: the date was grouping level #2. Each day resulted in one header row + 1 or more detail records + one footer row (min. row total=3). For a year of data, report pages number in the hundreds. No good.
Solution:
- a delete query will delete data in a table.
- a totals query gets a data set for the time period supplied and appends to that table.
- In code, I compare each successive row to the previous and test if the emplID has changed. If so, I move to the next record.
- If not, I ensure the date is not the same. If it is, I move to the next record. If not, I subtract the earlier date from the second date.
- If it does not pass the test, I set a flag in the table to True for the record, move back one row, set the same flag (so I have a pair)
During the process, if I move back one row, I must move forward two to make the net advance one record for the next comparison. I also have to compare the beginning emplID to the current emplID and move to the next record if they are not the same. Regardless of the foregoing, if the value of a certain field in a record exceeds a value, I set a secondary flag to True.
I have no report yet, but the table data looks good, so the report query will be based on the two fields with True values. It's a bit complicated for sure, but it is only about 58 lines of code including error trapping and clean up. Making a report should be a snap now.
Thanks to all who helped.

[IrogSinta]

Your logic seems sound. So his long does if take to go through all your records?