title: Doing a calculation between two rows with EXTRA CRITERIA?
body: <pre><code>Name      ExactDate  Presents      Location
  bob1         2011        1          home
  bob2         2008        2          school     
  bob2         2012        3          school
  mary2        1986        4          school       
  mary1        2001        5          home
  mary1        2012        6          home    
  kate1        2011        7          home
  kate1        2012        8          home
  kate2        2011        9          school 
  celia2       2011       10          school 
  celia2       1986       11          school  
  celia1       1972       12          home
  celia1       2012       14          home 
  celia2       2012       13          school
  </code></pre>
  
  <p>This problem is done in SQL in MS Access 2003 for a query.</p>
  
  <p>So the goal is we subtract the amount of presents kate got from celia on the same year ( but since there are a few different present values for the same year we choose to have priority of home > school....for example celia and kate both receive presents in 2012 but celia gets both home presents and school presents in 2012 in which case we choose her home present value to do the calculation) and out put should be something like the following: </p>
  
  <pre><code>Name              ExactDate        PresentsDiff
  celiaminuskate     2011                      3
  celiaminuskate     2012                      6
  </code></pre>
  
  <p>So far I have :</p>
  
  <pre><code>SELECT 'celiaminuskate'AS [NAME],T1.[date] AS [EXACT DATE],
  T1.presents T2.presents AS [PRESENTS DIFF]
  FROM Some_Table T1, Some_Table T2 
  </code></pre>
  
  <p>part that I think needs to be fixed?? </p>
  
  <pre><code>WHERE (T1.Name = 'celia1'&gt;'celia2')
  AND (T2.Name = 'kate1'&gt;'kate2')
  AND T2.ExactDate = T1.ExactDate 
  </code></pre>
  
  <p>to indicate priority? I'm not too sure how to do it </p>
  
  <pre><code>ORDER BY T1.ExactDate
  </code></pre>