Query Help

[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.

 

[Lord Kelvan]

you told me you want all records that have

dcity dstate dzip consignee shipdate

the same am i correct. Based on that data the first three are the
same so you get thoes records and the 4th and 5th are the same so you
get thoes records. You cannot have just the first three or the 4th
and 5th without some other condition BECAUSE they are the same in some
retrospect

if you one want the first three records then you have to supply a
condition on why the first three should be selected because as i said
the 4th and 5th are the same the query is not intelligant it only
follows orders and your request was records that have the same dcity
dstate dzip consignee shipdate.

So please tell me what the condition is other than the samedcity
dstate dzip consignee shipdate so the query can be altered to supply
you with only the first three because ti is impossible to just supply
the first three without a condition other than the one stated above.

regards
Kelvan

 

[Lord Kelvan]

ok based on the different condition i added another query

SOOOO listen up i am only going to say this once

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


query2

SELECT p.ocity, p.ost, p.ozip, Count(p.ocity) AS CountOfocity
FROM p INNER JOIN qrypgroups ON (p.shipdate = qrypgroups.shipdate) AND
(p.consignee = qrypgroups.consign) AND (p.dzip = qrypgroups.dzip) AND
(p.dstate = qrypgroups.dstate) AND (p.dcity = qrypgroups.dcity)
GROUP BY p.ocity, p.ost, p.ozip
HAVING (((Count(p.ocity))>1));

save this query as qrypogroups


and the big one

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

save this as whatever this is the one you run

as a note for the last query because of the <> in the inner join you
cannot view this in design view only sql view

that provides only the first three records

hope this helps

Regards
Kelvan