Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Eliminating records from filea that match fileb - Access 2007

Posted on 2011-02-18
4
Medium Priority
?
268 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

972 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