Question on sorting and grouping

[techdiva]

I'm a newbie so bear with me. I have a project report where I am sorting on the record ID and then project# and want to group all the project #'s and their sub entries together and have them stay together on a page. Record ID is just for sort purposes since the report has to sort by order of record entry. This is what I have:
sort: record ID (ascending, no group)
sort/group: Project # (ascending, group header YES, Group on Prefix Char, Group Int = 1, Keep together Whole Group)

The report is sorting correctly but my groups are breaking up. for instanace, Project P, P-1, P-2 should group together but P is printing on page 2 and P-1, P-3 printing on page 3. When I remove the ID sort, my groups work fine. It's when I put the ID in that the grouping breaks up. Can you sort and group on two different fields? What am I not doing or doing wrong? HELP!

 

[THWatson]

techdiva
Yes, you can sort and group on two different fields, or more.

What happens if you make the Project# the first sort and group, the RecordID the second sort?

I am guessing that what's happening is this...
RecordID 1 is part of Project P
RecordID 2 is part of Project P-1
RecordID 3 is part of Project P-2
RecordID 4 is part of Project P-1
or something like that.

If you want the Projects to stay together by their number, P or P-1 or P-2 or P-3, then make that your first sort and group.

Tom

 

[dhookom]

Have you tried to group first by Project and then by record ID?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[techdiva]

therein lies the problem. Yes, I tried sorting/grouping with Project # and found that it is the records that are DOUBLE ALPHA that are causing the problem: e.g. PROJECT AA is sorting and grouping underneath PROJECT A when it actually is a separate project and belongs (sorted) after PROJECT Z. (This format was in place when I got here. I personally wouldn't have designed this kind of 'numbering' system but I'm stuck with it for now.)
So, am I screwed?

 

[THWatson]

techdiva
From how you describe the "numbering" system, Projects are identified by A, B, C up through Z, then AA, BB through ZZ, then AAA, BBB through ZZZ.

I just made up a little query to see how, if what I have described above is correct, things might sort.

I brought the Project# into the query, then added a column Sort: Len([Project#]). Thus projects A though Z would have a 1, AA through ZZ a 2, etc. I added a sort Ascending for both query columns. Then, I moved the Sort column to the left of the Project# column, so that the Sort column would sort first.

The records came out
A
B
C
D
AA
BB

Could making up a custom sort column in your query, and then making it the first sort in your report, solve the problem for you?

Tom

 

[techdiva]

you're right on the money... that's the project numbering schema and that's exactly what I'm looking to do... I'm not exactly sure how to write the query. Can you post a sample string for me to play with? Any other info/instructions were be more than helpful

 

[THWatson]

techdiva
I'm assuming that your report is based on a query, and that your query already has the Project# in it.

That being the case, just add a new column to your query. Perhaps call it CustomSort.

So the field in the query would have the expression
CustomSort: Len([Project#]).

If you sort that column Ascending, it will put everything in order by the length of the string.

Then you can use that CustomSort field as the first Sorting/Grouping in your report.

Hope that helps.

Tom

 

[techdiva]

Thanks - I'll give it a try.