Search code examples
sql-server-2008ms-accessms-access-2016

Query working in SQL 2008 but not in Access 2016. Delete records with same RevInv but different InvValue

DELETE FROM [SampleData34] WHERE [RevInv] IN 
( SELECT t.[RevInv] FROM[SampleData34] s JOIN
( SELECT [RevInv], [Invoice Value] 
  FROM SampleData34 GROUP BY [RevInv],[Invoice Value]
  HAVING COUNT(*) >=1
 ) t 
 ON s.[RevInv]=t.[RevInv] AND s.[Invoice Value]<>t.[Invoice Value]
)

Logic of this code is as follows:

The table has duplicate RevInv values - with either same Invoice Value or different. I want to identify (and delete) all records that have different Invoice Value (same RevInv number but different Invoice Value). There are scenarios where we have no duplicates ie onl one RevInv Number and corresponding InvValue (those cases need not be included)

SampleData

RevInv InvoiceValue  
1111   100  
1112   101  
1112   101  
1113   102  
1113   103

(1113 102 and 1113 103 should be deleted - different InvValues for same RevInv)

the above query works in SQl however in Access I'm getting this error:

Syntax error in query expression '[RevInv] IN ( SELECT t.[RevInv] FROM [SampleData34] s JOIN ( SELECT [RevInv], [Invoice Value] FROM SampleData34 GROUP BY [RevInv],[Invoice Value] HAVING COUNT(*)>=1 ) t ON s.[RevInv]=t.[RevInv] AND s.[Invoice Value]<>t.[Invoice Value])'.

enter image description here

Solution

  • Access won't accept just the naked JOIN keyword. It has to be either INNER, LEFT or RIGHT join. I believe SQL server defaults to INNER.