98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Removing duplicate information in reports | Access forum
Removing duplicate information in reports | Access forum
Resolved · Medium Priority · Version 2007
Mon 25 Jan 2010, 10:14Delegate 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:46Trainer 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:01Delegate 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:32Trainer 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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Insert The Current DateTo insert the current date into a Table field or Form Text box use: |
