I want a union - or something like that? (1 Viewer)

[Lissa]

Hi everybody.
I have three queries.
Query1 contains employee ID, employee name, hours worked, utilization percentage and project billing type is "direct". (127 records returned)
Query2 contains the same information except the project billing type is "indirect". (41 records returned)
Query 3 contains the same information except project billing type is "potential". (10 records returned)

Now if I create Query4 based on Query1, Query2 and Query3 joined on employee ID - the result is only employees found in all three queries. (4 records). My problem is that I want to include all employees.... so that I have an hours worked and utilization % for direct, indirect and potential projects for each employee.

If I remove the join, I will get all employees but in duplicated record format.
Is it possible to create a fourth query that will combine all queries and use nulls for records that don't have matching records in the other queries? Does that make sense? I'm not sure what kind of query terminology that would fall under so I can do further research.


Any ideas are greatly appreciated!
Thanks

 

[redneckgeek]

Given the proper table structure, you should be able to do this with one query.... or do you have seperate tables for each billing type?

 

[Lissa]

I have a data table that stores hours worked against each project and a project table that stores billing type(direct, indirect,potential), manager, charge code...
I had a query and report that listed total hours and utilization per month but then managers wanted to throw in the composition of the total hours.. x hours of direct, x hours of indirect, x hours of potential projects per month...

 

[Lissa]

Thanks Redneckgeek! Your response actually made me think of something else. I created another query off of my data table and was able to use a cross tab query on it to get it in the exact format I want!