select statement

[Guest]

I have several queries that are used as the source for some mailing label
reports. I need to make sure only one label is printed per vendor_nbr (this
is not a unique field in the database). How can I make sure only one label
is selected if multiple records are selected with the same vendor-nbr? Here
is my sql behind my query:

SELECT tbl_Vendor.Local_Agency_Code, tbl_Vendor.Vendor_Nbr,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Vendor_Name, tbl_Vendor.M_Line_One,
tbl_Vendor.M_Line_Two, tbl_Vendor.M_City, tbl_Vendor.M_State,
tbl_Vendor.M_Zip_Code
FROM tbl_Vendor, tblCount
WHERE (((tbl_Vendor.Chain_Suffix)<>0) AND ((tblCount.CountID)<=[How many
labels are needed per vendor?]) AND ((tbl_Vendor.Region_Code)="0") AND
((tbl_Vendor.Peer_Group_Code)=11) AND ((tbl_Vendor.Status_Code)="active") AND
((tbl_Vendor.Label_Code)="1"))
ORDER BY tbl_Vendor.Local_Agency_Code, tbl_Vendor.Vendor_Nbr;

Thanks for any help!

 

[Jeff L]

Put the word DISTINCT after Select.

 

[Guest]

Adding the DISTINCT like below will eliminate duplicates. However if even on
piece of data in any of the selected fields is different, it will still show
up.

SELECT DISTINCT tbl_Vendor.Local_Agency_Code,
tbl_Vendor.Vendor_Nbr,
tbl_Vendor.Chain_Suffix,
tbl_Vendor.Vendor_Name,
tbl_Vendor.M_Line_One,
tbl_Vendor.M_Line_Two,
tbl_Vendor.M_City,
tbl_Vendor.M_State,
tbl_Vendor.M_Zip_Code
FROM tbl_Vendor,
tblCount
WHERE (((tbl_Vendor.Chain_Suffix)<>0)
and ((tblCount.CountID)<=[How many labels are needed per vendor?])
and ((tbl_Vendor.Region_Code)="0")
and ((tbl_Vendor.Peer_Group_Code)=11)
and ((tbl_Vendor.Status_Code)="active")
and ((tbl_Vendor.Label_Code)="1"))
ORDER BY tbl_Vendor.Local_Agency_Code,
tbl_Vendor.Vendor_Nbr;

 

[Allen Browne]

Create a query that uses just tbl_Vendor (not tblCount.)
In query design, open the Properties box (View menu.)
Set the Unique Values property to Yes.
(If you don't see this property in the Properties box, you may be looking at
the properties of a field, instead of the properties of the query.)

Check that this query generates only one record per vendor, and save the
query.

You can then use that query as an input "table" for another query, and add
tblCount as well so you get the number of labels you want.