compuzak1
asked on
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:
Data:
1 AAA
2 AAA
3 AAA
4 BBB
5 BBB
6 AAA
7 AAA
Result:
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.
Data:
1 AAA
2 AAA
3 AAA
4 BBB
5 BBB
6 AAA
7 AAA
Result:
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<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
Jim.
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
Jim.
ASKER
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.
@compuzak1
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.
JeffCoachman
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.
JeffCoachman
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.
That "Difference" could be what you sort on.
ASKER
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.
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.
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, ...so 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
Here is another technique:
(I am on my way home, ...so 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
rst.MoveFirst
strData = rst!Data
Do Until rst.EOF
If rst!Data = strData Then
bytCounter = bytCounter
Else
strData = rst!Data
bytCounter = bytCounter + 1
End If
CurrentDb.Execute "Update YourTable SET Sortx=" & bytCounter & " WHERE ID=" & rst!ID, dbFailOnError
rst.MoveNext
Loop
End Function
Report would look like this:
I also feel that what Nick first posted should also work for you...
...it is basically the same idea I had, ... (create an artificial "Sort" field)
...it 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
else
isOdd = True
end if
end Function
and in our query we can add a column CheckOdd
CheckOdd:isOdd([ID])
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.
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
else
isOdd = True
end if
end Function
and in our query we can add a column CheckOdd
CheckOdd:isOdd([ID])
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.
ASKER
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. 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just went with the VBA option to create a temporary extract file used in the report. Thanks for everyone's input.
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?
...etc
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...
JeffCoachman