Looping code to exclude duplicates

[tizwaz]

Can anyone help. I have the following code which loops through a recordset and emails a report covering the departments to their budget holder. Some budget holders have more than one department. The details of the departments and budget holders email address are in a table. I have the following on the click event of a command button. it works fine in that it emails each budget holder the relevant report but as it loops through the list it emails the same report more than once to the same person as they appear in the list of dept and budget holders more than once. how can I exclude the duplicates?

Private Sub Command3_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryCostCentreMultiple", dbOpenDynaset)
rs.MoveFirst
Do
vOfficeID = rs("email")
DoCmd.SendObject acSendReport, "rptAutoEmailMultiple", "pdfFormat(*.pdf)", rs("Email"), "", "", "Monthly Report", "Please find attached a breakdown of the current details for your Cost Centres., True
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close
End Sub

The SQL of qryCostCentreMultiple is

SELECT TblVodafoneList.[Mobile Number], TblVodafoneList.[Subscriber Name], TblStockLocation.StockLocation, TblVodafoneList.[Next Upgrade Date], TblVodafoneList.[SIM Number], TblVodafoneList.[Subscriber status], TblPricePlan.Device, TblStockLocation.StockLocation, TblVodafoneList.[Price Plan], TblCostCentre.[Cost Centre], TblCostCentre.Dept, qryCostCentreMultiple.Email
FROM (((TblVodafoneList LEFT JOIN TblStockLocation ON TblVodafoneList.Stock = TblStockLocation.StockLocationCode) INNER JOIN TblPricePlan ON TblVodafoneList.[Price Plan] = TblPricePlan.PricePlan) INNER JOIN qryCostCentreMultiple ON TblVodafoneList.[Cost Code 1] = qryCostCentreMultiple.[Cost Centre]) INNER JOIN TblCostCentre ON (qryCostCentreMultiple.[Cost Centre] = TblCostCentre.[Cost Centre]) AND (TblVodafoneList.[Cost Code 1] = TblCostCentre.[Cost Centre])
WHERE (((qryCostCentreMultiple.Email)=OfficeID()))
ORDER BY TblVodafoneList.[Subscriber Name];

 

[dhookom]

Do you need all those fields in the query? Can you eliminate some and change the query to a group by?

Duane
Hook'D on Access
MS Access MVP