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)<>(TblADPClean.Worked Department));


The error I'm getting is Syntax error (missing operator) in query expression  "(((TblTWClean.DeptName)<>(TblADPClean.Worked Department));"
MwvarnerAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Here's the two queries you need:

Find Emp in ADP but Not In TW or in both but Depts don't match --
SELECT TblAdpClean.*, TblTWClean.*
FROM TblAdpClean LEFT JOIN TblTWClean ON TblAdpClean.EmpID = TblTWClean.EmpID
WHERE (((TblTWClean.EmpID) Is Null)) OR (((TblAdpClean.[Worked Department])<>[DeptName]));


Find Emp in TW but not in ADP --
SELECT TblTWClean.*
FROM TblTWClean LEFT JOIN TblAdpClean ON TblTWClean.EmpID = TblAdpClean.EmpID
WHERE (((TblAdpClean.EmpID) Is Null));
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You have spaces in your Column names (which is a no-no, by the way):

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]));

Note the changes in the WHERE clause
0
 
Jeff DarlingDeveloper AnalystCommented:
Your quotes are mismatched.

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];

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Dale FyeCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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]

Open in new window

0
 
PatHartmanCommented:
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
0
 
MwvarnerAuthor Commented:
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.
0
 
MwvarnerAuthor Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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)
0
 
PatHartmanCommented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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]

Open in new window

0
 
PatHartmanCommented:
Pawan, that is not Access syntax and so cannot be used against linked tables.
0
 
Pawan KumarDatabase ExpertCommented:
Thank you PatHartman!! Just moved from SQL SERVER. :)

@Author - Could you please some data and the expected output. ?
0
 
MwvarnerAuthor Commented:
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
0
 
NerdsOfTechTechnology ScientistCommented:
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

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

Open in new window


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
0
 
MwvarnerAuthor Commented:
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.
0
 
PatHartmanCommented:
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
0
 
NerdsOfTechTechnology ScientistCommented:
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
0
 
NerdsOfTechTechnology ScientistCommented:
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
0
 
MwvarnerAuthor Commented:
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
0
 
NerdsOfTechTechnology ScientistCommented:
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:
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

Open in new window


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)

SQL JOINS
0
 
PatHartmanCommented:
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.
0
 
NerdsOfTechTechnology ScientistCommented:
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#a41906209
0
 
PatHartmanCommented:
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.
0
 
NerdsOfTechTechnology ScientistCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
Not very sure..try..

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]

Open in new window


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])

Open in new window


Hope it helps !!
0
 
John TsioumprisSoftware & Systems EngineerCommented:
A small amount of sample data would be most helpful....
Essentially the "filter" would be something like this
[TableA]![Field] NOT IN ( SELECT [TableB]![Field] FROM TableB)

Open in new window

0
 
MwvarnerAuthor Commented:
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.[Worked Department]),0,TblAdpClean.[Worked Department]) <> IIF(ISNULL(TblTWClean.[DeptName]),0,TblTWClean.[DeptName])) AND (TblAdpClean.[EmpID] = TblTWClean.[EmpID]);
Capture1.jpg
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Updated for case 1..

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 AS t2 ON t1.EmpID = t2.EmpID
		  WHERE t2.EmpID IS NULL
) AS k
INNER JOIN  TblAdpClean AS b ON k.[EmpID] = b.[EmpID] 
INNER JOIN TblTWClean  AS c ON c.[EmpID] = k.[EmpID]

Open in new window


Hope it helps !!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
MwvarnerAuthor Commented:
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'
0
 
MwvarnerAuthor Commented:
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
0
 
MwvarnerAuthor Commented:
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
0
 
PatHartmanCommented:
Glad I could help.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.