Duplicate results for query

[MSandell13]

I am running a report in access for my team and have created the query based on live data that we have feeding into access. I have created and run the report but I am seeing duplicate information and I am wondering how to get this down. I also am hoping to after this determine that the report is pulling all necessary data. The data is creating duplicate records for customer names and customer ID's, but I only want one. (this may be pulling duplicates because of other information I am pulling but do not know how to determine this). It's a big report/query and I have pasted the SQL below.





SELECT dbo_customer.customer_name, dbo_customer.customer_token, [Proposal Status Codes].description1, dbo_underwriter.last_name, dbo_salesperson.lastname, dbo_proposal.effec_date, dbo_customer.state, dbo_producer_company.producer_company_name, dbo_producer_company.state, dbo_producer.firstname, dbo_producer.lastname, dbo_experience_contacts_view.contact_type, dbo_experience_contacts_view.contact_name, Sum(dbo_worksheet.enroll_total) AS SumOfenroll_total, dbo_mgu_representative.first_name, dbo_mgu_representative.last_name, dbo_proposal.renewal INTO Renewals
FROM (((((((dbo_mgu_representative INNER JOIN ((dbo_producer INNER JOIN dbo_producer_company ON dbo_producer.producer_company_token = dbo_producer_company.producer_company_token) INNER JOIN (dbo_customer INNER JOIN dbo_proposal ON dbo_customer.customer_token = dbo_proposal.customer_token) ON dbo_producer.producer_token = dbo_proposal.producer_token) ON dbo_mgu_representative.mgu_rep_token = dbo_proposal.mgu_rep_token) INNER JOIN dbo_worksheet ON dbo_proposal.proposal_token = dbo_worksheet.proposal_token) INNER JOIN dbo_underwriter ON dbo_proposal.under_token = dbo_underwriter.under_token) INNER JOIN dbo_salesperson ON dbo_proposal.sales_rep_token = dbo_salesperson.salesperson_token) INNER JOIN [Proposal Status Codes] ON dbo_proposal.status = [Proposal Status Codes].value) INNER JOIN dbo_experience_period ON dbo_customer.customer_token = dbo_experience_period.customer_token) INNER JOIN dbo_experience_contacts_view ON dbo_experience_period.exp_period_token = dbo_experience_contacts_view.exp_period_token) INNER JOIN dbo_producer_parent ON dbo_producer_company.parent_company_token = dbo_producer_parent.parent_company_token
GROUP BY dbo_customer.customer_name, dbo_customer.customer_token, [Proposal Status Codes].description1, dbo_underwriter.last_name, dbo_salesperson.lastname, dbo_proposal.effec_date, dbo_customer.state, dbo_producer_company.producer_company_name, dbo_producer_company.state, dbo_producer.firstname, dbo_producer.lastname, dbo_experience_contacts_view.contact_type, dbo_experience_contacts_view.contact_name, dbo_producer_parent.Parent_company_name, dbo_mgu_representative.first_name, dbo_mgu_representative.last_name, dbo_proposal.renewal
HAVING (((dbo_proposal.effec_date)=#1/1/2020#) AND ((dbo_experience_contacts_view.contact_type)="01") AND ((dbo_proposal.renewal)="Y"));

[June7]

You are joining multiple tables - should expect 'parent' data to repeat for every 'child' record. This should not be an issue when only 2 tables are involved. However, you have many more tables. If there are multiple many-to-many related tables, use subreports instead of everything in one query.

[MSandell13]

Thanks! Do you happen to have any references or documents on how to use subreports so I can break this up appropriately?

[June7]

Subreports are built same way as subforms. Use Master/Child Links properties to synchronize related records. Search web, I am sure will find tutorials.

[Bulzie]

Maybe also, to get rid of duplicate records in that query above, you could maybe create new query with the source the first query you pasted above. Then turn on Totals on this 2nd query and have it use Groupby on all the fields. Base your report on this 2nd query. If you are talking about duplicate data in fields on multiple records, on the reports you can put those in group header field areas so they only show once on the report.

Maybe give an few records example of the data from that query above.