Solved

Eliminating records from filea that match fileb - Access 2007

Posted on 2011-02-18
4
253 Views
Last Modified: 2012-08-13
I have two tables that I can match by ID.  If the ID on table A is found on Table B, I do not want to include the record on Table C.  If there is no match that I would like to include the record on Table C.
How can I do this is Access?  
0
Comment
Question by:morinia
  • 2
4 Comments
 
LVL 12

Expert Comment

by:geowrian
ID: 34931425
Did you try this?

INSERT INTO TableC(field1, field2, etc)
SELECT field1, field2, etc FROM Table1
LEFT OUTER JOIN Table2
ON Table1.ID=Table2.ID
WHERE Table2.ID IS NULL

Open in new window

0
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 500 total points
ID: 34931426
SELECT * FROM TableA WHERE TableA.ID NOT IN (SELECT ID FROM TableB)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34931429
does tablec already exists?

if it is, using an append query

insert into tablec
select tablea.* from tableA  as A
left join tableB as B On A.ID=B.ID
where B.ID is null
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34931436
if tableC does not exists, use a make table query

select A.* into tableC
from tableA as A
left join tableB as B On A.ID=B.ID
where B.ID is null
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copied Report Does not run on a different Query Source ? 7 33
Access 2010 change CurrentUser 5 36
MkDir to create multiple folder and subfolder 2 41
IIF in access query 19 27
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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