QueryProblem

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hello,

I'm trying to track employee movements in a big database
and I'm having problems with one aspect of my query. I
have three queries to track movements: 1) Employees in
Jan that left in Feb 2) Employees in Feb that are new and
3) Same employees that have moved to different
departments.

I am having problems writing the third query for
employees that are still employeed but have moved to
different departments. I'm running this query off of two
tables, one table for Jan and another for Feb. I have
linked the unique field of employee ID # and then under
Criteria for Dept, I put "Is Null" thinking this would
show me people employeed in both Jan and Feb that have
different department codes. Unfortunately, this isn't
working. Any thoughts? Thanks in advance for your help.

Example: In this example, I need some help for the query
to tell me that employee 003 is the only one working both
months that changed departments.

Month Employee # Dept #
Jan 001 A
Jan 002 A
Jan 003 B
Jan 004 B

Feb 002 A
Feb 003 A
Feb 004 B
Feb 005 B
 
SELECT JanEmployees.[Employee #], JanEmployees.[Dept #] As OldDept,
FebEmployees.[Dept #] As NewDept
FROM JanEmployees INNER JOIN FebEmployees
ON JanEmployees.[Employee #] = FebEmployees.[Employee #]
WHERE JanEmployees.[Dept #] <> FebEmployees.[Dept #]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top