Good Morning,

I have two tables, table 1 and table 2. The records that are in table 1 are extensive and are the records that I need to use further in my database. The records in table 2 are the records that I want to exclude from table 1. I would be joining the two tables on the WorkOrder fields. What kind of query to I need to build to accomplish this?

Thanks!

Do those table have any uqnique key that you can use?
If so, I usually start getting the records that I want to exclude:

Select SomeID From Table2
Where ...

Then I just get the records I want using:

Select * from Table 1
Where SomeID NOT IN(
Select SomeID From Table2
Where ...)

Have fun.

---- Andy

Andrzejek,

Thank you for your response, unfortunatly my SQL is rather weak so a little more assistance would be greatly appreciated. The two tables that I am working with are tblHist and qryWarrWO. The records in tblHist is the complete list of records that I am working with, while the records in qryWarrWO are the records that I am trying to exclude. There are three unique records that I would need to "join" and they are WONO, CompCode, and CPTMD3.

Thanks again!

This is the SQL that I came up with, but it does not return any results.


Thanks

Let's take it one step at the time.
When you do:

Select WONO from qryWarrWo

How many records do you get?

When you do:

SELECT *
FROM tblHist

How many records do you get?

When you do:

SELECT *
FROM tblHist
WHERE WONO Not In
(Select WONO from qryWarrWo)

How many records do you get?

Have fun.

---- Andy

Andrzejek,

For Select WONO From qryWarrWO, it returns 79 records.

For Select * From tblHist, it returns 5897 records.

For Select * From tblHist Where Not In (Select WONO from qryWarrWO), it returns 5689 records.

There can be multiple CompCode and CPMTD3 for each WONO which is why the last SQL statement removes more than 79 records. For instance: Lets say that under WONO GT50000 they did an engine swap along with a left front wheel (I work for a heavy equipment dealer). So under WONO GT50000 there are CompCodes 1000 and 4006 with CPMTD3 LF. If WONO GT50000 shows up in qryWarrWO with CompCode 4006 and CPMTD3 LF I want that record to not show up, but leave the GT50000 CompCode 1000 in the final query.

Hope that explanation helps.

Thanks!

OK, you may get better handle of this stuff if you do:

Select DISTINCT WONO From qryWarrWO
Select DISTINCT * From tblHist
Select DISTINCT * From tblHist Where Not In (Select DISTINCT WONO from qryWarrWO)

the numbers, math, should be easier.

Have fun.

---- Andy

Why not simply follow the unmatched query wizard ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Thanks guys, the unmatched query with a few tweeks works great!