Improve company productivity with a Business Account.Sign Up

x
?
Solved

Eliminating records from filea that match fileb - Access 2007

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

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

595 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