deleting duplicates but with a condition

[subs]

ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f cat 1/2/2008
a b c d
e f cat 1/2/2008
f g o l
m n cat1 2/4/2008
f1 g1 o1 l
m n cat1 2/4/2008
f1 g1 o1 l
m n cat1 2/4/2008

I want to delete the first two records in this table since they donot
have any other record with SAME dcity,dstate,dzip ,consignee ,shipdate
but DIFFERENT ocity,ost and zip. On the other hand , the last two
records have another matching record(the third one) which have SAME
dcity,dstate,dzip ,consignee ,shipdate but DIFFERENT ocity,ost and
zip. IS THERE ANY SQL QUERY or Delete Query with a condition.PLS HELP

 

[Lord Kelvan]

the first two records are the same as one another

SELECT yourtableid, ocity, ost, ozip, dcity, dstate, dzip, consignee,
shipdate
FROM yourtable
WHERE (((dcity) In (SELECT dcity
FROM yourtable
GROUP BY dcity, dstate, dzip, consignee, shipdate
HAVING (((Count(yourtableid))>1)))) AND ((dstate) In (SELECT dstate
FROM yourtable
GROUP BY dcity, dstate, dzip, consignee, shipdate
HAVING (((Count(yourtableid))>1)))) AND ((dzip) In (SELECT dzip
FROM yourtable
GROUP BY dcity, dstate, dzip, consignee, shipdate
HAVING (((Count(yourtableid))>1)))) AND ((consignee) In (SELECT
consignee
FROM yourtable
GROUP BY dcity, dstate, dzip, consignee, shipdate
HAVING (((Count(yourtableid))>1)))) AND ((shipdate) In (SELECT
shipdate
FROM yourtable
GROUP BY dcity, dstate, dzip, consignee, shipdate
HAVING (((Count(yourtableid))>1)))))
GROUP BY yourtableid, ocity, ost, ozip, dcity, dstate, dzip,
consignee, shipdate;

that query should select the values that are duplicates if you have a
problem please dont keep creating threads if you cannot use the
solution people provide or it dosnt work after the 3rd or 4th thread
maybe there isnt a solution i have tested that query with the data and
it works

regards
Kelvan

 

[Lord Kelvan]

as a note

I have provided you queries before that have worked but you said they
didnt the reason they might not work may be because the data you are
supplying is not a relevant representation of your actual data because
that coudl be the problem.

If this dosnt work please provide some real data for us to build a
query from if it is a confidanality issue then email me the data in a
spreadsheet and ill take a look.

Regards
Kelvan

 

[subs]

as a note

I have provided you queries before that have worked but you said they
didnt the reason they might not work may be because the data you are
supplying is not a relevant representation of your actual data because
that coudl be the problem.

If this dosnt work please provide some real data for us to build a
query from if it is a confidanality issue then email me the data in a
spreadsheet and ill take a look.

Regards
Kelvan

hi

it is asking me to enter parameter value - yourtableid. what should i
do?

Thanks but what is

 

[Lord Kelvan]

replace the yourtable and the yourtableid with whatever the
crrsopnding values are

if your table name is cheesepuff then replace the yourtabl with
cheesepuff if the primary key field is names cheesepuffid then replace
the yourtableid with cheesepuffid

if you dont have a primary key field then replace yourtableid with
ocity or something

regards
kelvan

 

[John W. Vinson]

it is asking me to enter parameter value - yourtableid. what should i
do?

Replace "yourtableid" with the name of the Primary Key field of your table.

Lord Kalvan (and the rest of us) cannot see your screen; we do not know your
tablenames or fieldnames, so in order to communicate, we'll all use made-up
names that hopefully can be adapted to your needs.

If your table does not HAVE a primary key then you may be in real trouble.
Perhaps you could post the complete structure of your table (all the
fieldnames), and identify which - if any - is the primary key.