If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup with loop
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? |
#2
|
|||
|
|||
Lookup with loop
JE wrote:
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? 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 |
#3
|
|||
|
|||
Lookup with loop
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. -- Duane Hookom Microsoft Access MVP "JE" wrote: 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? |
#4
|
|||
|
|||
Lookup with loop
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: kenwsheridanatyahoodotcodotuk 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 "JE" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|