finding duplicates in two different objects (1 Viewer)

[mariaw]

Hi

Is there a way of finding duplicate field entries in a table and a query: e.g.

In the Sickness Query (which contains, say 10 fields), in the Section field, it says "Benefits", and in the Week Commening field, it says "15/10/06"

And then

In the Nil Return Table (which only contains the Section & Week commencing fields), they also say "Benefits" and "15/10/06"

If there is a way of finding them, how do I delete the entry that has been made in the Nil return table?

Thanks

Maria

 

[ByteMyzer]

If there is a way of finding them, how do I delete the entry that has been made in the Nil return table?

If you mean to delete the entry in the Nil table itself, where a matching entry exists in the Sickness Query, use the following SQL (substitute actual names in the bracketed text):

DELETE T1.*
FROM [Nil] AS T1
WHERE EXISTS
 (SELECT T2.*
  FROM [Sickness Query] AS T2
  WHERE T2.[Section]=T1.[Section]
  AND T2.[Week Commencing]=T1.[Week Commencing]
 )
;

 

[mariaw]

Hi ByteMyzer

I have put this in (see below): the table that I want to delete from is called NIL RETURN - but it says there is a problem with the FROM clause...? If I put brackets round the Nil Return after the AS, it still doesn't like it??



DELETE Nil Return.*
FROM [Nil Return] AS Nil Return
WHERE EXISTS
(SELECT Sicknes Query.*
FROM [Sickness Query] AS Sickness Query
WHERE Sickness Query.[Section]=Nil Return.[Section]
AND Sickness Query.[WeekCom]=Nil Return.[WeekCom]
);


Any ideas?

Thanks

Maria

 

[ByteMyzer]

Please be aware that it is poor naming convention to have spaces in table/field names. In Microsoft Access, if there MUST be a space in a table/field name, it must be enclosed in braces. Try modifying your SQL statement thus:

DELETE NR.*
FROM [Nil Return] AS NR
WHERE EXISTS
 (SELECT SQ.*
  FROM [Sickness Query] AS SQ
  WHERE SQ.Section=NR.Section
  AND SQ.WeekCom=NR.WeekCom
 );

 

[mariaw]

Fab!! Thanks Byte Myzer - that worked!!

Maria