Delete Query in access

[Sreedevi]

Hi All,

I have a table with the fileds Style#, DC#, Reciept Date and some other fields.

I have two similar rows having all the columns same but one row has Receipt date NULL and one row has some value in Receipt date.

If for same style# and Dc# combination two rows exists with Reciept Date as NULL and with some value, I want to delete the row that has Receipt date as NULL for same combination.

I cant do as DELETE * FRom table where [Receipt Date] AS NULL

Because there are some Style# and Dc# combinations which doesn't have Reciept Date at all.

Can any body help me out?

Thanks in advance

 

[asather]

Sreedevi,

I think the way you built this will require a more complex solution than a simple query. See if what I pasted below works for you.

DELETE tblStyles.[Stlye#], tblStyles.[DC#], tblStyles.[Reciept Date], tblStyles.ID
FROM tblStyles
WHERE (((tblStyles.[Stlye#]) In (SELECT [Stlye#] FROM [tblStyles] As Tmp GROUP BY [Stlye#],[DC#] HAVING Count(*)>1 And [DC#] = [tblStyles].[DC#])) AND ((tblStyles.[Reciept Date]) Is Null));

I just used the Query Wizard to create a Find Duplicates Query, which looks for duplicate combinations of Stlye# and DC#. Once it created that, I added the criterion of is null to the Receipt Date field, and changed the query type to a delete query.

 

[Sreedevi]

Thanks a lot asather..

It is working great..