Grouping records in a specific order

My database is as follows:

Rank............Surname

High.............Smith
High.............Bloggs
Medium........Carter
Low..............Blaine
High.............Copp
Medium........Bates


What I need is a report that will group all the records together under their ranks.

I have experimented with it but it groups in alphabetical order:

High
Low
Medium

How can I dictate the order of the group so that I get

High
Medium
Low

Is this possible? Many thanks in advance.

Fallyhag

Since there is no way of sorting them other than alphabetically, you could change them from words to numbers (High = 1, Med = 2, Low = 3) or just create another to hold that data.

Thanks for the reply.

I like what you are saying, it makes sense. Is there a way of keeping my headings (High, Medium and Low) and assigning them values?

I have these ranks set up as a lookup field.

Fallyhag

Select [Rank Table].Name, [SurName Table].Surname from [Surname Table] INNER JOIN [Rank Table] ON [SurName Table].Rank = [Rank Table].ID
Order By [SurName Table].Rank;

This will bring the name of the Rank over and sort by rank ID

You could just use that one table and add another column call RankID something like this (This violates table normalization rules)


The just do a select statement like this.

Forgot

For this to work you need it to be a table, You can still have the reference to is as lookup, but reference the Rank Table instead of values


I would always recommend using lookup tables so when you need to change things you are only changing data and not code

Many Many Thanks for the very fast and helpful replies.

Much appreciated. Now for the experimentation

All the best

Fallyhag


ps: Great Forum!

Well after a little hair pulling and playin around I solved it via the following solution:

Created the lookup with 2 columns.

Ist column had the rank and the 2nd had the numerical value.

I then gave the 2nd column zero cm width.

I could then sort the ranks in the report using the hidden numrical values.

Does this make sense or have I gone around the houses to sort a simple problem? Suggestions/advice welcome.

Fallyhag

What ever works for you works. Ther is no one right way, there are ways that are better then others and ways that some prefer.

My preference is to not use lookup fields in the tables. I create a form that will have a combobox the is reference to another table where I will store the look values and then populate the fields this way and create a relationship between the two tables. I prefer using tables because if I ever have to add another item to my look up. All I have to do is add another record to one table and everything is done.

S-