Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Try:</p> <pre><code>SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.CO1 FROM (SELECT 'RECIPETABLE1' as TableName, sc_head.ID, sc_head.CO1 FROM sc_head IN 'C:\MyRecipes\old.mdb' UNION ALL SELECT 'RECIPETABLE2' as TableName, compare_sc_head.ID, compare_sc_head.CO1 FROM compare_sc_head in 'C:\MyRecipes\new.mdb') As tmp GROUP BY tmp.ID, tmp.CO1 HAVING Count(*)=1 ORDER BY tmp.ID </code></pre> <p>However, I suggest it would be easier to use a little VBA for the comparison. </p> <p>It is not too unpleasant comparing a single table with a very small number of fields using the query design window, however, for more tables of fields, it is far easier to use VBA.</p> <p>Here are some queries that will run in the query design window.</p> <p>Record in NewDB but not in OldDB</p> <pre><code>SELECT n.ID, n.CO1 FROM (SELECT ID, CO1 FROM compare_sc_head IN 'C:\MyRecipes\new.mdb') AS n LEFT JOIN (SELECT ID, CO1 FROM sc_head IN 'C:\MyRecipes\old.mdb') AS o ON n.ID = o.ID WHERE o.ID Is Null </code></pre> <p>Record in OldDB but not in NewDB</p> <pre><code>SELECT o.ID, o.CO1 FROM (SELECT ID, CO1 FROM sc_head IN 'C:\MyRecipes\old.mdb') AS o LEFT JOIN (SELECT ID, CO1 FROM compare_sc_head IN 'C:\MyRecipes\new.mdb') AS n ON o.ID = n.ID WHERE n.ID Is Null </code></pre> <p>Record ID matches but CO1 does not</p> <pre><code>SELECT n.ID, n.CO1, o.CO1 FROM (SELECT ID, CO1 FROM sc_head IN 'C:\MyRecipes\old.mdb') AS o INNER JOIN (SELECT ID, CO1 FROM compare_sc_head IN 'C:\MyRecipes\new.mdb') AS n ON o.ID = n.ID WHERE n.CO1 &amp; "" &lt;&gt; o.CO1 &amp; "" </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PObasic question on SQL for access
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USFionnuala
    1. USFionnuala
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PObasic question on SQL for access
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for the correction. By running I have another error: Syntax error in Union Query.
      singulars
      1. PO
      1. USJohnMunich
    2. CO@JohnMunich Sorry about that, I have corrected and tested
      singulars
      1. PO
      1. USFionnuala
    3. COgreat! It works this time. By running this script I get all the fields displayed instead of only the fields which are changed. Is there any other method?
      singulars
      1. PO
      1. USJohnMunich
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload