Group results into groups of 10

[Alex]

Hello,
I have a table with a series of dates. Each group of 10 dates represents a
session, so I'd like to create some way to count to 10 and create an
indicator which signifies a group, for a report.

Is this best done in a query and then show in the report, or run the
general query and do the grouping in the report?

Either way, I'm stumped as to how to pull this off, and using google to
track it down has been fruitless.

Any ideas and/or a nudge in the right direction greatly appreciated.
Alex

 

[Gina Whipp]

Alex,

Duane Hookom just did this for someone else but it looks like it could work
for you...


Consider adding a text box to the detail section:
Name: txtCountGroup
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code (module window) to the On Format event of the section:
Cancel = Me.txtCountGroup > 10

 

[Alex]

Alex,

Duane Hookom just did this for someone else but it looks like it
could work for you...


Consider adding a text box to the detail section:
Name: txtCountGroup
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code (module window) to the On Format event of the section:
Cancel = Me.txtCountGroup > 10

Thank you, Gina, I will try that!

 

[Gina Whipp]

Thanks to Duane Hookom! And you're welcome...

 

[John Spencer (MVP)]

Unless I am misreading that solution. It will print 10 records and then it
will not print any more records within the group.

I think that the poster wants to print records 1 to 10 and then do something.
Then print records 11 to 20 and then do something, 21 to 30 ...

I can think of a couple solutions, but the solutions depend on what the user
wants to do and whether or not a ranking query of unique numbers nwould be
needed (or is even possible) to establish the groups.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

I think another way to do this would be to create a function (fnGroupNumber)
which uses a static variable to count how many times it has been called, and
another static variable to track the GroupNumber. When the counter reaches
10, reset it and increment the GroupNumber.

Because this uses static variables, it will work properly the first time you
run it, but will start with the numbers that it ended with the next time you
run it. To prevent this, and start scratch each time you use it, you will
first have to call it from code and set Reset to true, before running your
query. This will reset the value of the counter and the groupnumber to zero.
So your code might look like:

Private sub cmd_PrintReport_Click

Call fnGroupNumber(1,1,true)
docmd.openreport "reportname", ...

Then, you would just add it as a column in your query, passing it any field
from your query as the SomeValue parameter, and the number of items you want
in the group as the 2nd parameter.

Public Function fnGroupNumber(Somevalue As Variant, _
ItemsPerGroup As Integer, _
Optional Reset As Boolean = False) As Integer

Static intCounter As Integer
Static intGroup As Integer

If Reset Then
intCounter = 0
intGroup = 0
Else
intCounter = intCounter + 1
Debug.Print intCounter,
If intCounter Mod ItemsPerGroup = 1 Then
intGroup = intGroup + 1
Debug.Print intGroup
End If
End If

fnGroupNumber = intGroup

End Function

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Alex]

Unless I am misreading that solution. It will print 10 records and
then it will not print any more records within the group.

I think that the poster wants to print records 1 to 10 and then do
something.
Then print records 11 to 20 and then do something, 21 to 30 ...

Hello John,
Yes, I wanted more than a top 10 query would give me, but thought I could
try to work the solution into a group header, but if you don't think that
will work I'd be happy to hear any solution you might think will work.

I tried using a query to create the extra columns for a group number 1,
2, 3, etc. but what seems to stop me from doing this is its reliance on
the unique id's and sometimes the dates are canceled and throws
everything off. If the first 4 dates were canceled, then I only had 6 in
the first group. I added extra expresssions to do this, but didn't
include them here, just the expression to give me a row count:

SELECT mt.SessionID, mt.SessionDate, (SELECT COUNT(*) FROM
tblTrainingSessions mt2 WHERE mt2.SessionID <= mt.SessionID) AS RowNumber

I need the final report to look like as you outlined, but do not need any
any ranking just sorting by date, and each date will be unique:

01/01/09 1 (or group 1 or anything to signify a grouping)
01/04/09 1
....
01/21/09 1


01/26/09 2
01/29/09 2
....
02/05/09 2

Thank you for any help.
Alex

 

[Gina Whipp]

John,

I have not tried the solution (found it when going thru the posts) but I was
under the impression it would print 10 records per group. Okay, I think
I'll play with that today to see what happens!

 

[Alex]

I think another way to do this would be to create a function
(fnGroupNumber) which uses a static variable to count how many times
it has been called, and another static variable to track the
GroupNumber. When the counter reaches 10, reset it and increment the
GroupNumber.

Looks really good, Dale, I'll give that a try!

 

[John Spencer (MVP)]

Again, not enough details to give you a specific solution, but perhaps the
following will work for you. I integer divide the count by 10 and also
modified the count to start with zero. So you will get groups of 10. 1-10 in
Group 0, 11-20 in Group 1, etc.

SELECT mt.SessionID, mt.SessionDate
, (SELECT COUNT(*)
FROM tblTrainingSessions as mt2
WHERE mt2.SessionID < mt.SessionID) \10 AS GroupNumber
FROM tblTrainingSessions as MT

Of course, if you need to sort by some value other than SessionID, then the
subquery will need to be modified to reflect that sort order.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Alex]

Again, not enough details to give you a specific solution, but perhaps
the following will work for you. I integer divide the count by 10 and
also modified the count to start with zero. So you will get groups of
10. 1-10 in Group 0, 11-20 in Group 1, etc.

SELECT mt.SessionID, mt.SessionDate
, (SELECT COUNT(*)
FROM tblTrainingSessions as mt2
WHERE mt2.SessionID < mt.SessionID) \10 AS GroupNumber
FROM tblTrainingSessions as MT

Of course, if you need to sort by some value other than SessionID,
then the subquery will need to be modified to reflect that sort order.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I'm not an sql expert, so I don't understand why excluding some records
would foul up the count, but as mentioned below it still happened, even
when counting the dates rather than the id's, which are all unique. Any
"group" which had excluded records ended up with a less than 10 count,
but still grouped. So, I created a query to just return the records I
wanted, and based the main query on that rather than the table, and it
seemed to work.
Thank you for your help as it gave me a nudge in the right direction, as
I was totally stumped and out of ideas.
Another user suggested a function, and think I'll give that a whirl as
that seems like an interesting solution, too.
Thanks, again, for your help.
Alex

 

[Alex]

I think another way to do this would be to create a function
(fnGroupNumber) which uses a static variable to count how many times
it has been called, and another static variable to track the
GroupNumber. When the counter reaches 10, reset it and increment the
GroupNumber.

Because this uses static variables, it will work properly the first
time you run it, but will start with the numbers that it ended with
the next time you run it. To prevent this, and start scratch each
time you use it, you will first have to call it from code and set
Reset to true, before running your query. This will reset the value
of the counter and the groupnumber to zero.
So your code might look like:

Private sub cmd_PrintReport_Click

Call fnGroupNumber(1,1,true)
docmd.openreport "reportname", ...

Then, you would just add it as a column in your query, passing it any
field from your query as the SomeValue parameter, and the number of
items you want in the group as the 2nd parameter.

Public Function fnGroupNumber(Somevalue As Variant, _
ItemsPerGroup As Integer, _
Optional Reset As Boolean = False) As Integer

Static intCounter As Integer
Static intGroup As Integer

If Reset Then
intCounter = 0
intGroup = 0
Else
intCounter = intCounter + 1
Debug.Print intCounter,
If intCounter Mod ItemsPerGroup = 1 Then
intGroup = intGroup + 1
Debug.Print intGroup
End If
End If

fnGroupNumber = intGroup

End Function

I managed to get this to work via querying a query, but I really like
your solution and want to try this, as well - great looking function!
Thanks for your help,
Alex