Need help with simple query. Pls read.

[skuba2004]

Hi,
I don't know how to create a simple query that will compare 2 tables and create a new one after that.
Each table has only one field.

Example:

Table 1 has records A, B, C, D and E
Table 2 has records A and C

I want new table to be like table 1 minus table 2. Like cleaning up table 1.
New table would have records B, D and E

I think it's very simple but I can figure how to do it.

Could you pls give me advice?

Thanks a lot,

skuba

 

[phil009]

Skuba-In the Table 1 Criteria why don't you just search for records B,D, and E and then either manually save the results as Table 3 or write a macro that will save them as a new table.

Hope this helps,
Phil

 

[skuba2004]

Phil009, thanks, but of course my table is not only with values A, B, etc...

This was an example to illustrate what kind of job I want to perform.

One table has actually 2k records and the other 300

I want to have a new table with all record in table 1 that are not in table 2.

Get it?

Thanks

 

[lespaul]

SELECT * FROM TABLE1 WHERE PKFIELD NOT IN (SELECT PKFIELD FROM TABLE2)

this query will return all the records in table one that are not in table 2.

replace the TABLE1 and TABLE2 with your tablenames and the PKFIELD with the Primary Key field from the two tables.

HTH

leslie

 

[dhookom]

Have you looked at using the unmatched query wizard? It does exactly what you want.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[skuba2004]

I got it!!!! Thanks dhookoom.
Got it with unmatched query.

 

[James529]

skuba2004 ,


You could also try the Query Wizard and use the "Find Unmatched" wizard option. This always has worked well for me.

HTH2

James