Pls correct this query

[subs]

SELECT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

I have this query which returns all records with same shipdate, same
consignee, same ozip , same dzip but Different BL.

The above query is returnign records which satisfy the above condition
but giving me DUPLICATE RECORDS. For example a record which has to be
there only once is coming up twice. Can anyone pls correct the query.
Is it because of type of Joins.


Thanks for the help

 

[Ken Snell [MVP]]

SELECT DISTINCT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

Note that the above query will eliminate duplicates, but will not be
updatable.

 

[subs]

SELECT DISTINCT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

Note that the above query will eliminate duplicates, but will not be
updatable.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -

Thanks But why was the query initially giving me duplicates?

 

[Ken Snell [MVP]]

Without knowing your table structures and data, it's hard to give a
definitive answer. But usually one gets duplicates because you are not
joining on enough fields so that you get unique matching, or you have
duplicate data in one table.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT DISTINCT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

Note that the above query will eliminate duplicates, but will not be
updatable.
--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -

Thanks But why was the query initially giving me duplicates?

 

[John W. Vinson]

Thanks But why was the query initially giving me duplicates?

Because it's joining the records on Ship_Date, Consignee, OZip and DZip...

and if there are multiple values of BL, it's giving you *every possible
combination* of those values.

Let's say you have three records which match on the four fields, but have BL
values 1, 2 and 3. You'll get 1 paired with 2, 1 paired with 3, 2 paired with
1 (again, it's unequal!), 2 paired with 3, 3 paired with 1 (again), and 3
paired with 2 (again).

If you have more records, you'll get more pairings.