Sorting and Grouping

[mduplantis]

I have a database that is already established and working, but I need to make a new query, which is no problem. But, I need to group and sort. First of all I need to sort based on classifications. I am using design view to do this within my query. it is not ascending or decending, but instead it is based on a rank from highest to lowest classification. Kinda like the military, Generals of top, privates on the bottom. Is there a way to use an operator for this?



The second sort is based on seniority dates with in the classifications. So all the generals in a group and then within that group, the most senior to the least senior general.

This might be done in a report with group and sorting? Just not sure the best method of accomplishing this.

Thanks for any tips

[jzwp11]

You have to tell Access the order at some point. If you have many "ranks" then you should have a table that holds that information as well as the sequence of the ranks

tblRanks
-pkRankID primary key, autonumber
-txtRankName
-longRankSequence

Data in the table may look like this

pkRankID|txtRankName|longRankSequence
1|Private|8
2|General|1
3|Major|3
etc.

Then in your table that holds the information about the people reference the pkRankID as a foreign key.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRankID foreign key to tblRank
-ServiceDate

Then in your query, you can join the tblPeople to tblRank and select the longRankSequence field to do your sorting.

SELECT txtFName, txtLName, longRankSequence, ServiceDate
FROM tblPeople INNER JOIN tblRanks ON tblPeople.fkRankID=tblRanks.pkRankID
ORDER BY longRankSequence, serviceDate

[mduplantis]

Ok, that makes sense. I just did not know if there was an operator that you could simply use. But, I see now that I'll have to set the order.

thanks

[jzwp11]

You're welcome. Good luck with your project.