Pls help with SQL query

[subs]

ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f catsur 1/1/2008
h f g d e f catsurd 1/2/2008
p k l o z t cat 1/2/2008
l st mn d e f cat1 1/2/2008
otp ltp mtp dsc sto ppp cat2 2/3/2008
olm tma ots otaa iiiip ddddds sss 2/3/2008
l st mn d e f catsur 1/2/2008


I have the above data in a table called as p. The table has fields
like ocity, ost, ozip, dcity, dstate,dzip, consignee and shipdate.

I want to write a query which can extract all rows which has the SAME
dcity,dstate,dzip,consignee and shipdate but DIFFERENT ocity,ost and
ozip. For the consignee field, the query needs to check the first
six characters,- if they are same they are considered same or else
different. In the above example , the query should return the first,
second and the last record.

Can somebody pls help me with a SQL statement which can extract such
records , sorted such that we could see the result immediately.

Thanks- i will appreciate it.

 

[Lord Kelvan]

do you want to see

dcity dstate dzip consignee shipdate
d e f catsur 1/1/2008


or

ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f catsur
1/1/2008
h f g d e f catsurd
1/2/2008
l st mn d e f catsur
1/2/2008

what if your data was



ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f
catsur 1/1/2008
h f g d e f
catsurd 1/2/2008
p k l o z t
cat 1/2/2008
l st mn o z t
cat 1/2/2008
otp ltp mtp dsc sto ppp cat2
2/3/2008
olm tma ots otaa iiiip ddddds sss
2/3/2008
l st mn d e f catsur
1/2/2008

do you want
ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f
catsur 1/1/2008
h f g d e f
catsurd 1/2/2008
l st mn d e f catsur
1/2/2008
p k l o z t
cat 1/2/2008
l st mn o z t
cat 1/2/2008

please inform

reagrds
kelvan

 

[Lord Kelvan]

as a note you will only get the second and last row as the first row
is 1/1/2008 not 1/2/20008

 

[Lord Kelvan]

regardless while i haev been waiting for you i developed a method to
do the last option

ocity ost ozip dcity dstate dzip consignee shipdate
h f g d e f catsurd 2/01/2008
l st mn d e f catsur 2/01/2008
p k l o z t cat 2/01/2008
l st mn o z t cat 2/01/2008

this was my result

i used two queries

query 1

SELECT p.dcity, p.dstate, p.dzip, Left([consignee],6) AS consign,
p.shipdate, Count(p.dcity) AS CountOfdcity
FROM p
GROUP BY p.dcity, p.dstate, p.dzip, Left([consignee],6), p.shipdate
HAVING (((Count(p.dcity))>1));

save this query as qrypgroups

this query finds all records with like values and groups them finding
duplicates

query2

SELECT p.ocity, p.ost, p.ozip, p.dcity, p.dstate, p.dzip, p.consignee,
p.shipdate
FROM p INNER JOIN qrypgroups ON (p.dcity = qrypgroups.dcity) AND
(p.dstate = qrypgroups.dstate) AND (p.dzip = qrypgroups.dzip) AND
(p.shipdate = qrypgroups.shipdate)
WHERE (((p.consignee) Like [qrypgroups].[consign] & "*"))
ORDER BY p.dcity, p.dstate, p.dzip, p.shipdate;

save this and whatever this is the query you use

this query returns all values comparing them against the values in the
first qurey to give you the above results

hope this helps

as i said if this isnt what you want please inform me

Regards
Kelvan

 

[subs]

as a note you will only get the second and last row as the first row
is 1/1/2008 not 1/2/20008

Hi Lord

I want the last option that you have typed- ofcours the date has to be
same. For consignee- it has to check the first six characters.
Like this below. It should have been sorted out such that we could see
the below result immediately.


ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f
catsur 1/2/2008
h f g d e f
catsurd 1/2/2008
l st mn d e f catsur
1/2/2008
p k l o z t
cat 1/2/2008
l st mn o z t
cat 1/2/2008


I would really appreciate yr help

thanks

 

[Lord Kelvan]

i have already posted the solution for that one above your post have a
look try it out and see it if works i have tested it with the data
you provided but it is obvious that the data you provided is fake so
it may not work the same in reality

 

[subs]

regardless while i haev been waiting for you i developed a method to
do the last option

ocity ost  ozip  dcity  dstate  dzip  consignee shipdate
h       f      g      d       e           f     catsurd    2/01/2008
l       st     mn   d       e           f    catsur     2/01/2008
p      k      l       o        z          t     cat        2/01/2008
l      st     mn    o        z          t    cat       2/01/2008

this was my result

i used two queries

query 1

SELECT p.dcity, p.dstate, p.dzip, Left([consignee],6) AS consign,
p.shipdate, Count(p.dcity) AS CountOfdcity
FROM p
GROUP BY p.dcity, p.dstate, p.dzip, Left([consignee],6), p.shipdate
HAVING (((Count(p.dcity))>1));

save this query as qrypgroups

this query finds all records with like values and groups them finding
duplicates

query2

SELECT p.ocity, p.ost, p.ozip, p.dcity, p.dstate, p.dzip, p.consignee,
p.shipdate
FROM p INNER JOIN qrypgroups ON (p.dcity = qrypgroups.dcity) AND
(p.dstate = qrypgroups.dstate) AND (p.dzip = qrypgroups.dzip) AND
(p.shipdate = qrypgroups.shipdate)
WHERE (((p.consignee) Like [qrypgroups].[consign] & "*"))
ORDER BY p.dcity, p.dstate, p.dzip, p.shipdate;

save this and whatever this is the query you use

this query returns all values comparing them against the values in the
first qurey to give you the above results

hope this helps

as i said if this isnt what you want please inform me

Regards
Kelvan

hi

I tried this but when i run the second query- i get fourth
record( from my original example) and last record- something which i
donot need at all. It is giving l, st mn records. Can you help pls?

 

[subs]

i have already posted the solution for that one above your post have a
look try it out and see it if works  i have tested it with the data
you provided but it is obvious that the data you provided is fake so
it may not work the same in reality

I tried this but when i run the second query- i get fourth
record( from my original example) and last record- something which i
donot need at all. It is giving l, st mn records. Can you help pls?

 

[subs]

I tried this but  when i run the second query- i get fourth
record( from my original example) and last record- something which i
donot need at all.  It is giving l, st mn records. Can you help pls?

OH I HAVE TO CLARIFY- OCITY, OST, OZIP HAVE TO BE DIFFERENT AMONG
DIFFERENT RECORDS BUT DCITY,DST,DZIP,SHIPDATE,CONSIGNEE HAVE TO BE
SAME. IT CANNOT BE VICE VERSA

 

[subs]

I tried this but  when i run the second query- i get fourth
record( from my original example) and last record- something which i
donot need at all.  It is giving l, st mn records. Can you help pls?

I HAVE TO CLARIFY- OZIP, OST, OCITY HAVE TO BE DIFFERENT AMONG
DIFFERENT RECORDS BUT DCITY , DSTATE, DZIP,CONSIGNEE AND SHIPDATE HAVE
TO BE SAME - IT CANNOT BE VICE VERSA.

 

[Lord Kelvan]

are you sure when i tested it on that data i only got the two records
the second and the last

make sure you scopied and pased the two queries exatally

 

[ram8595]

are you sure when i tested it on that data i only got the two records
the second and the last

make sure you scopied and pased the two queries exatally

yes i pasted it correctly and ran the second query Also I HAVE TO
CLARIFY- OZIP, OST, OCITY HAVE TO BE DIFFERENT AMONG
DIFFERENT RECORDS BUT DCITY , DSTATE, DZIP,CONSIGNEE AND SHIPDATE
HAVE
TO BE SAME - IT CANNOT BE VICE VERSA.

 

[Lord Kelvan]

i know and thats how i built the query thats why query 1 selects the d
information not the o information paste the results of the second
query so i can see what the fault may be

Regards
Kelvan