UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Issue Using Not In With Append Query, Access 2013    
 
   
ozop102
post Sep 5 2019, 10:57 PM
Post#1



Posts: 8
Joined: 28-August 19



Hello Everyone,

I have put together an Append Query with SQL to insert new records from one table to another. The tables do not have the same exact fields. I already use one NOT operator as the Where condition and it works fine. The issue is that when I try to use NOT IN combined with AND, I receive a syntax error.

My Code That Works
CODE
INSERT INTO Locator ([SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS])
SELECT [SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS]
FROM tblTest
WHERE NOT [DAS] Is Null


Mismatch error
CODE
INSERT INTO Locator ([SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS])
SELECT [SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS]
FROM tblTest
WHERE NOT [DAS] Is Null AND SSAN NOT IN (SELECT SSAN FROM Locator)


I have also tried to use inner join but receive an error stating fields could refer to more than one table listed in the from clause. If I add "tablename." before each field I still recieve a syntax error.
CODE
INSERT INTO Locator ([SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS])
SELECT [SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS]
FROM tblTest INNER JOIN Locator ON tblTest.SSAN = Locator.SSAN
WHERE NOT [DAS] Is Null AND Locator.SSAN Is Null


Any ideas or suggestions?
Go to the top of the page
 
theDBguy
post Sep 5 2019, 11:05 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,522
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

Just a guess but try using this instead.
CODE
...WHERE [DAS] Is Not Null AND ...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ozop102
post Sep 5 2019, 11:32 PM
Post#3



Posts: 8
Joined: 28-August 19



I tried

CODE
INSERT INTO Locator ([SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS])
SELECT [SSAN], [Last Name], [First Name], [Rank], [Supervisor], [Remarks], [Crew Position], [Cell Phone], [Email], [DAS]
FROM tblTest
WHERE [DAS] Is Not Null AND SSAN NOT IN (SELECT SSAN FROM Locator)


and I still get a mismatch error. SSAN is the primary key for both tables so I am not sure if that has anything to do with it.
Go to the top of the page
 
MadPiet
post Sep 5 2019, 11:38 PM
Post#4



Posts: 3,358
Joined: 27-February 09



Maybe my brain is mush. Could you post a sanitized copy of your database with just those two tables so we can see what's going on? If you're trying to join two tables on primary keys, that's almost guaranteed to be wrong, because you normally join a primary key in one table to a foreign key in another table.
Go to the top of the page
 
ozop102
post Sep 6 2019, 12:19 AM
Post#5



Posts: 8
Joined: 28-August 19



Here is the basic copy of just the tables and the query. So basic overview of how its intended to work is that the majority of the database pulls data from the locator table. The test table is where temporary work is done on potential inbound users. If the users end up not coming, they are simply deleted from that table. However, if they end up coming to the organization, the would need to be moved from the test table to the locator table. Normally we just hand jam, manually copy and paste them or re-enter their information one by one into the locator table. When we enter 20 to 50 people at a given time, it can be time consuming and so easily inserting them into the main table with this append query will save a lot of time.

Attachment is provided. The social security number is the primary key SSAN and so we cannot have duplicates.
Attached File(s)
Attached File  query_test.zip ( 48.85K )Number of downloads: 4
 
Go to the top of the page
 
MadPiet
post Sep 6 2019, 12:43 AM
Post#6



Posts: 3,358
Joined: 27-February 09



This doesn't look right:

CODE
WHERE NOT [DAS] Is Null AND SSAN NOT IN (SELECT SSAN FROM Locator)


Shouldn't it be:

CODE
WHERE [DAS] Is Not Null
AND SSAN NOT IN (SELECT SSAN FROM Locator);


Go to the top of the page
 
ozop102
post Sep 6 2019, 12:57 AM
Post#7



Posts: 8
Joined: 28-August 19



CODE
WHERE NOT [DAS] Is Null


works fine and it is valid syntax. They both do the right thing and work. The only issue is adding the AND operator and not inserting records where the SSAN already exists.


I created a macro to run the query with just "WHERE NOT [DAS] Is Null" and i setwarnings off. Doing so I notice the records which are new import fine and records with an already existing SSAN just dont insert in (since it skips the error prompt). I still feel there should be a better way than ignoring warnings and errors. Its not the ideal way and personally im not a fan of just ignoring errors laugh.gif

Regardless at least it works!
Go to the top of the page
 
MadPiet
post Sep 6 2019, 01:10 AM
Post#8



Posts: 3,358
Joined: 27-February 09



you'd have to modify your code to eliminate any records already in the destination table

CODE
INSERT INTO tblDestination
SELECT ID, <other columns>
FROM tblSource
WHERE ID NOT IN (SELECT ID FROM tblDestination)
Go to the top of the page
 
RJD
post Sep 6 2019, 02:06 AM
Post#9


UtterAccess VIP
Posts: 10,130
Joined: 25-October 10
From: Gulf South USA


Hi: Actually, the problem is with the SSAN in the WHERE clause ... AND SSAN NOT IN (SELECT SSAN FROM Locator) ... In tblTest the SSAN is a Number and in Locator it is Text, thus the mismatch. Access handles this in the INSERT, but cannot in the WHERE clause, constructed as it is.

I changed the SSAN to Text in tblTest and the query is without error. However, no records are inserted in your test db since the unmatched SSAN in tblTest also has a Null DAS field...

HTH
Joe
Attached File(s)
Attached File  querytest_Rev1.zip ( 24.58K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Sep 6 2019, 03:40 PM
Post#10



Posts: 6,150
Joined: 11-November 10
From: SoCal, USA


QUOTE
The social security number is the primary key SSAN and so we cannot have duplicates

just as an fyi: you can't guarantee the uniqueness of US social security numbers, so it's not the best choice for a primary key value. not to mention the sensitive nature of the SSN - will your database security be adequate to protect it?

hth
tina
This post has been edited by tina t: Sep 6 2019, 03:41 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Sep 6 2019, 07:09 PM
Post#11


UtterAccess VIP
Posts: 10,130
Joined: 25-October 10
From: Gulf South USA


Hi tina! wavehi.gif

QUOTE
You can't guarantee the uniqueness of US social security numbers

Ouch. I hope that isn't true! I don't want someone else's government account to get mixed with mine! (Unless it's Bill Gates, perhaps, to my benefit!)

QUOTE
...not to mention the sensitive nature of the SSN - will your database security be adequate to protect it?

That I agree with wholeheartedly! In the food-pantry apps I have developed, we sometimes have kept the last 4 of the SSAN for ID purposes, and of course, this is not unique, but combined with other data tends to be helpful. This helps sort out often indigent clients who change names and addresses. But NEVER all 9 digits. We often use other iD such as driver license (or picture ID) number, but never as a primary key, since they are subject to change.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Sep 6 2019, 09:36 PM
Post#12



Posts: 6,150
Joined: 11-November 10
From: SoCal, USA


hi Joe! :)

QUOTE
I hope that isn't true!

unfortunately it is. SSNs are unique at the point of being issued and are never re-used, supposedly - though with only 9 digits, and the population of this country, i tend to question that statement. but SSNs can be stolen - it's a form of identity theft unless the owner is complicit, of course. and faking an SSN must surely be easy; if your number is 123456789, well, probably numbers 123456788 and 123456790 have been issued also. and fake SSN cards can be bought, same as fake driver's licenses.

Wikipedia has an interesting article about SSNs, for anyone who is interested. :)

hth
tina
This post has been edited by tina t: Sep 6 2019, 10:00 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 12:05 PM