Unalphabetical Roster

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

Hello,

I'm trying to create a database that keeps a roster of employees. the
problem is, the report that acts as a directory will not be alphabetical. It
needs to be sorted by department, then a hierarchy within that department.
I've tried numbering each department, then numbering each job within that
department and sorting it that way. Unfortunately, that is not a practical
way to keep the database updated. As people move in and out and jobs are
created and deleted, it is difficult for the data entry person to know the
convoluted numbering system. Has anyone tackled this problem before? I will
also be trying to do an Org chart with this data.
Thanks,
Lisa
 
Just what are your layering requirements? I never heard of a directory by
department, job, then employee. Only department and employee or job and
employee.
 
Hello,

I'm trying to create a database that keeps a roster of employees. the
problem is, the report that acts as a directory will not be alphabetical. It
needs to be sorted by department, then a hierarchy within that department.
I've tried numbering each department, then numbering each job within that
department and sorting it that way. Unfortunately, that is not a practical
way to keep the database updated. As people move in and out and jobs are
created and deleted, it is difficult for the data entry person to know the
convoluted numbering system. Has anyone tackled this problem before? I will
also be trying to do an Org chart with this data.
Thanks,
Lisa
Click to expand...

Hi Lisa,

It sounds like maybe your database isn't normalized. You should have
separate tables for Department, Job and Employee.

In the Department and Job tables, you can add a field for
SequenceNumber. When you design your report, you can join all three
tables together, and using Sorting and Grouping to sort by your
sequence numbers (they don't have to be visible on the report).

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Let's start with your highest level first. I would think that alpha by
department would be normal. How will people work with the report if the
departments are sorted in some other order based on a numeric code?
Wouldn't they then need to know the code to find the department? If this
other sort order is normal for your company, just store the sequence number
in the department table and use that for sorting.

As for jobs, the same question applies, if they are not alphabetical, what
is the order? Do you need a grouping code that adds a level between
department and job so that you can group all the supervisory jobs and all
the engineering jobs and all the clerical jobs regardless of their various
job titles?
 
Thank you all for your advice. I think what I will do is create lookup
tables for the Department and jobs since each department will have a chief
and deputy, but the rest of the department members will vary. I'll just rank
each department numerically to get the report in the correct order. That
way, the data entry person who will maintain this can select the department
and job in the language they are used to.

Thanks!
 
Back
Top