Lookup with loop

  • Thread starter Thread starter JE
  • Start date Start date
J

JE

I am a novice in programing with MS Access and all db's consist of only of
simple queries, make tables, and procedures. Below is my objective. Is this
possible without using VB? If not, would someone direct me to a useful
site/post that would get me started. Many thanks!

Table:

Employee Mgr Initials
---------- -------------
AAA XYZ
BBB XYZ
CCC AAA
DDD HHH
EEE H12
FFF CCC
GGG XYZ
JJJ BBB
(etc.)

Objective: Enter Mgr's Initials (e.g., XYZ) and return everyone who reports
up to this person. Desire report would display:

Mgr Initials Employee Employee Employee
------------ ------------ ------------ ----------
XYZ AAA CCC FFF
XYZ BBB JJJ
XYZ GGG

I attempted to use link the same table multiple times in a simple query to
no avail. Can this forum offer any suggestions?
 
JE said:
I am a novice in programing with MS Access and all db's consist of only of
simple queries, make tables, and procedures. Below is my objective. Is this
possible without using VB? If not, would someone direct me to a useful
site/post that would get me started. Many thanks!

Table:

Employee Mgr Initials
---------- -------------
AAA XYZ
BBB XYZ
CCC AAA
DDD HHH
EEE H12
FFF CCC
GGG XYZ
JJJ BBB
(etc.)

Objective: Enter Mgr's Initials (e.g., XYZ) and return everyone who reports
up to this person. Desire report would display:

Mgr Initials Employee Employee Employee
------------ ------------ ------------ ----------
XYZ AAA CCC FFF
XYZ BBB JJJ
XYZ GGG

I attempted to use link the same table multiple times in a simple query to
no avail. Can this forum offer any suggestions?
Click to expand...

The most important thing is to get your tables right. First thoughts
are that you'd want two tables: one which contained all employees
(managers are employees, presumably). Each record would have a unique
identifier (probably an autonumber) as Primary Key. The second table
would "associate" the identifier of an employee who is a manager with
the identifier of a managed employee, having a separate record for each
relationship. You could also add fields for the dates on which the
relationship began and ended if that was useful. Like this:

tbl_Employee:
=============
employee-id: autonumber (identifier = Primary Key)
emp-given-name: text
emp-family-name: text
emp-initials: text
emp-favourite-beer: text {or any other field(s) you like}

tbl_management:
===============
relationship-id: autonumber (Primary Key, optional)
emp-mgr: long (to hold the manager's employee-id as a Foreign Key)
emp-sub: long (to hold the subordinate's employee-id as a Foreign Key)
rel-begin: date (date of relationship start)
rel-end: date (date relationship ends)

You'll need to create relationships between the Foreign Keys and the
corresponding Primary Key (employee-id) by dragging in the Relationships
window in Access (preferred), or at least in the query builder.

Then you'd run a query based on these tables to list all management
relationships. You can use "filter-by-form" (built-in and easy to use -
see Help and experiment) to restrict output to just the records needed.
Later, you could build your own custom form if you needed refinement,
and use event procedures (VB or macros) to create and apply the filter.

HTH

Phil, London
 
Only one of your managers has multiple subordinates. What would you expect if
AAA was the Mgr of KKK also?

You can use LEFT or RIGHT self JOINS but doubt you will get an adequate view
of your data.

Since this query is the record source of a report, I would probably use
subreports. Actually, I would use Visio to create an org chart from the
Access data.
 
What your report amounts to is a classic database problem, that of a 'Bill of
Materials' or 'Parts Explosion', so called because it commonly arises in the
context of assemblies which can be mad up of other assemblies and so on right
down to the base parts. Your scenario is analogous to this in than employee
reports to a manager, who in turn reports to another manager and so on.
Yours is in fact a much simpler model because one employee can only report
directly to one manager whereas an assembly can, and normally will, contain
more than one sub-assembly.

Because of the simplicity of your scenario it is in fact is a 'tree' (in the
mathematical sense) as it can be mapped out diagrammatically as a 'graph'
(again in the mathematical sense) in which there is only one path between any
two nodes, in the same way as there is only one path from a leaf on an oak
tree to the base of its trunk.

The most efficient way of modelling a tree is Joe Celko's Nested Set Model,
which he describes at:


http://www.intelligententerprise.com/001020/celko.jhtml


In fact the scenario he uses to illustrate it is an organisational structure
which closely parallels yours.

However, while I'd recommend you read Joe's article, I think you may find
implementing this a little beyond your level of experience, and you'd be
better off relying on the 'adjacency list' model, which is in essence what
Phil describes. Strictly speaking, as one employee reports directly to one
manager only you can do it all with one table, but using the separate
'adjacency list' table to model the relationship does have some advantages.
Note that the primary key of Phil's tbl_management table can be a composite
one of the emp-mgr, emp-sub and rel-begin columns, discarding the autonumber
column. If you do keep the autonumber column a unique index should be
created on these three columns (in conjunction, not individually). If you
are only modelling an emplyees' current position rather than there employment
history then the rel-begin column need not be part of the key.

When it comes to a query for your report things get tricky, because to cater
for an arbitrary number of levels in the hierarchy the query needs to be
recursive. However, no such animal exists as far as I'm aware. Recursive
querying is theoretically well covered in the literature, however, and it is
possible to simulate the steps involved. As it happens I did this once for a
magazine column written by a contact of mine, and the solution has very
recently been republished in a book celebrating 30 years of the magazine in
question. Unfortunately my file has long since disappeared from its web
site, but I can send you a copy direct if you mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

The file which simulates the recursive querying probably won't be a lot of
use to you in fact, but Zipped with it is another 'PartsTree' file which does
produce a report in a horizontally oriented layout, which might well fit the
bill. This relies on a straightforward query which joins the adjacency list
table to itself a fixed number of times in LEFT OUTER JOINS. Consequently it
is limited to a fixed maximum levels of hierarchy, nine in fact. This
doesn't sound a lot but I'd imagine it will cope with most organisational
structures for which Access would be sensibly used.

Ken Sheridan
Stafford, England
 
Back
Top