Combining Two Queries

[JudyB]

I have two queries that both give me the information I am looking for.

1. Current Dept Query which has the following fields and tells me each
employee's current Department:
EmpId LastName First Name DeptName
34 Jones Tom Accounting
34 Brown Larry Accounting

35 Smith John Purchasing


and

2. Weeks Service by Department which has these fields and tells me the
number of weeks an employee has spent in each Department:
EmpId DeptName WksService
34 Accounting 417
34 Admin 209
35 Accounting 135
35 Admin 156
35 F/S 52
36 Accounting 4

I am trying to create a query that combines these two queries and will give
me the number of weeks each employee has spent in his/her current Department.
For example: (34) Tom Jones is currently working in the Accounting
Department and has a total of 417 weeks in that Department.

I have be unsuccessful in getting the information tied together. Can anyone
tell me if this is possible and how to get it done? I want to be able to run
reports by Department showing each employeee's service time. Thanks in
advance. Any help is much appreciated.

 

[Marshall Barton]

I have two queries that both give me the information I am looking for.

1. Current Dept Query which has the following fields and tells me each
employee's current Department:
EmpId LastName First Name DeptName
34 Jones Tom Accounting
34 Brown Larry Accounting

35 Smith John Purchasing


and

2. Weeks Service by Department which has these fields and tells me the
number of weeks an employee has spent in each Department:
EmpId DeptName WksService
34 Accounting 417
34 Admin 209
35 Accounting 135
35 Admin 156
35 F/S 52
36 Accounting 4

I am trying to create a query that combines these two queries and will give
me the number of weeks each employee has spent in his/her current Department.
For example: (34) Tom Jones is currently working in the Accounting
Department and has a total of 417 weeks in that Department.

Does this do what you want?

SELECT D.*, W.WksService
FROM [Current Dept Query] As D
INNER JOIN [Weeks Service by Department] As W
ON D.EmpId = W.EmpId
AND D.DeptName = W.DeptName

 

[ErezM via AccessMonster.com]

hello
first, i hope it's a typo that gave Tom Jones and Larry Brown the same EmpID.
now to your question, if you create a third query that relates the 2 queries
by both empID and DepName then your'e done
it should look like:
SELECT qry1.DeptName, qry1.FirstName, qry1.LastName, qry1.EmpId, qry2.
WksService
FROM qry1 LEFT JOIN qry2 ON (qry1.DeptName = qry2.DeptName) AND (qry1.EmpId =
qry2.EmpId);

good luck
Erez