S
subs
This should clear out everything- i hope. Pls accept my apologies for
posting it many times today and the other day. I am combining
everything into a single problem
ocity ost ozip dcity dstate dzip consignee shipdate
h f g d e f catsurd 2/01/2008
h f g d e f catsurd 2/01/2008
l st mn d e f catsurd 2/01/2008
p k l o z t cat 2/01/2008
p k l o z t cat 2/01/2008
j m n y g q cat1 3/1/2008
i used two queries - i.e Kelvan gave me
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;
LET US ASSUME THE ABOVE TABLE IS THE ORIGINAL TABLE>
Now these two queries that you(Kelvan) wrote gives me the first five
records as output. But i want only first three records as output. The
fourth and fifth records are same but they donot have complimentary
record in the table with same dcity, dst, dzip,consignee, shipdate and
DIFFERENT ocity, ostate and ozip. So i donot want the fourth, fifth,
sixt th record.
i think you should just tweak the abouve two queriers. Can you give me
the changed queries.
posting it many times today and the other day. I am combining
everything into a single problem
ocity ost ozip dcity dstate dzip consignee shipdate
h f g d e f catsurd 2/01/2008
h f g d e f catsurd 2/01/2008
l st mn d e f catsurd 2/01/2008
p k l o z t cat 2/01/2008
p k l o z t cat 2/01/2008
j m n y g q cat1 3/1/2008
i used two queries - i.e Kelvan gave me
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;
LET US ASSUME THE ABOVE TABLE IS THE ORIGINAL TABLE>
Now these two queries that you(Kelvan) wrote gives me the first five
records as output. But i want only first three records as output. The
fourth and fifth records are same but they donot have complimentary
record in the table with same dcity, dst, dzip,consignee, shipdate and
DIFFERENT ocity, ostate and ozip. So i donot want the fourth, fifth,
sixt th record.
i think you should just tweak the abouve two queriers. Can you give me
the changed queries.