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
> Append Records From Table A To Table B That Are Not Duplicate Last And First Names, Access 2016    
 
   
billwild
post Feb 13 2020, 06:23 PM
Post#1



Posts: 125
Joined: 4-June 02



I need to append records from table A to table B that are not duplicate last and first names

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
GroverParkGeorge
post Feb 13 2020, 06:32 PM
Post#2


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


We normally use the "Find Unmatched Query Wizard" to identify records that exist in one table, but not another.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
billwild
post Feb 13 2020, 07:16 PM
Post#3



Posts: 125
Joined: 4-June 02



Actually this is what I need:
Merging Access Tables and Removing Duplicate Records. I believe this should be a union SQL query but I can't figure it out.

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
GroverParkGeorge
post Feb 13 2020, 07:23 PM
Post#4


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


And did you look at the Query Wizard as I suggested?

(I doubt a Union query will help.)

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Feb 13 2020, 08:21 PM
Post#5


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


If you want to merge records from both tables, and not simply append records from one table to the other, you can write two queries, one for each direction.

The "Find Unmatched Query Wizard" will do the trick for those two queries.

Write them and then turn them into Append queries.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
John Vinson
post Feb 14 2020, 01:00 PM
Post#6


UtterAccess VIP
Posts: 4,292
Joined: 6-January 07
From: Parma, Idaho, US


Probably a bad idea. Names are not unique - I know three gentlemen named Fred Brown (father, son, and unrelated). What's the context? Is there any other information that would let you distinguish unique people?

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
orange999
post Feb 14 2020, 02:18 PM
Post#7



Posts: 2,054
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


billwild,
How do you define duplicate? Do you have another field or 2 to more precisely identify people? Please give us an example.
This post has been edited by orange999: Feb 14 2020, 02:19 PM

--------------------
Good luck with your project!
Go to the top of the page
 
billwild
post Feb 21 2020, 01:26 PM
Post#8



Posts: 125
Joined: 4-June 02



Okay, I have Table A and Table B. I want to append the records from Table B that do not have matching email address. In other words append all the records from Table B unless there is a matching email address in Table A

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
MadPiet
post Feb 21 2020, 02:35 PM
Post#9



Posts: 3,486
Joined: 27-February 09



Okay, I have Table A and Table B. I want to append the records from Table B that do not have matching email address. In other words append all the records from Table B unless there is a matching email address in Table A

INSERT INTO TableB (FieldList)
SELECT <field list from A>
FROM A
WHERE NOT EXISTS (SELECT 1 FROM TableB WHERE A.EmailAddress = B.EMailAddress);

Go to the top of the page
 
billwild
post Feb 21 2020, 05:36 PM
Post#10



Posts: 125
Joined: 4-June 02



Thanks for the quick response. I have a couple of questions tho.
1. The table I want to append TO is TABLE A. Your example says Table B
2. What are you referring to when you say <FieldList> and <field list from A>?

It might help if I give the EXACT name of the table/fields.
Table A is MasterListing
Table B is CoffeeCup
The Email address is the field named "Email"

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
MadPiet
post Feb 21 2020, 05:42 PM
Post#11



Posts: 3,486
Joined: 27-February 09



It might help if I give the EXACT name of the table/fields.
Table A is MasterListing
Table B is CoffeeCup
The Email address is the field named "Email"

CODE
INSERT INTO MasterListing(Email)
SELECT EMail
FROM CoffeeCup cc
WHERE NOT EXISTS (SELECT 1 FROM MasterListing ml WHERE ml.EMail = cc.Email)


?
Go to the top of the page
 
billwild
post Feb 21 2020, 05:51 PM
Post#12



Posts: 125
Joined: 4-June 02



Were getting there but I want to append all the records from CoffeeCup that don't have matching email not just the email

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
MadPiet
post Feb 21 2020, 05:54 PM
Post#13



Posts: 3,486
Joined: 27-February 09




INSERT INTO MasterListing(EMail, FirstName, LastName)
SELECT EMail, FirstName, LastName
FROM CoffeeCup cc
WHERE NOT EXISTS (SELECT 1 FROM MasterListing ml WHERE ml.EMail = cc.Email)

OMG. use your head. Instead of just EMAIL in both the MasterListing(…) and CoffeeCup(…) tables, add ALL THE COLUMNS TO BOTH LISTS.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th February 2020 - 02:50 PM