Group Sorts in Rosters - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Group Sorts in Rosters

Kent Jamison

4 years ago

I have a personnel database that I maintain for work. I've created a contact roster for our company, which has a hierarchy (from biggest to smallest) of offices, divisions, branches and teams. Each level has leaders.
How do I force Access to sort each the roster so each level's leader shows up first in their group? I can't sort or group records alphabetically by last name because each leader has a different last name. Sorting by position title also doesn't work because "Team Chief" comes at the end of the alphabet, when I want that person to show up first in its group. Likewise, "Division Chief" shows up after "Analyst."
I was thinking I could add a rank order field (not visible in the report) to each person's record, but that would be time consuming to add that field and value to each record in the database. I have 100+ active positions to deal with. Is there a simpler way?
Any other ideas?
--Kent Jamison

Juan C Rivera @Reply

4 years ago

open the table add a field anyname (number) - give leaders that you wish on top a value of 1 all others maybe 10 room for grouth...
sort by the number field. the report should sort by value also making the leadres rise to the top of the report. real easy in a query.
let me know how it works many ways to get the result you are trying. oh forgot in the query dont check mark the value this way it will not show up.

best of luck.

	Scott Axton @Reply 4 years ago
Your best bet is through a Query. How is / are you tables set up? Do you have a lookup table for your Positions? Are we talking about sorting for in a form or in a report?

Kent Jamison OP @Reply

4 years ago

Yes, I'm referring mainly to grouping and sorting values in a report. I don't know how to set up a Lookup table in a report yet. I have most of my data set up in a main contact table. But the table is getting big and I'm also considering breaking up my table into smaller tables. I just finished the beginner courses. I'm eager to learn more about relationships in the Expert series before I start dividing tables. I have a few basic relationships set up with primary and foreign keys. But only b/w single lines. I haven't yet figured out how to add more than one relationship between two tables yet. Right now my database resembles a hub and spoke approach. Core data is in the middle. Extra details in outer tables so I don't repeat a lot of info.
I hope that adds a bit of info so you can help me.
--Kent J.

Kent Jamison OP @Reply

4 years ago

A bit of return and report here. I tried what Juan and Scott said. I figured it out with a small tweak. Here's what I had to do to make it work.
If your report is based on a query, like mine are, you have to add a step to what Scott and Juan suggest above.  First, add the rank order field to your table.  Then in your query, in query design, make sure check box "show" is checked.  Run the query after adding your new sort field. Save the query. Then, open the report and use the new rank/order field to sort/group data.  NOTE: You can choose any field to sort by in a report--even if it's not seen/used in the report itself.  The key is it has to be available in the report's existing fields list.  But if its not "shown" in the query design, by clicking the "show" box, you won't be able to see it in the report's existing fields list and thereby use it to sort data in your report.
I hope that helps someone else.
--Kent J.

	Juan C Rivera @Reply 4 years ago
and thats part of the fun I bet you will never forget this and why you did this yourself with just a little help. Glad it worked for you. Juan

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View