query for extracting some records

[subs]

ozip dzip BillofladingNo ship date transportprice Actual
Wgt
07022 19856 12345 9/12/06 56777
12390
09888 76555 94345 9/5/06 96734
13598
87566 86534 08888 7/5/07 9876
5647
07034 08638 12322 9/1/06 9654
34980
07022 19856 12378 9/12/06 98076
23890
07022 19856 12379 9/12/06 98567
14578
87566 86534 08888 7/5/07
998888 8977

the above table is just a sample of data. I want to select the records
with the same ozip, same dzip and with the same ship date but with
DIFFERENT Billoflading no. In the above example,The query result
should select the first record, fifth and sixth records only. It
should NOT select the third and seventh records(because it has same
billoflading no)

Can you please let me know what the query will be ?

Thanks in advance

 

[John W. Vinson]

ozip dzip BillofladingNo ship date transportprice Actual
Wgt
07022 19856 12345 9/12/06 56777
12390
09888 76555 94345 9/5/06 96734
13598
87566 86534 08888 7/5/07 9876
5647
07034 08638 12322 9/1/06 9654
34980
07022 19856 12378 9/12/06 98076
23890
07022 19856 12379 9/12/06 98567
14578
87566 86534 08888 7/5/07
998888 8977

the above table is just a sample of data. I want to select the records
with the same ozip, same dzip and with the same ship date but with
DIFFERENT Billoflading no. In the above example,The query result
should select the first record, fifth and sixth records only. It
should NOT select the third and seventh records(because it has same
billoflading no)

Can you please let me know what the query will be ?

Thanks in advance

One way would be to create a Self Join query. Add this table to the query grid
twice, giving each instance a different alias; join ozip to ozip, dzip to
dzip, and ship date to ship date, and select both Bill of Lading numbers. Put
a criterion on the second instance of BillOfLadingNo of

< [firsttablealias].[billofladingno]

You could use <> but that would give you each pair twice, once with the bill
of lading less and once with it greater.

Obviously if there are THREE records with the same fields you'ld get
multiples.