Yet another query question...

[Guest]

I have a query where I am trying to pull the Project Number, The Resource
Role and the Actual Hours worked for the month.

I have seven roles that I track and I want each of them to appear in the
results whether there are hours applied or not.

I would think this would be as simple as using a Left Join between my
Tracked Roles table and my actual hours table but that doesn't work because
all of the role groups appear "somewhere" in the table.

I'm shooting for something like this:

Project1 Role1 Actuals or 0
Project1 Role2 Actuals or 0
Project1 Role3 Actuals or 0
Project1 Role4 Actuals or 0
Project1 Role5 Actuals or 0
Project1 Role6 Actuals or 0
Project1 Role7 Actuals or 0
Project2 Role1 Actuals or 0
Project2 Role2 Actuals or 0
Project2 Role3 Actuals or 0
Project2 Role4 Actuals or 0
Project2 Role5 Actuals or 0
Project2 Role6 Actuals or 0
Project2 Role7 Actuals or 0

Any help is, as always, greatly appreciated...

 

[John Vinson]

I have a query where I am trying to pull the Project Number, The Resource
Role and the Actual Hours worked for the month.

I have seven roles that I track and I want each of them to appear in the
results whether there are hours applied or not.

I would think this would be as simple as using a Left Join between my
Tracked Roles table and my actual hours table but that doesn't work because
all of the role groups appear "somewhere" in the table.

Create a Query filtering the desired records from your table; save the
query; and left join *IT* to the Tracked Roles table.

John W. Vinson[MVP]

 

[Guest]

You got me going down the right path.

I ended up writing a query using both tables with no join pulling the
project number and role to create a list of all possible project role
combinations, than I left joined that to my actuals table and voila... worked
like a charm.

I had to use two left joins one project to project and the other role to role.

Thanks for your help on this.

m
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office