Create query to merge 2 tables?

[HeatherD25]

Hello,

I am trying to build a query (based on the results of two different queries)
to use as the data for a report.

One query returns a list of all of the projects an individual has worked on
during the year. Some fields from this query are:
EmployeeID
Period
Project
Hours

Another query returns a list of each employee and the total possible hours
they could have worked during the year.

I used to just have the first query run as the source for the report (and
had "total possible hours" as a field in it), but then I realized that I was
excluding any people
that hadn't worked on any specific projects.

How can I create a query that will give me both sets of data... If a person
has worked on a project I don't need to add anything else, but if they don't
then I need a line in the query return that lists their name and something in
"total possible hours" field. All of the other columns would be blank for
this entry.

Is this possible?

Thanks!
Heather

 

[Brendan Reynolds]

Hello,

I am trying to build a query (based on the results of two different
queries)
to use as the data for a report.

One query returns a list of all of the projects an individual has worked
on
during the year. Some fields from this query are:
EmployeeID
Period
Project
Hours

Another query returns a list of each employee and the total possible hours
they could have worked during the year.

I used to just have the first query run as the source for the report (and
had "total possible hours" as a field in it), but then I realized that I
was
excluding any people
that hadn't worked on any specific projects.

How can I create a query that will give me both sets of data... If a
person
has worked on a project I don't need to add anything else, but if they
don't
then I need a line in the query return that lists their name and something
in
"total possible hours" field. All of the other columns would be blank for
this entry.

Is this possible?

Thanks!
Heather

First you need to exclude from the second query any employees who *have*
worked on specific projects. Exactly how you do that depends on the design
of your database, it might look something like ....

SELECT Employees.EmployeeID, Employees.TotalPossibleHours FROM Employees
LEFT OUTER JOIN Projects ON Employees.EmployeeID = Projects.ProjectID WHERE
Projects.ProjectID IS NULL

This is of course a simplified example and does not reflect a real-world
database design. In practice you would be unlikely to store the total
possible hours in the employees table, and the relationship between
employees and projects would probably be a many-to-many relationship
implemented via an intermediate linking table. The purpose of this example
is merely to illustrate the technique of using an outer join and IS NULL to
select records from one table with no matching records in another table - a
"frustrated outer join" to borrow a phrase from (if I remember correctly)
John Vinson.

Once you have a query that returns employees who have not worked on any
specific projects, you can create a union query to combine that with your
original query ...

SELECT EmployeeID, Period, Project, Hours FROM YourFirstQuery
UNION SELECT EmployeeID, Null, Null, TotalPossibleHours FROM YourSecondQuery

You need the same number of columns in each SELECT clause in a union query,
hence the use of Null above.

 

[John Spencer]

It sounds as if you may only need to change the way the two queries are joined.

Try this.
In the query design view, double click on the join line between the two
queries. Select the option that gives you ALL records in the Employee hours
table and only matching records in the Project table.

If that does not work for you,
Please copy and post the SQL of your queries.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County