title: Multiple table join with condition on second table
body: <p>There are two tables: person and invoice
  there are many invoice rows for each person, and I want select all persons info with last invoice amount of them.</p>
  
  <p><strong>Person</strong></p>
  
  <pre><code>code  |  Name | ....
  1     |  name1
  2     |  name1
  3     |  name1
  </code></pre>
  
  <p><strong>Invoice</strong></p>
  
  <pre><code>ID    | person_code   |  amount   |  date 
  1     |  2            |  30000    |  12
  2     |  1            |  40000    |  10
  3     |  3            |  50000    |  12
  4     |  2            |  60000    |  14
  5     |  3            |  70000    |  12
  6     |  2            |  80000    |  12
  7     |  1            |  90000    |  18
  </code></pre>
  
  <p>I want select</p>
  
  <pre><code>person code | person name  | last amount
  1           |  name1       |  90000
  2           |  name2       |  60000
  3           |  name3       |  70000
  </code></pre>
  
  <p>or </p>
  
  <pre><code> SELECT person.code , person.name , lastinvoice.amount 
   FROM person 
   LEFT JOIN
       (SELECT * FROM invoice where invoice.person_code=person.code order by date, ID) as
   lastinvoice ON lastinvoice.person_code = person.code
  </code></pre>
  
  <p>This query does not work on ms access:</p>
  
  <pre><code>select * from invoice as i where id=(select max(id) from invoice where personCode=i.personcode and date=( select max(date) from invoice where personCode=i.PersonCode
  </code></pre>