Excluding records from a table

[exRP12Nuke]

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!

 

[Andrzejek]

Do those table have any uqnique key that you can use?
If so, I usually start getting the records that I want to exclude:
[tt][blue]
Select SomeID From Table2
Where ...[/blue]
[/tt]
Then I just get the records I want using:
[tt]
Select * from Table 1
Where SomeID NOT IN([blue]
Select SomeID From Table2
Where ...[/blue])[/tt]

Have fun.

---- Andy

 

[exRP12Nuke]

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!

 

[exRP12Nuke]

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

SELECT tblHist.WONO, tblHist.CompCode, tblHist.CPTMD3, *
FROM tblHist
WHERE (((tblHist.WONO) Not In (Select WONO from qryWarrWo)) AND ((tblHist.CompCode) Not In (Select CompCode from qryWarrWO)) AND ((tblHist.CPTMD3) Not In (select CPTMD3 from qryWarrWO)));

Thanks

 

[Andrzejek]

Let's take it one step at the time.
When you do:
[tt]
Select WONO from qryWarrWo
[/tt]
How many records do you get?

When you do:
[tt]
SELECT *
FROM tblHist
[/tt]
How many records do you get?

When you do:
[tt]
SELECT *
FROM tblHist
WHERE WONO Not In
(Select WONO from qryWarrWo)
[/tt]
How many records do you get?


Have fun.

---- Andy

 

[exRP12Nuke]

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!

 

[Andrzejek]

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

Select [blue]DISTINCT[/blue] WONO From qryWarrWO
Select [blue]DISTINCT[/blue] * From tblHist
Select [blue]DISTINCT[/blue] * From tblHist Where Not In (Select [blue]DISTINCT[/blue] WONO from qryWarrWO)

the numbers, math, should be easier.

Have fun.

---- Andy

 

[PHV]

Why not simply follow the unmatched query wizard ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[exRP12Nuke]

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