grouping report data without sorting in Access 2003

How can I add a subtotal to a report in Access 2003 without sorting?  Here is an example of what I want:

1   AAA
2   AAA
3   AAA
4   BBB
5   BBB
6   AAA
7   AAA

1  AAA
2  AAA
3  AAA
    subtotal line

4  BBB
5  BBB
    subtotal line

6  AAA
7  AAA
    subtotal line

I want the data ordered by the seq number (1st column), but grouped by the second column.  If I add a sort/grouping by seq number it of course subtotals each seq  number and if I sort by the second column, the data is no longer sorted in seq number order.  This seems very simple, but I can't figure it out.  I could make it work by sorting by seq number in the query and then adding a grouping only subtotal by the 2nd column.  But I can't figure out how to add a grouping only column to the access 2003 report, it always sorts by it.  It would be nice if the sort option were "as is" or "unsorted", but it is not available in 2003, at least what I could find.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Then you need to go back "further" in the hierarchy than those values...

For example,
...What make them all different?
...Why are all the AAA values not together originally?
...What is the Primary key here?
...How is that original Sort order determined?

What you are asking for is possible, (by checking if the value changes) ...but will require a fair amount of work...
...especially if the the "sequences" might change...

This is why it is always a good idea to state the reason for wanting things like this...
...Perhaps there is an alternate approach...

I could make it work by sorting by seq number in the query and then adding a grouping only subtotal by the 2nd column


You'll discover that reports pretty much ignore the Order By of the underlying query.
The good news is that reports are read-only.
That means you are free to concoct whatever you need in the query as artificial columns to group/sort by.  So if you can construct some computed column in the query that is good to sort and group by, you're golden.

The other possibility is subreports.
Have the main report with your 1,2,3,4,5 data, and your subreports with the XXX,XXX,XXX data.
You then can separate the sorting and groupings, rather than have them interfere.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How can I add a subtotal to a report in Access 2003 without sorting?  Here is an example of what I want:>>

  DIY Route<g>

1. Delcare a  variable in the reports module.
2. Init the variable to 0 in the Group header.
3. in the OnFormat, do:

  If FormatCount = 1 then
       var = var + field
  End If

4. In the footer, push the var value into a control:

  Me.<somecontrol> = var

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

compuzak1Author Commented:
What would I make my group header field?  If the group header is the seq number it will break on each row, if it is the 2nd column it will then reorder the data in 2nd column order.  Not following how your idea will work for ordering, but do understand that it would sum up the group, but I can't make a group without sorting.
Jeffrey CoachmanMIS LiasonCommented:
Who are you responding to?

There were three Experts posts, ...please reply to each expert on the merits of his/her post, in the order that they were posted.

Jeffrey CoachmanMIS LiasonCommented:
As I first asked, ...what is the difference between AAA 1,2,3 and AAA 5,6  ?
 That "Difference" could be what you sort on.
compuzak1Author Commented:
I was referring to the post by Nick67, didn't see the other one until now.   As far as the other posts go, I don't see how I could create a field that will do what I want.  Also, why is the reason I want to do this important?  I listed the raw data and show you the output I desire.  The actual data should have no relevance to this problem.

I am currently working on something based off what Nick67 said.  It involves turning off the visible when the seq number causes the group break, this way it will still sort by seq number,, but it will not subtotal after each seq number.  I will then manually count the subtotal (per Nick67) and print it when the 2nd column changes.  This solution seems like it may work.  Just need to recognize what caused the break (seq number or 2nd column) and also manually total the data fields.  If it is the seq number, change the group header and footer to not visible.  If it is the 2nd column causing the break or the last row, then make the group header and footer visible.  Seems like a lot of work for a simple idea.
Jeffrey CoachmanMIS LiasonCommented:
First I will say that what Jim posted should work for you, ...because 95% of the time Print Preview is used.

Here is another technique:
(I am on my way home, I threw this together in a hurry)
If, however, you need this in Report View, then you could also loop the records and sort on the "Change"
But what I posted would require adding a Sort field to your report, and also running a function to create a sequential grouping based on the changes

Function code and sample db is attached
Public Function Sorting()
Dim rst As DAO.Recordset
Dim strData As String
Dim bytCounter As Byte
Set rst = CurrentDb.OpenRecordset("SELECT ID,Data FROM YourTable")
bytCounter = 1
strData = rst!Data
Do Until rst.EOF
    If rst!Data = strData Then
        bytCounter = bytCounter
        strData = rst!Data
        bytCounter = bytCounter + 1
    End If
    CurrentDb.Execute "Update YourTable SET Sortx=" & bytCounter & " WHERE ID=" & rst!ID, dbFailOnError

End Function

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
Report would look like this:
New Group
Jeffrey CoachmanMIS LiasonCommented:
I also feel that what Nick first posted should also work for you... is basically the same idea I had, ... (create an artificial "Sort" field)
The actual data should have no relevance to this problem.
What would I make my group header field?

The two statements are in conflict as far as receiving help is concerned.

There's a boatload of things that can get done in the query that powers the report.
For fun, let's say that the first column is ID

We can create a nice little Public Function call isODD

Public Function isOdd (ID as Long) as Boolean
If ID mod 2 = 0 then
    isOdd = False
    isOdd = True
end if
end Function

and in our query we can add a column CheckOdd
and in the report we can then group by CheckOdd and sort by ID and we'll get two sections, the odds and the evens and sorted by ID.

As long as you can construct something that will work to flag the sections and then sort by the ID's after, you're golden.

But without real data it's hard to make suggestions.
compuzak1Author Commented:
Ok, thanks everyone, the responses are incredible and quick.  In the past, I have done exactly what Jeffery did in the VBA, I run the vba code in a form and create a table with the results that are then used by report called by this same form.  This has worked in the past and allows preview as well.  I was just hoping there was a cleaner solution.  

As far as Nick67 goes, the data changes ALL the time, the report can not in any way be dependent on the data.  I just gave you the example, so that you could see what I desire.  The general requirement is to order the report by a unique sequence number but to subtotal any time the 2nd column changes (but don't sort by this field).  I definitely was not clear enough in my write-up.  Can't figure out a way to create a function or variable that would order the data the way I want (without using VBA).

For the record, hiding the subtotals is not working for the footer, works great for the header, but I can't figure out how to access the new data record that is causing the break, the fields I have on the report all refer to the previous row so the footer thinks the 2nd column has not changed.  Anyway, that would have been cleaner, but probably just going back to the VBA option unless you guys have some other ideas.

Again, thanks so much for the prompt responses.
As far as Nick67 goes, the data changes ALL the time, the report can not in any way be dependent on the data.

Well, then you are pretty much stuck with creating a 'temptable' to stage your data, because if you can't work out a stable function that will create what you need, then you have to do it by hand

Although, given that you have controls--and those are certainly dependent on the fields and datatypes remaining stable--the report is dependent on the data already.
As @Jeff has already suggested, you can add actual columns to the underlying tables and by code or by hand add data that you can group and sort by as needed.

You can set grouping/sorting in code
Me.GroupLevel(x)= "SomeField"  but the problem is that you don't have the fields to get what you want from the raw data -- and don't seem to be able to synthesize the needed fields either.
I think you are stuck, then.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
What you are asking for is what most people would call a recap.  In a report, that is usually a subreport in the main report's footer.  This is a no code solution.  Use the same query as the main report but make the subreport sort and group differently.  In most cases, this recap is significantly smaller than the main report because it is just totals with no detail.

For those who would understand better with a concrete example.  Take a list of employees sorted by last name.  Then in the report footer, add a subreport that sorts and groups by department and lists all the departments with just a count of their employees and a sum of the payroll amount, no detail.
compuzak1Author Commented:
I just went with the VBA option to create a temporary extract file used in the report.  Thanks for everyone's input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.