关于SQL for access的基本问题

Question

hallo专家，

我有两个mdb数据库，比如old.mdb和new.mdb，它们具有相同的表和结构。我只想验证两个数据库中特定表中的哪些记录被更改了。我用谷歌搜索了很长一段时间，最终发现了以下SQL脚本：

SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.CO1

FROM ( SELECT RECIPETABLE1 IN 'C:\MyRecipes\old.mdb' as TableName, sc_head.ID, sc_head.CO1

FROM sc_head

UNION ALL

SELECT RECIPETABLE1 in 'C:\MyRecipes\new.mdb ' as TableName, compare_sc_head.ID, compare_sc_head.CO1

FROM compare_sc_head

) AS tmp

GROUP BY tmp.ID, tmp.CO1

HAVING Count(*)=1

ORDER BY tmp.ID

在运行时，我收到一个错误，说"In-Operator without ()“

任何见解都是感恩的。

约翰

Answer 1

尝试：

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

但是，我建议使用一点VBA进行比较会更容易一些。

使用查询设计窗口将单个表与非常少的字段进行比较并不是很令人不快，但是，对于包含更多字段的表，使用VBA要容易得多。

以下是将在查询设计窗口中运行的一些查询。

记录在NewDB中，但不在OldDB中

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

记录在OldDB中，但不在NewDB中

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

记录ID匹配，但CO1不匹配

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 & "" <> o.CO1 & ""