Deleting similar Dups

[Chippie]

Thanks to Sawatzky I now have a table with records in which contain either 2 options BILL and SHIP selected (tick) or one option SHIP selected. Unfortunately the Items which have SHIP and BILL have been duplicated also with only SHIP selected. Therefore I need to know how to delete the records which are duplicated. I have no primary keys, so I am a bit **foobarred**. I have tried distinct, but it selects all the records because the tick boxes are disfferent on the dup records

 

[Sawatzky]

Dear chippie

gotcha

1)The records with only ship selected, as far as I remember they should have the bill field = NULL

if it is like that you could query for that criterion.

2)On the other hand if you do not display the ship and bill field in a

Select distinct customername,street from customers

statement, you should only get the records back once.

3) Did you read my message about how to create a primary key on several fields?

regards Astrid

 

[Chippie]

Sawatzky, I read your email but many of the ship/bill addresses have the same place, therefore making it impossible to apply the key field trick. Bummer. Ummm, I tried your code, thankyou again, but without the primary keys it added the bill info - great, but when adding the ship info, it added the data to the allready created bill record, plus added another record with only the ship information. I'm going nutz I tell you. I've been doing this for 2 days now, and it's only about 5 lines SQL !!!!

 

[Sawatzky]

Dear chippie,

try if the following select statement query the duplicates:

select * from adrbillship where bill is NULL;

if so make a delete query from this.

you need not go nutz as I am on your side , holding your hand, trying to get you out.

If that doesn't help either, mail me your original table (that which caused all of this), and I'll have a look at it.

e.mail: assawatzky@compuserve.de

regards Astrid