Query will not show distinct lines

[zbaker]

Doesnt anyone have an idea why my query shows distinct on some records but not on others.



SELECT DISTINCT tblMASTERDeviceTable.AccrualRefID, tblMASTERSiteListTable.CC, tblMASTERSiteListTable.CCDescr, tblMASTERSiteListTable.Region, tblMASTERSiteListTable.CompanyCode, tblMASTERSiteListTable.CompanyName, tblMASTERContractTable.TVCNo, tblMASTERContractTable.ServiceAgreementVendorName, tblMASTERDeviceTable.EquipID, tblMASTERDeviceTable.CEIDNumber, tblMASTERDeviceTable.SerialNumber, tblMASTERDeviceTable.InvoiceRefNo, tblMASTERDeviceTable.Modality, tblMASTERDeviceTable.DeviceManufacturer, tblMASTERDeviceTable.DeviceModel, tblMASTERDeviceTable.DeviceDescription, tblMASTERDeviceTable.[Device Annual Contract Amount], tblMASTERDeviceTable.[Device Contract Start Date], tblMASTERDeviceTable.[Device Contract End Date], tblMASTERDeviceTable.DeviceDeactivated, tblMASTERDeviceTable.DeviceDeactivationDate, tblMASTERDeviceTable.DeviceDeactivationReason, tblMASTERDeviceTable.DateDataChanged, tblMASTERDeviceTable.Notes, IIf([Device Contract Start Date]<=Now() And [Device Contract End Date]>=Now() And [DeviceDeactivated]=False,"Active",IIf([Device Contract Start Date]>Now() Or [Device Contract End Date]<Now() Or [DeviceDeactivated]=True,"Inactive","Error")) AS [Contract Status], [Accrual Subquery].[Outstanding Balance]
FROM tblMASTERSiteListTable INNER JOIN (tblMASTERContractTable INNER JOIN (tblMASTERDeviceTable INNER JOIN [Accrual Subquery] ON tblMASTERDeviceTable.AccrualRefID = [Accrual Subquery].AccrualRefID) ON tblMASTERContractTable.TVCNo = tblMASTERDeviceTable.TVCNo) ON tblMASTERSiteListTable.CC = tblMASTERDeviceTable.CostCenter
WHERE (((IIf([Device Contract Start Date]<=Now() And [Device Contract End Date]>=Now() And [DeviceDeactivated]=False,"Active",IIf([Device Contract Start Date]>Now() Or [Device Contract End Date]<Now() Or [DeviceDeactivated]=True,"Inactive","Error")))="Active")) OR ((([Accrual Subquery].[Outstanding Balance])<>0))
ORDER BY tblMASTERDeviceTable.AccrualRefID;

[John_G]

What do you mean by "...distinct on some records but not on others..." - do you mean some look to be duplicates?

You are selecting a lot of fields to be using the "Distinct" qualifier.

Can you give more detail on the problem you are having.

[orange]

???? You could try DISTINCTROW instead of DISTINCT, but I'm really not sure what you are asking.