Removing duplicate information in reports | Access forum

Resolved · Medium Priority · Version 2007

Mon 25 Jan 2010, 10:14 Delegate Nicola said...

Nicola has attended:
Access Introduction course

Removing duplicate information in reports

Whenever I run a report collating information from various tables within my database, it repeats the information from each table at least twice (16 times in one case!). If I select "Hide Duplicates", it doesn't differentiate sufficiently - it gets rid of ALL duplicates in a certain field, even if it shouldn't, for example, if different expenses occurred on the same date, the report will show the date for only one of the expenses. How do I only get rid of the true duplicate records (or how can I run the report correctly so that it doesn't duplicate everything in the first place?)

Mon 25 Jan 2010, 19:46 Trainer Simon said...

RE: Removing duplicate information in reports

Hi Nicola,

Thank you for your question.

I would advise you first to take a copy of the database before trying the following to ensure you achieve the desired result first.

Are the tables related together?

Can the duplicated records be deleted from the relevant table?

There is a query you could run on the copy of the database:

1. Go to the Queries object and click the New button. Choose Find Duplicates Query Wizard and select the table or tables that contain duplicate records. This query will then identify any duplicates which could be then copied into another table or deleted. Make sure you add a unique field into the query to uniquely identify each duplicate.

I hope this helps.

Regards

Simon

Tue 26 Jan 2010, 14:01 Delegate Nicola said...

RE: Removing duplicate information in reports

The tables are related together, but the duplicated records do not appear as duplicates in the original tables (apart from having particular fields in common), just in the report.

To be specific, it is a property management database and I have separate tables for (among other things) expenses, rental payments and management fees. I am trying to run a report that will show monthly rent minus expenses and management fees for each property.

I pull the information together for a particular property belonging to a particular landlord so obviously these two fields are shared by ALL the relevant records, but I "step" them so they only show once on the report. The problem is with entries showing what was paid and on what date.

For example, there should be 1 rent payment, 2 fee payments and 8 expenses showing for a particular landlord's property. Somehow I end up with 16 of everything in the report - 2 copies of each expense, 8 copies of each fee payment, and 16 copies of the rent payment.

I don't know why this is happening, so I don't know how to fix it.

Thu 28 Jan 2010, 09:32 Trainer Simon said...

RE: Removing duplicate information in reports

Hi Nicola,

Thank you for your response.

Can you tell me whether you have the Landlord field in every table or is there a separate table for the landlords details.

Perhaps you could try and create a query that collates all the information and then base the report on the query.

It is difficult to identify the problem without seeing the database. Unfortunately this would be beyond the scope of the forum as the amount of time spent looking at the database would exceed the allotted time allowed for each question.

Regards

Simon