title: Show Only Department Changes in Access 2010 Query
body: <p>I have a table in Access that shows department history for associates; in total, there are several thousand records, and if an employee was in more than one department, he/she will have more than one record. An sample of the data is shown in the attached photo. <a href="http://i.stack.imgur.com/XU70W.png" rel="nofollow">Sample Table</a> </p>
  
  <p>The goal is for the query in Access to return transfer history for an associate, or in other words, the first time he/she was in each unique department code. An example of the expected outcome from the sample data is shown in the attached photo as well. <a href="http://i.stack.imgur.com/EsDhu.png" rel="nofollow">Expected Outcome</a></p>
  
  <p>Notice, for example, that the first associate (Employee Test1) had a top of stack row on 7/5/2016 that I would expect to be omitted because the previous record already reflects the transfer into 001271 on 5/2/2016. </p>
  
  <p>Thus far, I've created the following query in Access via the Query Wizard (I'm a novice at SQL): </p>
  
  <pre><code>SELECT DISTINCT [Multiple Records_1].[Associate ID], [Multiple Records_1].[First Name], [Multiple Records_1].[Last Name], Min([Multiple Records_1].[Position Effective Date]) AS [MinOfPosition Effective Date], [Multiple Records_1].[Home Department Code], [Multiple Records_1].[Business Unit Description]
  FROM [Multiple Records] INNER JOIN [Multiple Records] AS [Multiple Records_1] ON [Multiple Records].[Associate ID] = [Multiple Records_1].[Associate ID]
  GROUP BY [Multiple Records_1].[Associate ID], [Multiple Records_1].[First Name], [Multiple Records_1].[Last Name], [Multiple Records_1].[Home Department Code], [Multiple Records_1].[Business Unit Description], [Multiple Records].[Home Department Code]
  HAVING ((([Multiple Records].[Home Department Code])&lt;&gt;[Multiple Records_1].[Home Department Code]));
  </code></pre>
  
  <p>This got me pretty close to what I needed, but the 10/5/2015 record for "Employee Test 1" was suppressed because he transferred back into 001289...I want to make sure that similar records aren't dropped because an associate transfers out of a department but then later transfers back into it. </p>
  
  <p>I would greatly appreciate any help! Many thanks in advance! </p>