Solved

Error in SQL Query

Posted on 2016-11-29
36
34 Views
Last Modified: 2016-11-30
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));"
0
Comment
Question by:Mwvarner
  • 9
  • 8
  • 7
  • +5
36 Comments
 
LVL 84
ID: 41905823
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 41905824
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41905847
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41905913
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41905941
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
 

Author Comment

by:Mwvarner
ID: 41905945
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
 

Author Comment

by:Mwvarner
ID: 41905946
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41905953
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41905956
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41905967
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41905982
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41905988
Pawan, that is not Access syntax and so cannot be used against linked tables.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41906056
Thank you PatHartman!! Just moved from SQL SERVER. :)

@Author - Could you please some data and the expected output. ?
0
 

Author Comment

by:Mwvarner
ID: 41906100
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906155
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
 

Author Comment

by:Mwvarner
ID: 41906209
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41906347
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906350
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906353
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
 

Author Comment

by:Mwvarner
ID: 41906361
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906409
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41906422
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906433
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41906514
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906612
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41907070
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
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41907123
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
 

Author Comment

by:Mwvarner
ID: 41907231
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41907488
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
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points
ID: 41907496
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
 
LVL 84
ID: 41907533
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
 

Author Comment

by:Mwvarner
ID: 41907538
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
 

Author Comment

by:Mwvarner
ID: 41907540
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
 

Author Closing Comment

by:Mwvarner
ID: 41907543
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41907544
Glad I could help.
0
 
LVL 84
ID: 41907677
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now