Need to show a record using an IIF statement

[jp3scr]

Hello, I am developing a project tracking database for my department. I am creating an active list of the projects and need to show the project manager for each project.

There are a few titles the project manager can be. I've assigned an ID to each of the those titles.

Program Manager = 1
Lead Manger= 2
Secondary Manager = 3
Support 1= 4
Support 2= 5
Support 3 = 6
Support 4 = 7
Business Rep = 8

For some projects there may be mutiple people assigned, I only need to see the Program Manger, if no program manager then the lead manager, if no lead manager than the business rep.

Currently if there are 3 different people assigned to a project the active project query is showing 3 of the same projects, one for each member assigned. I only need to see the top level person on the project.

thoughts??

 

[bob fitz]

Re: Need to show a record using an IFF statement

Can you show us the SQL for the active project query

 

[plog]

You've not given enough information about your current table structure so that I can help your specific situation, so I will tell you how it should be done.

You should have 4 tables, Projects, Staff, Titles, ProjectManagers. Projects is the existing main table you have and holds all the data about a project (i.e. start date, deadline, name, etc.). Staff should be an existing table that has all the people eligible to be a Project Manager (i.e. first name, last name, dept, etc.) Titles is a table that holds the data you listed above, however, it also has a priority field which is a Double and orders the importance of each title. It has this field, because the ID field you currently have should be an autonumber field that doesn't mean anything. This is what the Titles table should look like:

TitleID, Title, TitlePriority
1, Program Manager, 1
2, Lead Manager, 2
...
...
7, Support 1, 7
8, Business Rep, 8

I know it looks like TitleID is redundant and means the same as TitlePriority, but it isn't. TitleID is just a unique way to reference each Title. TitlePriority, is how you will prioritize each title. Suppose in 3 months a new title is created called 'Tertiary Manager' who falls below the Secondary Manager but above the support people. To make this title you'd have to touch every record below Secondary Manager so that they priority landed correctly. With a seperate TitlePrioty field you can just shove them in the table, take whatever ID is assigned and set their priority correctly. Like this:

TitleID, Title, TitlePriority
7, Support 1, 7
8, Business Rep, 8
9, Tertiary Manager, 3.5

It also will make reclassifying a title easier.

The last table, ProjectManagers is a join table that links your Staff to your Projects tables and allows you to designate the title of each on a particular project. It would have 3 numeric fields and look like this:

ProjectID, StaffID, TitleID
1,7,1
1,6,2
1,8,4
2,7,1
2,5,2
3,6,1


This allows you to assign multiple people to a project, their title on a project and will allow you to build a query to determine the highest ranking staff member on a project.

 

[jp3scr]

SELECT [Q_Active Project List].[Status Change], [Q_Active Project List].Proj_Type, [Q_Active Project List].Project_Size, [Q_Active Project List].Primary_PM, [Q_Active Project List].RAG_Status, [Q_Active Project List].Target_Imp_Date, [Q_Active ProjectList].Adj_Target_Date, [Q_Active Project List].Proj_Description, [Q_Active Project List].Impact_Bus_Area AS [Q_Active Project List_Impact_Bus_Area], [Q_Active Project List].Impact_Bus_Area.Value AS Expr1, [Q_Active Project List].Sponsor, [Q_Active Project List].Assigned_PMAS Expr2, [Q_Active Project List].Weekly_Update, [Q_Active Project List].Reason_RAG_Changed, [Q_Active Project List].Reason_Target_Changed, [Q_Active Project List].Proj_Name, [Q_Active Project List].Proj_Status, AssignPMs.PM_Role, AssignPMs.Proj_Mgr, [Q_ActiveProject List].[Impacted Business Area(s)] AS Expr3
FROM (Proj_Name INNER JOIN [Q_Active Project List] ON Proj_Name.[Proj_Name] = [Q_Active Project List].[Proj_Name]) LEFT JOIN AssignPMs ON Proj_Name.Proj_Name = AssignPMs.Proj_Name
WHERE ((([Q_Active Project List].Proj_Status)="Active") AND ((AssignPMs.PM_Role)=1 Or (AssignPMs.PM_Role)=2));

 

[jp3scr]

I do have the tables set in that fasion. I have a project manager table listing all the mangers names, info etc.

I then have a table where the project assignements are made including their role.

I then have a table with the role ID # and their descriptions, exactly how you described above. How will I only show program manger, if none then only lead PM, if none then only Business Rep in that order

 

[plog]

Can you list all the tables and the relevant field names of each? With that I could guide you through creating a query to get that information.

 

[jp3scr]

Project Manager Table
ID
First name
Last Name
Full Name
Manager Name
Phone Number

Project Assignment Table
ID
Project Manager
Project Name
Project Number
Project Type
PM Role

PM Role Description Table
ID
Description
Numeric Value

Project Table
Project Name
Project Number
Project Status
RAG Status
Last Update
Target Date
Adjusted Target Date
Reason RAG Changed
Reason Target Changed
Weekly Update
there are 10-15 additional fields, but not relevant to the active project report

 

[plog]

The following code assumes that these fields are numeric:

Project Assignment Table
ID, Project Manager, Project Number, PM Role

PM Role Description Table
ID, Numeric Value

Project Manager Table
ID

Further it assumes the Project Assignment Table links its PM Role field to the Numeric Value field in PM Role Description Table and its Project Manager field to the ID field in the Project Manager Table.

Last, the Numeric Value of "Program Manager" is 1, the Numeric Value of "Lead Manager" is 2 and the Numeric Value of "Business Rep" is 3.

First, you will need to build a sub-query to get the lowest Numeric Value of each project. Use this SQL to create that sub-query and call it "sub_ProjectLeaders":

SELECT [Project Assignment].[Project Number], Min([PM Role Description].[Numeric Value]) AS HighestStaffPriority
FROM [Project Assignment] INNER JOIN [PM Role Description] ON [Project Assignment].[PM Role] = [PM Role Description].ID
GROUP BY [Project Assignment].[Project Number];

Then you will use that query in a new query called "ProjectLeaders", this is that SQL:

SELECT [Project Assignment].ID, [Project Assignment].[Project Manager], [Project Assignment].[Project Number], [Project Assignment].[PM Role]
FROM (sub_ProjectLeaders INNER JOIN [Project Assignment] ON sub_ProjectLeaders.[Project Number] = [Project Assignment].[Project Number]) INNER JOIN [PM Role Description] ON (sub_ProjectLeaders.HighestStaffPriority = [PM Role Description].[Numeric Value]) AND ([Project Assignment].[PM Role] = [PM Role Description].ID);

This is the seed of what you want. From here, you can open the ProjectLeaders query in Design View and bring in all the related tables and link them to the Project Assignment table to get all the related Names.