title: Union of two tables but show which table the data came from
body: <p>I have two tables:</p>
  
  <pre><code>           TABLE_A                 TABLE_B
  Fields:    Trans   Amend           Trans   Amend 
  data:      100       0             100      0
             100       1
             110       0         
                                     120      0
                                     120      1
             130       0             130      0 
                                     130      1
             140       0             140      0
             150       0             150      0
             150       1             150      1
             150       2             
  </code></pre>
  
  <p>What I want is a table (view) that will combine (union) these to tables but will only show the highest Amend for each Trans</p>
  
  <p>Looking for this as the answer:   </p>
  
  <pre><code>Fields:    Trans   Amend   
  data:      100       1
             110       0
             120       1
             130       1
             140       0 
             150       2   
  </code></pre>
  
  <p>Then to make it harder, I would like to know if there is a way I can tell from which table the data is coming from. Table A always wins when Record A and Record B are equal
  Looking for this as the answer:   </p>
  
  <pre><code>Fields:    Trans   Amend    WhichTBL
  data:      100       1      Table_A
             110       0      Table_A
             120       1      Table_B
             130       1      Table_B
             140       0      Table_A
             150       2      Table_A
  </code></pre>
  
  <p>I know a UNION can't be done to get this result.</p>