0

I am trying to do a set difference as shown by this question, except I am doing it in Microsoft Access. The SQL query I am using is as follows:

SELECT FieldName
FROM CalParams_External
EXCEPT SELECT FieldName
FROM CalParams_Internal
UNION
SELECT FieldName
FROM CalParams_Internal
EXCEPT SELECT FieldName
FROM CalParams_External

When I run this however it throws an error:

Syntax error in FROM clause

I'd like to get this to work. What am I doing wrong here and how can I get this simple script to run?

EDIT

According to the comment below, JET does not support the EXCEPT statement. I would like to break down the problem by finding the non-intersecting part of only one of the datasets using the MINUS statement instead (which I believe is supported). Here's what I'm doing now:

SELECT FieldName
From CalParams_External
MINUS
SELECT FieldName
FROM CalParams_Internal

I'm still getting the same error regarding the FROM clause though.

halfer
  • 19,824
  • 17
  • 99
  • 186
user32882
  • 5,094
  • 5
  • 43
  • 82

1 Answers1

3

As MS Access doesn't support EXCEPT, use NOT IN or NOT EXISTS instead. NOT IN is more readable, but only works on non-null columns.

NOT IN query:

SELECT FieldName FROM CalParams_External
WHERE FieldName NOT IN (SELECT FieldName FROM CalParams_Internal)
UNION
SELECT FieldName FROM CalParams_Internal
WHERE FieldName NOT IN (SELECT FieldName FROM CalParams_External);

NOT EXISTS query:

SELECT FieldName FROM CalParams_External e
WHERE NOT EXISTS (SELECT * FROM CalParams_Internal i WHERE i.FieldName = e.FieldName)
UNION
SELECT FieldName FROM CalParams_Internal i
WHERE NOT EXISTS (SELECT * FROM CalParams_External e WHERE e.FieldName = i.FieldName)

Aggregation query:

An alternative would be to select each FiedName once from each table and then pick those that only occur in one of them:

SELECT FieldName
FROM
(
  SELECT DISTINCT FieldName FROM CalParams_External
  UNION ALL
  SELECT DISTINCT FieldName FROM CalParams_Internal
)
GROUP BY FieldName
HAVING COUNT(*) = 1;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • It appears to work but I would like to see if the `MINUS` statement can be used in this context. Could you add one option for a `MINUS` query? – user32882 Jun 22 '17 at 08:16
  • MS Access doen't support `EXCEPT` which is standard SQL nor `MINUS` which is Oracle proprietary. I've added an alternative to my answer, but you have nothing like `EXCEPT` in MS Access. – Thorsten Kettner Jun 22 '17 at 08:19