Sorting Question

[EllieFant]

In my database I have a jobs table listing the jobs that each department has. To be politically correct, I need to put the manager of the department at the top of the list for his/her department with the secretary immediately under them and then each job position in alphabetical order following them. My problem is that most job descriptions are alphabetically - before Manager and Secretary. To solve this issue, I put two blank space before the word manager and one before Secretary. This works great for sorting, but looks TERRIBLE in reports as it shows the lines not lining up.

What is the best way to make sure the manager shows up on top of his/her staff with the secretary under him/her like I described above and still have the names all line up in a nice straight line. I thought about having a sorting field, but didn't want to double the work of entering a job title by entering it twice. I thought about making a field in the query that would remove the spaces and have it be what was showing on the report, but have the report sorted on the actual Job Title field, but wasn't sure how to say "Get rid of those dumb spaces".

Thanks for any assistance that you can provide.
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[WildHare]

Simple. Add a "sort field" to your job table. Set the MANAGER jobs to 2, the Secretary's to 1, and leave everyone else blank or set to zero.

Sort by this field, IN DESCENDING ORDER, just don't print it.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[EllieFant]

Thank you wildhare...a simple solution.

I will keep looking into removing the leading spaces with code since I have other fields that I have done this same thing with (well actually the original database person did).

Thanks again!
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[WildHare]

Adding leading spaces to data values to make them sort differently is not a good thing. If you ever see this other database person, you have my permission to whack him/her on the skull with a large cudgel, or even, given the season, a shillelagh.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[EllieFant]

Well the database person is my boss, so smacking him may not be in the best interest of my job security....he actually did a real good job making the database work since he knows very little about what he was doing.

Besides making a sorting field for the Job Title (Department and Department Section), do you have another way of making the sort order the way it should be? Removing the spaces won't be hard, but I am not real thrilled at the thought of adding 3 new fields, populating all 800 (times 3) of them and changing all my reports...but I will if I need to.

Thanks for your assistance! Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[WildHare]

Changing the data shouldn't take more than about 15 seconds.

Update queries.

And why three fields? Did I come in in the middle of this movie?

PS - every boss needs a whack with a stick every now and then.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[SgtJarrow]

If you must keep the spaces, and I think WildHare is leading you down the CORRECT path, you should be able to use the Trim function anywhere you want the display to be correct....Trim will strip any leading and trailing zeros...check the help file. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[EllieFant]

There will actually be 4 added fields, but one of them is only for two records. Here is the explaination.

We have about 30 departments. Each department falls under Plant Management or Project Management. Because Project Management is the very tip top of the ladder, it needs to be on top and alphabetically it would be last. I could eliminate this by sorting descending.

Next we have the departments. Again, Project Management needs to be on top of the Project Management chain of departments, and Plant Management needs to be at the top of their food chains there is a space in front of their name. (This is one column)

Next we have the Department Sections. Using Engineering as an example, we have Design Engineering, Engineering Administration, and Systemization. The manager (being the top dog of this group needs to be on top) is in Engineering Adminstration. Since Design Engineering comes before Engineering Adminstration, there is a space in front of Engineering Management to bring it to the top. (This is column 2)

Now we come to the sorting of the employees within the Department Sections. Those that are managers/supervisors need to be first in the list, then the secretaries...therefore there are spaces before the names so they are in the right order of authority. (This is column 3)

This database was created in one huge table, with field names like Emp # (yes, it had a space and the # symbol). I have since broken things up into normalized tables and removed the symbols that shouldn't be used in names. Appears my next step is going to be adding sorting columns so that the food chain is in the right order :-(

Thanks for all your help...I appreciate the quick and informative responses.
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[WildHare]

Ahhh.. I see, said the blind man.

Yes, in your case, if I read it correctly, you will need a several-level sorting capability

1) Plant or Project Management - "Enterprise" sort level
1A) Department Level within Enterprise level
1B) Section Level - within Department
1C) Employee Level - within Section

Thus:
Project Management - EntSortLevel 1
Project Management - DeptSortLevel 1
Engineering Mgmt - SectionSortLevel 1
Joe Schmoe - Manager - Employee Level 1
Sally Schmoe - Secretary - Employee Level 2
Fred Sliderule - Engineer - Employee Level 3
Bill O'Lading - Engineer - Employee Level 3


What I might do in this process is build some lookup tables that have the higher level values and their sort values, and then link to them with foreign keys.

I assume this is an EMPLOYEE related table, correct? So every employee has a SECTION, a DEPARTMENT, and an ENTERPRISE that goes with him.

Your task is to sort the employees, apart from Managers and Secretaries, within each Section, each Section within the Department with Engineering first, Each Department within Project or Plant Management, again "Engineering" first, and everybody else after?

?

This shouldn't be that tough to do, with the appropriate update type queries...

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MichaelRed]

A somewhat more formal process would rely on a quasi linked list, where each empoyee (record) would have an "Id" of their own and a "ReportsToId" for their supervisor. Setting up the reports is then just doing various levels as grouping. It is a fairly common technique, but does require / expect an aggressive maintenance, particularly at the 'higher' levels, since that when supervisory personnel change, it is 'immediatly' necsessary to update all of the employees who (now) report to someone else. Many examples of this exist in ogranization databases. MichaelRed
m.red@att.net

Searching for employment in all the wrong places