Is there a way to group between 2 records in a table?

[mintok]

Hi everyone,

I have a table called tblEntries which is a simple log table.

I want to create a report with a custom date range for each group. Each group has an irregular interval so i have created a field called "OpenNewGroup" for the user to specify if the entry belongs in a new group.

I have created a simple illustration that i drew up to illustrate my question.

So is there a way that the report can refer to this "OpenNewGroup" field and group all records under it within a group until the next "OpenNewGroup"?

 

[nanscombe]

What I would do is:

Add a GroupID (number - default value 0) to tblEntries.

Create a new table tblGroups with fields:
ID autonumber
startDate Date/Time
endDate Date/Time

IDEA A:

Create a query to update tblEntries.GroupID to tblGroups.ID WHERE tblEntries.dateField BETWEEN tblGroups.startDate AND tblGroups.endDate AND tblEntries.GroupID = 0

You could then group your entries on tblEntries.GroupID

You would need to populate tblGroups with relevant start and end dates for grouping and run the update every now and then.

IDEA B:

Have a button available to your users which would:
Put an end date on the last record of tblGroups
Added a new record to tblGroups with now() as the start date

The following code could then be used to add the latest tblGroups.ID to the tblEntries record.

Private Sub Form_BeforeInsert()
 Me!GroupID = DMax("ID","tblGroups")
End Sub

 

[mintok]

Thanks for the reply. I have thought about it but I can't assign a group ID to the entries because I intend to make this log so that a user can go to any date and slot in a record.

Having group IDs would break the integrity of the grouping if a user wanted to open a new group in between an existing group.

I just want this "OpenNewGroup" to be sort of a filing system to insert break points that Access can refer to to start a group.

 

[nanscombe]

Oh, Ok.

A function might be a more appropriate way to go.

Public Function getGroupID(byval groupOpen as variant, byval rowID as variant)
  Static sLngGroupID as Long

  If groupOpen =  "Yes" Then sLngGroupID = sLngGroupID + 1

  getGroupID = sLngGroupID

End Function

You will need to create, if one doesn't already exist, a module to put this in.

The Static variable sLngGroupID will remain available for the current Access session and increment every time "Yes" is passed to the function getGroupID() via the parameter groupOpen.

You can then pull this back in a query by adding a column like groupID: getGroupID([OpenNewGroup], [ID]).

You can then use it for grouping in a report but don't necessarily need to show it.

Oh yes, don't forget to order your data correctly or you may get some odd results.

 

[mintok]

So the idea is, I have to use this function to assign a temporary group ID to the records so that Access group the report based on these temporary group IDs?

 

[nanscombe]

That's it exactly.

As I say, you should be able to group on it without having to show it.

I assume you are aggregating, counting or summing the entries.

If you just wanted to put a line separating the groups you could just have a full width textbox with a Control Source something like = IIF([OpenNewGroup] = "Yes", String(80,"-"),"").

It would be empty for most records but give you a line across the report every time the value was Yes.

 

[nanscombe]

I couldn't get that function to give consistent results.

In the attached database:

Report01 shows just a line across.

Report02 shows the result using the function but I had to resort to the use of a temporary table.

 

[mintok]

Ah yes!! That line break is just what i wanted. Although i think the function to group them is a better solution. This line break works out well for me now while i figure how to do the function. Thanks alot for the help!

 

[mintok]

Wow u are a lifesaver. That query is just what i needed. I dont mind the temporary table, i can just overwrite it everytime i need to run the report. Thanks alot man!

 

[nanscombe]

Tidied Report02 up a bit and added an example of a prompt so you can decide whether to refresh the temporary table or not.

 

[mintok]

That's very nice of u to help with the prompt. I needed help on that as well for being a total noob at VBA lol. Although it would be even better if it executed itself on opening the report without the prompt. Thanks alot

 

[nanscombe]

It only needs to be executed when any records are updated or added so it would be a waste otherwise.

The code that runs, when the report is opened is this.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
On Error Resume Next

  [COLOR="Red"]If MsgBox("Refresh temporary table", vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then[/COLOR]
      DoCmd.DeleteObject acTable, "tmpReport02EventGroupings"
    
      strSql = "SELECT tblEntries.ID, getGroupID([OpenNewGroup],[ID]) AS groupID " & _
      "INTO tmpReport02EventGroupings FROM tblEntries " & _
      "ORDER BY tblEntries.eventDate;"
      CurrentDb.Execute strSql

[COLOR="red"]  End If[/COLOR]

  Me.RecordSource = "qryReport02"
End Sub

The two lines in red provide the prompt and decide if it runs.

The report is self contained but doesn't need to be.

The query qryReport02TmpEventGroupings, from database from mintok_01, creates the temporary table.

All of the code in the event Report_Open() could be cut out and the Record Source of the report returned to qryReport02.

Then you would just need to run qryReport02TmpEventGroupings to refresh the temporary table just before you run the report for the first time that session.

 

[nanscombe]

Simplified report02 so there is no code to run.

Added a timestamp to the temporary table, so you can see when it was last run.

First time in a session run qryReport02TmpEventGroupings then run the report, or any other reports you may write, as many times as you like.

If you prefered you could just create a macro, like report02_Run, to run qryReport02TmpEventGroupings followed by the report.