possible circular reference causing duplicates???

[diwin]

I have a fairly straight-forward report that lists donors. The Main report lists the Company/Family name and the sum of donations Qty and Amt.

Then the only subform lists the Contacts for the Company/Family that have donated, again with sum of Qty and Sum of Amt.

Each pulls data from a similar query with just enough tables/queries and fields to populate the report.

For some strange reason, some of the records in the Subform display twice when there should only be one. Some others display more times (identical - See "Smith", below), upto about 10, but most are fine....

546 Smith Qty 1 Sum 60
- 650 M K Smith 1 60
- 650 M K Smith 1 60
- 650 M K Smith 1 60
- 650 M K Smith 1 60
322 Stewart Qty 1 Sum 60
- 405 Kevin Stewart 1 60
914 Swift Qty 2 Sum 60
- 1024 Mary Swift 1 30
- 1023 John Swift 1 30
etc.....

I cannot see anything suspect in the tables, queries for the reports, Master/Child Linking. I just don't get it. And when I close the database and re-open it, it is still there and always the same records with the same duplication.

Main Form Query

SELECT Count(*) AS [Count], Sum(qryGroupDonationsActualForReport.ActualAmount) AS SumOfActualAmount, tblDonorParents.parentID, tblDonorParents.parentName, "  - " & [tblCity].[cityName] & ", " & [tblCity].[cityProv] & ", " & [tblCity].[cityCountry] AS Address, Sum(qryGroupDonationsActualForReport.donationAmount) AS SumOfdonationAmount
FROM (tblCity INNER JOIN (tblPostalCode INNER JOIN (tblDonorParents INNER JOIN tblDonorContacts ON tblDonorParents.parentID = tblDonorContacts.contact_FparentID) ON tblPostalCode.pcID = tblDonorParents.parent_FpcID) ON tblCity.cityID = tblPostalCode.pcCity) INNER JOIN qryGroupDonationsActualForReport ON tblDonorContacts.contactID = qryGroupDonationsActualForReport.donation_FcontactID
GROUP BY tblDonorParents.parentID, tblDonorParents.parentName, "  - " & [tblCity].[cityName] & ", " & [tblCity].[cityProv] & ", " & [tblCity].[cityCountry]
ORDER BY Sum(qryGroupDonationsActualForReport.ActualAmount) DESC;

Subform Query

SELECT Count(*) AS [Count], Sum(qryGroupDonationsActualForReport.ActualAmount) AS SumOfActualAmount, tblDonorContacts.contact_FparentID, IIf(IsNull([tblDonorContacts].[contactNameFirst]),"",[tblDonorContacts].[contactNameFirst] & " ") & IIf(IsNull([tblDonorContacts].[contactNameMiddle]),"",[tblDonorContacts].[contactNameMiddle] & " ") & IIf(IsNull([tblDonorContacts].[contactNameLast]),"",[tblDonorContacts].[contactNameLast]) AS CName, qryGroupDonationsActualForReport.donation_FcontactID
FROM tblDonorContacts INNER JOIN qryGroupDonationsActualForReport ON tblDonorContacts.contactID = qryGroupDonationsActualForReport.donation_FcontactID
GROUP BY tblDonorContacts.contact_FparentID, IIf(IsNull([tblDonorContacts].[contactNameFirst]),"",[tblDonorContacts].[contactNameFirst] & " ") & IIf(IsNull([tblDonorContacts].[contactNameMiddle]),"",[tblDonorContacts].[contactNameMiddle] & " ") & IIf(IsNull([tblDonorContacts].[contactNameLast]),"",[tblDonorContacts].[contactNameLast]), qryGroupDonationsActualForReport.donation_FcontactID
ORDER BY Sum(qryGroupDonationsActualForReport.ActualAmount) DESC;

Daniel Dillon
o (<--- brain shown at actual size.)

 

[diwin]

OK. Are you ready for the goofy solution...which I don't understand? I just kept messing with it from every angle until I stumbled across this one.

I had the main form sorted by:
SumOfActualAmount DESC, with no header, and then by
ParentID ASC, With a header, which is shown in my example.

All I did was put one between them:
ParentName ASC, with no header.

Why did this fix it? I don't know. But I would love to hear an idea or two.

"Where would we be if we didn't try?"

Daniel Dillon
o (<--- brain shown at actual size.)