Mwvarner
asked on
Error in SQL Query
I'm trying to create an SQL query that will find unmatched records using 2 table. Here is the query,
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE (((TblTWClean.DeptName)<>( TblADPClea n.Worked Department));
The error I'm getting is Syntax error (missing operator) in query expression "(((TblTWClean.DeptName)<> (TblADPCle an.Worked Department));"
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE (((TblTWClean.DeptName)<>(
The error I'm getting is Syntax error (missing operator) in query expression "(((TblTWClean.DeptName)<>
Your quotes are mismatched.
I have not tested, but I suspect that is the problem.
I have not tested, but I suspect that is the problem.
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE TblTWClean.DeptName<>TblADPClean.[Worked Department];
Well, if you are trying to find records in tblADClean which don't have matching records in tblTWClean then I would use something like:
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code]
, TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean
LEFT JOIN TblTWClean
ON (TblAdpClean.[EmpID] = TblTWClean.[EmpID]) AND (TblAdpClean.DeptName = TblTWClean.[Worked Department])
WHERE TblTWClean.ID IS NULL
This assumes that tblTWClean contains a PK field with the name of ID.
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code]
, TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean
LEFT JOIN TblTWClean
ON (TblAdpClean.[EmpID] = TblTWClean.[EmpID]) AND (TblAdpClean.DeptName = TblTWClean.[Worked Department])
WHERE TblTWClean.ID IS NULL
This assumes that tblTWClean contains a PK field with the name of ID.
Your format of SQL is not good , try this. In the select you have used it properly. Basically if you have space in the column name then you should use square brackets since it is a single name just like you used in the select clause.
SELECT
TblAdpClean.[Last Name]
, TblAdpClean.[First Name]
, TblAdpClean.[Worked Department]
, TblAdpClean.[Pay Code]
, TblAdpClean.Hours
, TblAdpClean.EmpID
, TblTWClean.DeptName
FROM
TblAdpClean
LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE TblTWClean.DeptName <> TblADPClean.[Worked Department]
The others have addressed the syntax errors but I don't believe the query will return the results you expect.
Where a <> b will not return true when b is null and so nothing should be selected.
What you want to do for a left join where you want only unmatched records is to test the right-side table pk for null
Where b Is Null
Where a <> b will not return true when b is null and so nothing should be selected.
What you want to do for a left join where you want only unmatched records is to test the right-side table pk for null
Where b Is Null
ASKER
Scott and Jeff, thanks for the help. I'm running through both suggestions now and comparing the data. Thanks for the help. I'll let you know how it goes.
ASKER
Thanks Pat,
Actually I do what you suggested first and add those records to an exception table. However it doesn't give me complete results. That's why this second pass looking for unmatched departments seems necessary to me.
Actually I do what you suggested first and add those records to an exception table. However it doesn't give me complete results. That's why this second pass looking for unmatched departments seems necessary to me.
try this.. I think I missed the ISNULL in the where clause..
SELECT
TblAdpClean.[Last Name]
, TblAdpClean.[First Name]
, TblAdpClean.[Worked Department]
, TblAdpClean.[Pay Code]
, TblAdpClean.Hours
, TblAdpClean.EmpID
, TblTWClean.DeptName
FROM
TblAdpClean
LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE ISNULL(TblTWClean.DeptName ,o) <> ISNULL(TblADPClean.[Worked Department],0)
SELECT
TblAdpClean.[Last Name]
, TblAdpClean.[First Name]
, TblAdpClean.[Worked Department]
, TblAdpClean.[Pay Code]
, TblAdpClean.Hours
, TblAdpClean.EmpID
, TblTWClean.DeptName
FROM
TblAdpClean
LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE ISNULL(TblTWClean.DeptName
If you are also having to look for unmatched departments, you probably have a schema design issue that should be addressed.
This query should be an inner join rather than a left join. OR you can combine the two where clauses in the original left join query so you don't have to run two queries:
Where b is Null OR where a.dept <> b.dept
This query should be an inner join rather than a left join. OR you can combine the two where clauses in the original left join query so you don't have to run two queries:
Where b is Null OR where a.dept <> b.dept
or this...
SELECT
TblAdpClean.[Last Name]
, TblAdpClean.[First Name]
, TblAdpClean.[Worked Department]
, TblAdpClean.[Pay Code]
, TblAdpClean.Hours
, TblAdpClean.EmpID
, TblTWClean.DeptName
FROM
TblAdpClean
LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE TblTWClean.[EmpID] IS NULL
Try this..
SELECT
b.[Last Name]
, b.[First Name]
, b.[Worked Department]
, b.[Pay Code]
, b.Hours
, b.EmpID
, c.DeptName
FROM
(
SELECT
EmpID
FROM
TblAdpClean
EXCEPT
SELECT
EmpID
FROM
TblTWClean
)k
INNER JOIN TblAdpClean b ON k.[EmpID] = b.[EmpID]
INNER JOIN TblTWClean c ON c.[EmpID] = k.[EmpID]
Pawan, that is not Access syntax and so cannot be used against linked tables.
Thank you PatHartman!! Just moved from SQL SERVER. :)
@Author - Could you please some data and the expected output. ?
@Author - Could you please some data and the expected output. ?
ASKER
I'm attaching a subset of the data in a sample database with just the tables in question.
For these two tables I'm attempting to generate an exception report where first is an employee is listed in one table but not the other. See Orlando Ager in TblAdpClean but not in TblTWClean.
Second where the employee is in both tables but the some or all the departments don't match.
For example please see Taryn Allman. She is listed once in TblAdpClean but she is listed 2 times in the TblTwClean. The same goes for Rosiland Barnes.
The attached database also has the queries I've been working with. They are close but I'm not sure I'm seeing all the exceptions.
Sample3.accdb
For these two tables I'm attempting to generate an exception report where first is an employee is listed in one table but not the other. See Orlando Ager in TblAdpClean but not in TblTWClean.
Second where the employee is in both tables but the some or all the departments don't match.
For example please see Taryn Allman. She is listed once in TblAdpClean but she is listed 2 times in the TblTwClean. The same goes for Rosiland Barnes.
The attached database also has the queries I've been working with. They are close but I'm not sure I'm seeing all the exceptions.
Sample3.accdb
As other experts have suggested, there were a few issues in the original SQL statement:
1. spaces in the fields require brackets around the field.
2. the logic for the extraction must be correlated to the correct JOIN.
3. data structure may not adhere to best practices as data duplication should be avoided at all costs.
Issue #2 requires a definition of the desired results.
The last post clarified some expectations:
A. To find employees listed in one table but not the other:
Use FULL OUTER JOIN and WHERE a.key or b.key is NULL
B. To find where employees are in both tables but have unmatched departments
USE INNER JOIN as subquery for the source table and run it against the department table. However, the OP hasn't clearly defined which table contains the departments to check against
1. spaces in the fields require brackets around the field.
2. the logic for the extraction must be correlated to the correct JOIN.
3. data structure may not adhere to best practices as data duplication should be avoided at all costs.
Issue #2 requires a definition of the desired results.
The last post clarified some expectations:
A. To find employees listed in one table but not the other:
Use FULL OUTER JOIN and WHERE a.key or b.key is NULL
SELECT
TblAdpClean.[Last Name],
TblAdpClean.[First Name],
TblAdpClean.[Worked Department],
TblAdpClean.[Pay Code],
TblAdpClean.[Hours],
TblAdpClean.[EmpID],
TblTWClean.[DeptName]
FROM
TblAdpClean FULL OUTER JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE
TblAdpClean.[EmpID] IS NULL
OR TblTWClean.[EmpID] IS NULL
B. To find where employees are in both tables but have unmatched departments
USE INNER JOIN as subquery for the source table and run it against the department table. However, the OP hasn't clearly defined which table contains the departments to check against
ASKER
These tables are from imported data from 2 spreadsheets. The TblAdpClean table has the field Worked Department an the TblTWClean has the DeptWorked field.
I need to create a list with any exceptions where and employee has different departments in these 2 tables as well as exceptions where an employee exists in one table but not the other.
I need to create a list with any exceptions where and employee has different departments in these 2 tables as well as exceptions where an employee exists in one table but not the other.
NerdsOffTech I believe this is an Access question and Full Outer Join is not directly supported. You have to simulate it with a union of a left and a right join
Thanks for the extra info see if the full outer join satisfies one of the two queries.
Can you post a small sample of the two tables in plain text here. That will help some experts that can't open that file
Can you post a small sample of the two tables in plain text here. That will help some experts that can't open that file
Pat is right, with access you need to do a synthetic full outer join
left join where b.id is null
Union all
right join where a.id is null
left join where b.id is null
Union all
right join where a.id is null
ASKER
When I try the outter join query I get a syntax error in From clause error.
There is the sample data.
I'm attaching 2 text files with sample data.
Thanks for all the help.
TblAdpClean.txt
TblTWClean.txt
There is the sample data.
I'm attaching 2 text files with sample data.
Thanks for all the help.
TblAdpClean.txt
TblTWClean.txt
Sorry, in Access you have to simulate a SQL FULL OUTER JOIN (with match exclusion); try this to obtain a table of employees that are listed in one table but not the other:
Simply, the FULL OUTER JOIN (with match exclusion AKA A.key IS NULL OR B.key IS NULL) synthesis consists of:
(LEFT JOIN + B.key IS NULL) UNION ALL (RIGHT JOIN + A.key IS NULL)
SELECT
TblAdpClean.[Last Name],
TblAdpClean.[First Name],
TblAdpClean.[Worked Department],
TblAdpClean.[Pay Code],
TblAdpClean.[Hours],
TblAdpClean.[EmpID],
TblTWClean.[DeptName]
FROM
TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE
TblTWClean.[EmpID] IS NULL
UNION ALL
SELECT
TblAdpClean.[Last Name],
TblAdpClean.[First Name],
TblAdpClean.[Worked Department],
TblAdpClean.[Pay Code],
TblAdpClean.[Hours],
TblAdpClean.[EmpID],
TblTWClean.[DeptName]
FROM
TblAdpClean RIGHT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE
TblAdpClean.[EmpID] IS NULL
Simply, the FULL OUTER JOIN (with match exclusion AKA A.key IS NULL OR B.key IS NULL) synthesis consists of:
(LEFT JOIN + B.key IS NULL) UNION ALL (RIGHT JOIN + A.key IS NULL)
NerdsOfTech, I'm not sure where you are seeing a requirement for a full outer join. The OP wants unmatched rows which is a simple left join. The other set of data is where department IDs are different. Both criteria can be applied in a query that does a left join.
I think the OP said these tables are derived from spreadsheets which have similar data and the requirement for the search was to obtain a table of employees that are listed in one table but not the other.
https://www.experts-exchange.com/questions/28986084/Error-in-SQL-Query.html?anchorAnswerId=41906209#a41906209
https://www.experts-exchange.com/questions/28986084/Error-in-SQL-Query.html?anchorAnswerId=41906209#a41906209
NerdsOfTech, Please reread the thread. Mwvarner wanted a list of records in tblA that were not in tblB. That isn't a full outer join. It is a left join where "b" key is null (the second picture in the left column)
Mwvarner, is your syntax error fixed.
Mwvarner, is your syntax error fixed.
Pat I think in this case it is the bottom right scenario (full outer join excluding the matches) where it is a XOR solution AKA exclusive or; where the record is in one or the other table, but not in both tables.
Again, this is rare but the OP mentioned the two tables have similar data. I linked this direct message where the OP explains this.
Again, this is rare but the OP mentioned the two tables have similar data. I linked this direct message where the OP explains this.
Not very sure..try..
1..
try..
Hope it helps !!
For these two tables I'm attempting to generate an exception report where first is an employee is listed in one table but not the other. See Orlando Ager in TblAdpClean but not in TblTWClean.
1..
try..
SELECT
b.[Last Name]
, b.[First Name]
, b.[Worked Department]
, b.[Pay Code]
, b.Hours
, k.EmpID
, c.DeptName
FROM
(
SELECT
t1.EmpID
FROM
TblAdpClean AS t1
LEFT JOIN TblTWClean t2 ON t1.EmpID = t2.EmpID
WHERE t2.EmpID IS NULL
) AS k
INNER JOIN TblAdpClean b ON k.[EmpID] = b.[EmpID]
INNER JOIN TblTWClean c ON c.[EmpID] = k.[EmpID]
2. Second where the employee is in both tables but the some or all the departments don't match.
SELECT
TblAdpClean.[Last Name]
, TblAdpClean.[First Name]
, TblAdpClean.[Worked Department]
, TblAdpClean.[Pay Code]
, TblAdpClean.Hours
, TblAdpClean.EmpID
, TblTWClean.DeptName
FROM
TblAdpClean
INNER JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
AND IIF(ISNULL(TblAdpClean.[Worked Department]),0,TblAdpClean.[Worked Department]) <> IIF(ISNULL(TblTWClean.[Worked Department]),0,TblTWClean.[Worked Department])
Hope it helps !!
A small amount of sample data would be most helpful....
Essentially the "filter" would be something like this
Essentially the "filter" would be something like this
[TableA]![Field] NOT IN ( SELECT [TableB]![Field] FROM TableB)
ASKER
The first query failed with a syntax error. See the attached file Capture1.jpg. The second query worked after I changed the field name TblTWClean.[worked department] to TblTWClean[DeptName. in the INNER JOIN clause. The data in the second query also looks like exactly what I need so that query is good to go.
SELECT
TblAdpClean.[Last Name],
TblAdpClean.[First Name],
TblAdpClean.[Worked Department],
TblAdpClean.[Pay Code],
TblAdpClean.Hours,
TblAdpClean.EmpID,
TblTWClean.DeptName
FROM
TblAdpClean
INNER JOIN TblTWClean ON (IIF(ISNULL(TblAdpClean.[W orked Department]),0,TblAdpClean .[Worked Department]) <> IIF(ISNULL(TblTWClean.[Dep tName]),0, TblTWClean .[DeptName ])) AND (TblAdpClean.[EmpID] = TblTWClean.[EmpID]);
Capture1.jpg
SELECT
TblAdpClean.[Last Name],
TblAdpClean.[First Name],
TblAdpClean.[Worked Department],
TblAdpClean.[Pay Code],
TblAdpClean.Hours,
TblAdpClean.EmpID,
TblTWClean.DeptName
FROM
TblAdpClean
INNER JOIN TblTWClean ON (IIF(ISNULL(TblAdpClean.[W
Capture1.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pawan:
Do you test your SQL syntax before posting? Your most recent post fails, just like the last few did. The author has uploaded their database for testing - have you downloaded it to test your suggestions BEFORE posting?
Scott
Do you test your SQL syntax before posting? Your most recent post fails, just like the last few did. The author has uploaded their database for testing - have you downloaded it to test your suggestions BEFORE posting?
Scott
ASKER
I did download and test before posting. The last queries from Pat Hartman do exactly what I need. A little more testing and I think I can close this question.
Thanks'
Thanks'
ASKER
Everything works just like I need it to. I've adapted the queries to my main database and the data looks to be exactly what I need. It will give me an exception report for our payroll person to work from to correct the errors they have.
Thanks to everyone who assisted.
Marshall
Thanks to everyone who assisted.
Marshall
ASKER
Everything works just like I need it to. I've adapted the queries to my main database and the data looks to be exactly what I need. It will give me an exception report for our payroll person to work from to correct the errors they have.
Thanks to everyone who assisted.
Marshall
Thanks to everyone who assisted.
Marshall
Glad I could help.
I did download and test before posting.My comment was not directed at you, but rather one of the Experts (Pawan). More and more we see him (and many others) rapid-posting, without testing. Tends to clutter the threads, and makes it VERY difficult to assist others.
Of course, that's just my .02 worth ...
SELECT TblAdpClean.[Last Name], TblAdpClean.[First Name], TblAdpClean.[Worked Department], TblAdpClean.[Pay Code], TblAdpClean.Hours, TblAdpClean.EmpID, TblTWClean.DeptName
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.[EmpID] = TblTWClean.[EmpID]
WHERE (((TblTWClean.[DeptName])<
Note the changes in the WHERE clause