Delete entries from a table that are not in other table

[ennrike]

Hi,

I have a Table1 and Table2.

They have a relationship with the column "WorkOrder". Table1 has all "WorkOrders" and Table2 has most of them (but not all).

I would like to delete from Table1 the "WorkOrders" that are not in Table2.

I think it would be something like that but I am not sure.

DELETE Table1.* FROM [Table1] INNER JOIN [Table2] ON Table2.WorkOrder WHERE ????? <<< I don't know what should I put here ...

Could someone help me?

Thanks

 

[RuralGuy]

Use the Unmatched query wizard.

 

[ennrike]

Thanks, actually I have been trying that feature but I have a more complex situation now,

I would like also to limit my query using a second column in Table2 that has the Date where the WorkOrder was started.

I would like also to narrow down my result to those WorkOrders that started before 2010, I have already tried to add "AND Date < #1/1/2010# " but I am still getting in the result table WorkOrders that were started in 2011 and 2010, (I should be getting 2009 and before only)

How can I add a third condition to the query?

 

[spikepl]

Suggestion: Go into the QBE-window and build a SELECT query, that shows only the records you want to delete. This allows you to fiddle one condition at a time etc. Once that works as required, then change the query to a DELETE query.

 

[ennrike]

Actually I am doing it like that, that's why I am saying that still can see in the SELECTION query some records with DATE >= 2010 when should I be getting only DATE < 2010

 

[bob fitz]

Can you poat the SQL for the query?

 

[spikepl]

Maybe Date is killing you. Date is a reserved word and should not be used. Rename that column into eg OrderDate

And ARE you working in the QBE -window? Then I don't quite understand your question about adding a condition - you just insert it into the grid: is there a problem there?

 

[spikepl]

Also, if both tables have an OrderDate, then your condition surely must be TableX.OrderDate<#some_date#, since both tables have an order date. So that's also why I wonder whether you are working in the quey designer

 

[ennrike]

Here it is the complete query, some notation may not have sense for you but the database was already created when I started to work with it.

Table1, Table2 and Table3 has a common relationship called "ODT" (WorkOrder), but Table3 has a column called "TDate". I want to show the ODTs that are in Table1 but not in Table2 and TDate be lower than 2010, but I am still getting in the result table ODTs that have records in Table3 before 2010 (I should be getting 2009 or lower)

I could get the records that are in Table1 but not in Table2 with this and it is workin ok:

SELECT DISTINCT Table1.ODT
FROM (Table1 LEFT JOIN Table2 ON Table1.ODT = Table2.ODT)
WHERE (((Table2.ODT) Is Null)

But if I want to add the date, it does not work:

SELECT DISTINCT Table1.ODT
FROM (Table1 LEFT JOIN Table2 ON Table1.ODT = Table2.ODT) INNER JOIN Table3 ON Table1.ODT = Table3.ODT
WHERE (((Table2.ODT) Is Null) AND ((Table3.TDate)<#1/1/2010#));

 

[spikepl]

I dont' get it - you say Table3 has a Tdate, but in your query you use a condition on Table3.StartDate

 

[ennrike]

Sorry my friend I have made the corrections, if you reload the page ...

 

[spikepl]

can you post a screen shot of the relations in the query window?

 

[ennrike]

I could not paste the image.

Two lines come out from Table1. One line for Table2 and other one for Table3, there is a "1" for Table1 and there is a infinity symbol for Table2 and Table3 and the end of the lines.

So that, the main Table is Table1 where ODT is the primary Key nor repeatable and for Table2 and Table3 ODT can repeat. Table2 and Table3 has their own primary key a random integer.

 

[spikepl]

My mind is blown at this time, that's why I needed an image. I have to do some of my own work now, so can't sort it out for you right now. But hang in there, plenty of much more knowledgeable people than me are bound to pass by shortly