I created table that contains membe

[Greaser]

I created table that contains membership information:

ID -> [AutoNumber]
GroupNo -> [Text] 3 characters
Position -> [Text] 2 characters
LastName -> [Text] 50 characters
FirstName -> [Text] 50 characters
Age -> [Number] Byte

The [GroupNo] represents the group in which the members belong. The [Position] represents the position in the group.

Here are some examples:

McLeod Patricia 012 03 20
Campeau John 012 01 31
Armstrong Jack 012 02 9

Hahn Liz 023 03 32
McNiel George 023 02 43

Andrus Audrey 035 03 7

The members may be stored in the table in any order:

McLeod Patricia 012 03 20
Hahn Liz 023 03 32
Armstrong Jack 012 02 9
Andrus Audrey 035 03 7
Campeau John 012 01 31
McNiel George 023 02 43

I want a report to display the list grouped by [GroupNo] and sorted by ascending [LastName] of the member with the lowest [Position] of every group:

Campeau John 042 01 31
Armstrong Jack 042 02 9
McLeod Patricia 042 03 20

McNiel George 023 02 43
Hahn Liz 023 03 32

Stevens Audrey 005 03 7

Can you help me with the grouping and sorting?
Thanks,
John

 

[mdthornton]

In the report definition open the sorting/grouping dialog box and add GroupNo, LastName and Position. Reselect GroupNo and in the bottom section select Yes for the group header and/or footer to achieve the spacing that you want. Good Luck,
Mike

 

[Greaser]

Thanks for your reply Mike, but your proposed solution doesn't sort the report in ascending [Last Name] of members with the lowest [Position] of each group.
Your solution gives me the following result:

Stevens Audrey 005 03 7

McNiel George 023 02 43
Hahn Liz 023 03 32

Campeau John 042 01 31
Armstrong Jack 042 02 9
McLeod Patricia 042 03 20

I would like the report to look like this:

Campeau John 042 01 31
Armstrong Jack 042 02 9
McLeod Patricia 042 03 20

McNiel George 023 02 43
Hahn Liz 023 03 32

Stevens Audrey 005 03 7

Thanks,
John

 

[mdthornton]

Just move LastName to the bottom of the sort/group spec.
If you want to group GroupNo together this becomes your primary sort key and you can only control whether this is ascending or descending.

So the best you can do would be:

Stevens Audrey 005 03 7

McNiel George 023 02 43
Hahn Liz 023 03 32

Campeau John 042 01 31
Armstrong Jack 042 02 9
McLeod Patricia 042 03 20 Good Luck,
Mike

 

[Greaser]

Thanks again Mike,
Do you know of a way with Access Basic that I could
get the report grouped and stored the way I want?
Thanks,
John