Sorting by Manager

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that I need to sort. Each loan in the database has 3 people
associated with it: Sr. Manager, Manager and Analyst. I want to pull the
report and have it sort like the example below. Is this possible?


Smith
Manager Loan 1
Loan 45
Sr. Manager Loan 22

Jones
Analyst Loan 12
Manager Loan 4
 
Use the sorting and grouping box in your report design view. You would need
to include a group header for Analyst.

I'm confused though. Loan one has a manager, but no sr. manager? If so,
you would need to Sort and group by analyst, then create a field to pull
either Sr. Manager or Manager (whichever is not blank) and sort by that.

Is "Smith" an analyst, manager, or Senior manager? If an Analyst, then what
is "Jones"?

I'm not sure I understand your order.

I'd expect to see:

Sr. Manager Wilson
Manager Smith

Analyst Jones
Loan 1
Loan 2
Loan 3

Analyst Bing
Loan 4
Loan 5

Manager Hanson

Analyst Gray
Loan 6

Sr. Manager Jensen
Manager Klein
..
..
..
 
Try this first, the simple solution that will return this layout
===============================
Smith Manager Loan 1
Loan 45
Sr. Manager Loan 22

Jones Analyst Loan 12
Manager Loan 4
================================
1. Create a report with the three fields in the detail section
2. Open the sorting and grouping, in the menu select view > sorting and
grouping, select all three field by the order you want them to sort
Name, Job title, Loan
3. In the Job title field, and the Name field properties, set the
HideDuplicates Property to yes
 
In the database I have the following example:
Loan Sr. Manager Manager
Analyst
Loan 1 Carter Smith
Gracey
Loan 45 Carter Smith
Loan 12
Jones
Loan 22 Smith Wood
Loan 4 Wood Jones
Jones

I want to have the report like this:

Carter
Sr. Manager
Loan1
Loan 45
Gracey
Analyst
Loan 1
Jones
Manager
Loan 4
Analyst
Loan 4
Loan 12
Smith
Sr. Manager
Loan 22
Manager
Loan1
Loan 45
Wood
Sr. Manager
Loan 4
Manager
Loan 2

I have no problems building the report and getting all of the fileds that I
need but I can not get it to sort in the way I need it to. The hideduplicates
field helps but still doesn't get all of the desired results. Can you help?
 
You need to normalize your table using a union query:

SELECT Loan, [Sr Manager] as Person, "Sr. Manager" as [Position]
FROM tblLoans
UNION ALL
SELECT Loan, [Manager], "Manager"
FROM tblLoans
UNION ALL
SELECT Loan, [Analyst], "Analyst"
FROM tblLoans;

You can then create your report from this union query.
 
Back
Top