deleting records 1

[gavinjb]

Hi,

I am trying to write a query to delete all "TAKE FOR MOT TEST" records that have both "TAKE FOR MOT TEST", "CARRY OUT MOT TEST" on the same Vehicle Registration Number, Can any one give me any help please.

The Fields that I have which must be taken into account are RegNo, Actual-Date and Work-Type

Gavin,

 

[Crowley16]

eh, for clarification:

all the information is in 1 table?
each record has 1 regNo?
Actual-Date contains TAKE FOR MOT TEST?
Work-Type contains CARRY OUT MOT TEST

if the above is true, then use the below in a query:

DELETE FROM tblName WHERE [Actual-Date] = "TAKE FOR MOT TEST" AND [Work-Type] = "CARRY OUT MOT TEST";

 

[gavinjb]

Sorry I didn't put that vary clearly, all the data is in one table (data import table)

Both "TAKE FOR MOT TEST" and "CARRY OUT MOT TEST" are in the [Work-Type] field, The Reg No can be on multiple Record. I need to be able to delete only records that have the "TAKE FOR MOT TEST" Work Type for Records with Both Work Types on the Same Reg No with the same Actual-Date.

Gavin,

 

[Crowley16]

right, so your data is like:

RegNo Actual-Date Work-Type
123 01/01/01 CARRY OUT MOT TEST
123 01/01/01 TAKE FOR MOT TEST
... ... ...

and you want to delete only the records that have same regNo and Actual-Date and both Work-Types...

hmm, tricky... try something like:
DELETE FROM tblName AS a INNER JOIN tblName AS b ON a.RegNo = b.RegNo AND a.[Actual-Date] = b.[Actual-Date]
WHERE a.[Work-Type] = 'CARRY OUT MOT TEST' AND b.[Work-Type] = 'TAKE FOR MOT TEST';

 

[gavinjb]

Thanks that worked perfectly