Delete query not working

[MadCat]

Hi all,

I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.

I have a table called [PMI] and a table called [Referrals]

The tables have a one to many relationship and are linked by the primary key field Patient_No.

I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.

Hope that makes sense, all help is greatly appreciated.

Thanks in advance.

 

[Len Boorman]

Sometimes these can be a bit touchy particularly when you are using joined tables.

The way I normally get around this is to have a file in the table named Delete. text single character default N

Then use an update query to update this field to Y. This would be based on your select query which contains those PK's that you want to remove

Then run a Delete query purely over the table concerned where Delete =Y

You can link these queries to an event on a form if you wish. Develop and test it on a COPY of the real database then if you have an odd glitch you will not kill your live data

Len B

 

[MadCat]

Cheers Len,

That sounds like a good way to go. it will certainly do what i need to.

Thanks for your help, it appreciated.

 

[EMP]

My favourite delete query since Visual FoxPro 3.0

DELETE *
FROM [PMI]
WHERE [Patient_No] not in (Select [Patient_No] from [Referrals]);


^

 

[MadCat]

EMP,

Superb!, thats even better. This does exactly as requested without the need for any extra fields.

Cheers