Eliminating Duplicates from the query

[wally2321]

Can anyone please help!

This is probably an easy question that everyone has an answer for... I hope so.. I am a rookie, so please understand.

I created a table with values. This table is called ....FLORENCE - MATERIAL - MAKE - KAUF (PDM). In this table is the field called OLD PART NUMBER, which is the field I would like to reference.

I created a new query using the MAKE - Query function, utilizing the design view functionality (Easier for me). The source table is called FLORENCE - KAUF - SCRUBBER TABLE. I would like to only display the records that exist in the FLORENCE - KAUF - SCRUBBER TABLE but do not exist in the FLORENCE - MATERIAL - MAKE - KAUF (PDM) TABLE. I know how you can develop a link to show materials that match, but then those materials are shown. I want the opposite. I want the materials that match to be excluded from the finished product.

The common field in both tables is OLD PART NUMBER

Anyone have any ideas?

Any help would be greatly appreciated.

 

[dneufarth]

Have you tried the 'Find Unmatched Query' Wizard?


Dave

 

[wally2321]

I thought about the unmatched query but I do not think it will meet my needs. I am trying to create a make query, and while developing the make query built the logic that I have listed above.


Does anyone have any ideas?

 

[wally2321]

I thought about the unmatched query but I do not think it will meet my needs. I am trying to create a make query, and while developing the make query built the logic that I have listed above.


Does anyone have any ideas?

 

[tlbroadbent]

You can use the "Find Unmatched Query Wizard" to build the selection query. When the selection is corect, convert it to a make table query.

There are three simple ways to find unmatched records.

Select t1.*
From t1
Where t1.ID Not In (Select t2.ID From t2)

Select t1.*
From t1
Where t1.ID Not Exists (Select t2.ID From t2 Where ID=t1.ID)

Select t1.*
From t1 Left Join t2 On t1.id=t2.id
Where t2.id is null

Usually the third method is faster if the tables are indexed on the JOIN column. With small tables, there isn't much difference. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin