Removing duplicate information in reports

The UK's most regular instructor-led training courses.
Training information: training for microsoft access · Access training · Microsoft Access Training
See also · microsoft access training · access vba courses · access database course

Resolved · Medium Priority · Version 2007

3 posts

ReplyMon 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?)

Version	Date	Location	Places available	Book	Next place rate (£)
Access Intermediate 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2003	Fri 12 Mar 2010	Bloomsbury	6		£199	£204
2007	Tue 23 Mar 2010	Bloomsbury	3		£224	£225
2003	Tue 30 Mar 2010	Bloomsbury	6		£199	£204
2003	Thu 22 Apr 2010	Southwark	5		£190	£195
2007	Tue 27 Apr 2010	Bloomsbury	5		£224	£225
2003	Fri 28 May 2010	Bloomsbury	10		£110	£115
Full Schedule: See all 21 Access Intermediate course dates. Bookings currently available until 17th December 2010.

642 posts

ReplyMon 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

3 posts

ReplyTue 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.

642 posts

ReplyThu 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

· Use Microsoft Access to manage your work/life balance
· Taking Control Of Your Data - And Saving Money In The Process!
· Why consider Access Visual Basic training?
· Some Things You May Not Know About Relational Databases
· How Reorganising Your Data Can Take Your Business to the Next Level

Please browse our web site to find out more about
training for microsoft access and other Microsoft training courses.

Access tip:

Hide Access Tables

One method of stopping your Access tables appearing in Database window, is to rename the table(s) with the prefix usys.

This converts the table into a system object, which cannot be viewed in the Database window.

You can show system objects in the Database window by the following:

Step 1: On the Tools menu, click Options.
Step 2: Click the View tab.
Under Show, select the System Objects check box.
Click OK.

View all Access hints and tips