CODEWITHSUNDEEP
sqlms-access
user32882
user32882

Reputation: 5917

MS Access SQL error in From Clause

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.

Upvotes: 0

Views: 133

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95090

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;

Upvotes: 3

Related Questions