Creating Call lists

[fvreg]

I am trying to create call lists which will compile one-to-many relationships.

For example, suppose I have 1000 employee records with the following fields:

Manager's Name
Manager's Phone Number
Direct Report's Name
Direct Report's Phone Number

Each Direct Report has only one designated manager, but managers may have more than one direct report. Further, for roughly half of the records, the manager and the direct report are the same person (independent workers).

When I run the report, I want it to print the Managers' information, and have the Direct Report information below. I have added an IIf query stating that if the Manager's Name is the Same as the Direct Report's name, leave all of those cells blank. When I create a report, however, the labels for Direct Report will still show but the data does not. It looks somewhat shoddy.

Does anyone have experience with lists such as these? Any ideas?

 

[edtab]

Try using conditional formatting in the detail section or your report
such that you will make the label invisible if the direct report's name is
null or blank.

 

[The_Doc_Man]

To do this right, you need two tables. A person table and a reporting table.

Each person has a personal ID in this person table, which could be autonumber unless you have something else for a personnel prime key.

The reporting table is just a self-junction table that says,

reporting ID, reports-to ID.

If a person is in the reports-to ID, that person (by your definition) is a manager. But even managers can be in this table if they in turn must report to someone else.

You join the reporting table TWICE to the person table, once to the reporting ID and the other to the reports-to ID. Then you can sort the table by reporting ID to show every person and the name of the person to whom they report. Or you can sort/group-by reports-to ID, showing the manager's name and the critical information for everyone reporting to that manager.

You can also find more about this method if you SEARCH this forum for the topic of "Junction Table" as a way to JOIN records that don't have a "simple" relationship. Your reporting structure qualifies because a mid-level manager can still have someone higher up the chain to whom to report. In other words, the reporting structure is multi-tiered within a single table.

Using the junction table as the basis of this report, you would NEVER have a blank/null as long as you limited yourself to INNER JOIN operations.