Query of many-to-many produces duplicates

[NZArchie]

Hi all, my current problem is that I have a many-to-many relationship which returns odd numbers of duplicates when queried. Each payment has a unique payment ID, but there are duplicates of this payment ID. I could just get the top 1 payment ID, but that seems like a patch rather than a fix?

The intermediate table is the Commissions table, with foreign keys of AdvisorID and policy Number

SELECT Payments.PaymentID, Payments.DateOfPayment, Payments.PayRunNo, Payments.AmountExGST, Payments.AmountIncGST, Payments.PolicyNumber AS Payments_PolicyNumber, Payments.PaymentMode, Comissions.AdvisorID, Comissions.SharePercentage, Advisors.FirstName, Advisors.LastName, IIf(([PaymentMode]='New Business' Or [PaymentMode]='Select New Business'),[Payments].[AmountExGST],0) AS NBExGST, IIf(([PaymentMode]='New Business' Or [PaymentMode]='Select New Business'),[Payments].[AmountIncGST],0) AS NBIncGST, IIf(([PaymentMode]='New Business' Or [PaymentMode]='Select New Business'),[Payments].[AmountIncGST]-[AmountExGST],0) AS NBGST, IIf(([PaymentMode]='New Business' Or [PaymentMode]='Select New Business'),0,[Payments].[AmountIncGST]-[AmountExGST]) AS RenewalGST, Policies.TotPremiumIncGST
FROM (Policies INNER JOIN (Advisors INNER JOIN Comissions ON Advisors.AdvisorID = Comissions.AdvisorID) ON Policies.PolicyNumber = Comissions.PolicyNumber) INNER JOIN Payments ON Policies.PolicyNumber = Payments.PolicyNumber
WHERE (((Payments.DateOfPayment) Between [Forms]![Advisor Summary Request]![txtFromDate] And [Forms]![Advisor Summary Request]![txtToDate]) AND ((Comissions.AdvisorID)=[Forms]![Advisor Summary Request]![cboAdvisor]));

 

[vbaInet]

Look into the DISTINCT clause.