SQL Server 2008可用的删除查询在Access 2016中报错求助

First, let's break down why your original SQL works in SQL Server 2008 but fails in Access 2016: Access's ACE/Jet SQL has stricter limitations on nested subqueries with JOINs inside an IN clause for DELETE operations. The nested JOIN structure you used isn't supported in Access's query syntax.

Solution 1: Use EXISTS to Target Problematic RevInv Values #

This approach identifies all RevInv values that have conflicting Invoice Value entries, then deletes all records for those RevInv values. It's simpler and Access-friendly:

DELETE FROM SampleData34
WHERE RevInv IN (
    SELECT s.RevInv
    FROM SampleData34 s
    WHERE EXISTS (
        SELECT 1
        FROM SampleData34 t
        WHERE t.RevInv = s.RevInv
          AND t.[Invoice Value] <> s.[Invoice Value]
    )
)

How it works:

• The inner EXISTS check looks for any record t that shares the same RevInv as record s but has a different Invoice Value.
• If such a record exists, the RevInv is marked for deletion.
• All records with that RevInv are removed (exactly what you want for cases like 1113 in your sample data).

Solution 2: Target Only Valid RevInv Values to Keep #

Alternatively, you can first identify which RevInv values have all identical Invoice Value entries, then delete everything else:

DELETE FROM SampleData34
WHERE RevInv NOT IN (
    SELECT RevInv
    FROM SampleData34
    GROUP BY RevInv, [Invoice Value]
    HAVING COUNT(*) = (
        SELECT COUNT(*) 
        FROM SampleData34 t 
        WHERE t.RevInv = SampleData34.RevInv
    )
)

How it works:

• The subquery groups records by RevInv and Invoice Value.
• The HAVING clause checks if the count of records in that group equals the total number of records for that RevInv (meaning every entry for this RevInv has the same Invoice Value).
• We delete all records whose RevInv isn't in this "valid" list.

Verification with Your Sample Data #

For your test data:

Both solutions will correctly delete the 1113 records while preserving the others.

Key Notes for Access SQL #

• Always wrap field/table names with spaces (like [Invoice Value]) in square brackets.
• Avoid complex nested JOINs inside IN clauses for DELETE operations—Access doesn't handle these well.
• Access doesn't support COUNT(DISTINCT), so we use EXISTS or correlated subqueries instead to detect conflicting values.

内容的提问来源于stack exchange，提问作者Ansh