Grouping Query Data

[okerkula]

I have a query with grouped data that consist of titles. All titles with the
same name are grouped together and each group is in order alphabetically -
example;
[Title]
Brownie
Cadette
Junior

I would like to know how to maintain the groups but show them in a different
order - example;
[Title]
Junior
Cadette
Brownie

Your help is much appreciated.

thanks
Olivia

 

[John Spencer MVP]

Easiest way is to add a new table with two fields
Title
DisplayOrder

Then join that table to the Title field you have in your current table and
sort by (or group by) the Display order field

Otherwise you are going to need an expression to sort by or group by. One
such expression could be

Instr(1,"Junior Cadette Brownie",[Title] & "")

That expression will return zero if Title is null or not in "Junior Cadette
Brownie". It will return 1, 8, or 16 if the Title is in the string.

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

 

[okerkula]

F -A-B-U-L-O-U-S!! I used your expression which assigned each group a number
based on the order I wrote the names in - and then I sorted the assigned
numbers and wahlah!! - It grouped the names just the way I wanted!!

Thanks so much for your help and quick reply - I love this forum!!

Olivia
-----------------------------------------

Easiest way is to add a new table with two fields
Title
DisplayOrder

Then join that table to the Title field you have in your current table and
sort by (or group by) the Display order field

Otherwise you are going to need an expression to sort by or group by. One
such expression could be

Instr(1,"Junior Cadette Brownie",[Title] & "")

That expression will return zero if Title is null or not in "Junior Cadette
Brownie". It will return 1, 8, or 16 if the Title is in the string.

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

I have a query with grouped data that consist of titles. All titles with the
same name are grouped together and each group is in order alphabetically -
example;
[Title]
Brownie
Cadette
Junior

I would like to know how to maintain the groups but show them in a different
order - example;
[Title]
Junior
Cadette
Brownie

Your help is much appreciated.

thanks
Olivia