removing duplicate information r
return to quoteReturn to your Quote
(or Hide this message)
Microsoft Office TrainingThe UK's Number 1 for Microsoft Office Training add this page to your favourites/bookmarksBookmark page

view a printable version of this pagePrintable version
Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » Removing duplicate information in reports

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

resolvedResolved · Medium Priority · Version 2007

No ranking yet
3 posts
replyReplyMon 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?)

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

Platinum
642 posts
replyReplyMon 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

No ranking yet
3 posts
replyReplyTue 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.

Platinum
642 posts
replyReplyThu 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


Related articles

· 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


Rate this page:
2.1/5 (107 votes cast)
Institute of IT Training - Accredited Training Provider ILM
Microsoft Certified Partner
Microsoft Office Specialist Authorised Testing Centre (MOS and MCAS)

Prodigy Platinum Learning Partner

Institute of IT Training - Accredited Training Provider
McAfee Secure sites help keep you safe from identity theft, credit card fraud, spyware, spam, viruses and online scams
Association of Computer Trainers verified by visa, mastercard securecard