Organization listing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spencer - 15 Nov 2007 13:49 GMT

I have data that tells me the following: sales rep, recruiter of sales rep,
manager of sales rep and sr. manager of sales rep. I need to run a report
that will list all the sr. managers, then have all the managers under that
sr. manager, then all the recruiters under each manager and finally all the
sales reps under each recruiter.

Sales rep Recruiter Manager Sr. Manager
Mike Joe Mary Carol
Anita Sally Mary Carol
Cathy Nicole Keith Steve

It would need to look like this:
Carol
Mary
Joe
Mike
Sally
Anita
Steve
Keith
Nicole
Cathy

I have a table with the sales rep detail (SalesRep_tbl), recruiters
(Recruiter_tbl), managers (Manager_tbl) and sr. managers (SrManager_tbl).
Everyone is listed in the sales rep table, and the foreign key of the the
other tables is the sales rep ID, primary key of the SalesRep_tbl. The last
table I have is SalesRepDetail_tbl which has the data as to what manager,
recrutier and sr manager is associated with each sales rep.

I'm not great with Access but not a beginner. How do I create the queries
that will let me generate the report?

Thank you in advance. Hope this made sense.

Reply to this Message

Dorothy - 15 Nov 2007 17:51 GMT

> I have data that tells me the following: sales rep, recruiter of sales rep,
> manager of sales rep and sr. manager of sales rep. I need to run a report
[quoted text clipped - 30 lines]
>
> Thank you in advance. Hope this made sense.

Hi there. If your table SalesRepDetail_tbl contains all the pertinent
information for your report, then you just need to create a report
from that table. Use the Report wizard to create a report from the
table and use the group by sections to group by Sr Mgr, Mgr,
Recruiter, then Sales Rep.

Hope this helps.

Good luck.

Dorothy

Reply to this Message

Spencer - 15 Nov 2007 20:16 GMT

I think I simplified my table too much. The first column of the table is a
number. sales rep ID, the columns that follow have info about the sales rep,
i.e. name, date hired, level, rep type, etc... Then there are three columns
that tell who the recruiter was, the manager and the sr. manager. As follows:

SaleRepID Name Hired .... Recruiter Manager Sr. Manager
100 Joe 1/1/01 98 80 32
101 Cathy 2/1/01 99 90 32

All recruiters, managers and sr. managers are listed under sales rep ID with
their recruiter, manager and sr. manager info. I need to run a report that
will show the structure, whcih I can do with only the numbers, but I need it
to also show the employees data. I need it to look up the individuals
detailed data by number.

The report would look like this with the wizard:

32
90
99
101
80
98
100

I need it to show
32 Kevin 1/1/99
90 Sally 2/2/99 etc...

> > I have data that tells me the following: sales rep, recruiter of sales rep,
> > manager of sales rep and sr. manager of sales rep. I need to run a report
[quoted text clipped - 42 lines]
>
> Dorothy

Reply to this Message

KARL DEWEY - 16 Nov 2007 00:01 GMT

Try this --
SELECT SaleReps.SaleRepID, SaleReps.Name, SaleReps.Hired, SaleReps_1.Name AS
Recruiter, SaleReps_2.Name AS Manager, SaleReps_3.Name AS [Sr Manager]
FROM ((SaleReps LEFT JOIN SaleReps AS SaleReps_1 ON SaleReps.Recruiter =
SaleReps_1.SaleRepID) LEFT JOIN SaleReps AS SaleReps_2 ON SaleReps.Manager =
SaleReps_2.SaleRepID) LEFT JOIN SaleReps AS SaleReps_3 ON SaleReps.Sr_Manager
= SaleReps_3.SaleRepID;

Signature

KARL DEWEY
Build a little - Test a little

> I think I simplified my table too much. The first column of the table is a
> number. sales rep ID, the columns that follow have info about the sales rep,
[quoted text clipped - 71 lines]
> >
> > Dorothy

Reply to this Message