Solved

Need to hide duplicate lines on Access Report

Posted on 2012-03-22
22
1,216 Views
Last Modified: 2012-04-11
I have created a database of about 200 special "working groups".  Each group may have from one to 15 members.  The main table/query list all of the information about each group (name, meeting frequency, sponsors, location, etc.) and the group ID Numbers.  The second table/query lists all of the members with information about each staff member and their staff ID number.  A subform/subreport was also created to be used on a special report.

The report lists all groups in alphabetical order with the subform/subreport at the bottom of each record.  When looking at the report, the viewer will see all of the information about the group and a listing of all members of the group. The table/query is linked to the subform/subreport by Group ID Numbers and Staff ID Numbers.

The problem that I have is that when there are more than one member shown on the subform/subreport, the entire working group is repeated the exact number of times on the report.  Example;

SPECIAL REPORT SAMPLE
Group 1; Member List:   A
Group 2; Member List:   D, E
Group 2; Member List:   D, E
Group 3; Member List:   S
Group 4; Member List:   F, K, X
Group 4; Member List:   F, K, X
Group 4; Member List:   F, K, X
Group 5; Member List:   L
Group 6; Member List:   M

NOTE: When there are two or more members listed for any group, the report will repeat the group information and the Member List as many times as there are members.  The query that was used was automatically updated to repeat the information to match the number of members for that group.

What ever solutions is found, I hope that the adjustments are made automatically.  The members of each group and the listing of the various work group are constantly changing and the report should reflect the current information at any given time.

Thanks for any assistance you can give.

WS
0
Comment
Question by:sherman6789
  • 11
  • 9
  • 2
22 Comments
 
LVL 14

Accepted Solution

by:
Bill Ross earned 250 total points
Comment Utility
Hi,

Easy way:  Go to the report in design view.  Click on the duplicated field and select properties.  Set Hide Duplicates to Yes.

A little more difficult.  Set up group options on the report for each duplicated field.

Regards,

Bill
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<No Points wanted>

I like Bill's Grouping suggestion.
You can just use the wizard and create a new report and select the "Group" field as your Grouping Option
This way you can get summaries for the group that you can't get with Hide Duplicates
0
 

Author Comment

by:sherman6789
Comment Utility
Thanks Bill Denver,

Earlier today, I changed all of the fields to "Hide Duplicates" with the following results.

1.  I was not able to find a "Hide Duplicates" choice on the sub-report/subform.  Therefore it continued to show.  The other fields were blank but a big empty areas were left where the other information would have been.

2.  When a duplicate is continued on the next page, all of the information shows at the top record of the next page and any additional listings are blank.  It seems that "Hide duplicates" mean hide the duplicates on that page.  If it continues on the next page it puts the information on the first one and blanks all of the rest, for that item.  In most cases that would be OK since we normally would want to see the titles or field names on the next page.

3.  Even though the information is not being repeated on the same page.  The space is still being made for the blank information rather than ignoring the duplicate records  completely.  

4.  I temporarily removed the subform to see if that would make a difference.  It did not.
A big blank space is created for each "invisable" duplicate.

I will keep trying to figure this out.  Thanks for your help.

WS
0
 

Author Comment

by:sherman6789
Comment Utility
Thanks boag2000 and BillDenver.  I am studying the "group" method and trying to understand it.  I am goind to work on it tonight to see if I can figure out what you mean by selecting the "group" field.

If this works, will it automatically work each time that the report button is pressed?  Other staff members will be using this program after I complete it and I would not like for them to have to do more than press the "Print Report" or the "View Report" button.

Thanks again.

WS
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
This is why many times a sample database is a lot faster way to communicate what you have already.
Then just post an example of the exact output you require:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
0
 

Author Comment

by:sherman6789
Comment Utility
WHAT DO YOU THINK OF THIS?

I looked at the "Query" that was created and I see all of the duplicates.  Is there a way to tell the "Query" to hide or delete all duplicate records?

The only reason that I created a "Query" is so that I could connect the subform to it. I don't know how to connect a subform to the original "Table".

Maybe there is a way that I can use the original group "Table" rather than the "Query" which might eliminate the duplicates.  If that would work, then the report form would not have any duplicates to worry about.  The Query is in the corect order.  If I used the Table, I would somehow need to place it in the correct order also.

WS
0
 

Author Comment

by:sherman6789
Comment Utility
Thanks  boag2000.  I was writing my comments above when you made your last suggestion.  I will make a sample database with fake data in it tonight and send it tomorrow.
0
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

Either one of us can help with the issue.  Since you have a sub report you will also need to change the settings on it.  Try setting the Can Grow and Can Shrink properties on various sections of you sub report and see how that works.

As Jeff says, in addition to posting the database sample be sure to post a detailed example of the desired output.  That way we can give a specific answer to your question.
 
Regards,

Bill
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<I looked at the "Query" that was created and I see all of the duplicates.  Is there a way to tell the "Query" to hide or delete all duplicate records?>

Yes, you can create a "Group By" query...
...but that will lead us off onto another tangent....

So let's stick to the report.

;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Bill,

<Either one of us can help with the issue.>
Yep, so I'll step aside and let you handle this...

I'll still monitor though...

;-)

Jeff
0
 

Author Comment

by:sherman6789
Comment Utility
Hello Bill and Jeff,

Attached is a crude version of my database.  I have removed almost everything that I believe we don't need for this demonstration.  I have also changed personal names and other information that is not needed.
The database name is: EE-Version Working Groups2.mdb
Main form: frmOVC-Working Group Matrix
Report: rptOVC-WGMatrix-All*-QRY
Main Table and Query: tblOVC-Working Group Matrix; qryOVC-Working Group Matrix
Subgroup: subWG_RepInfo

You probably already know the following:  Open database by holding down the shift key while it opens: EE-Version Working Group2.mdb.  This will allow you to easily get to the Navigation objects.

I reduced the file to 15 records.  I selected samples that would have 1, 2 or 3 members so that you can see the duplications created in the "query" and on the "report".

If the "hide duplicates" is selected for each field, they will not show on the duplicate listings but a space will be left anyway.  I also do not know how to tell the subform to hide duplicates or how to hide itself.

I use MS Access version 2007 however, I save it as version 2000-2003 because some of the users have one or the other on their computers.  Everyone in the office can use the 2000-2003 version.  In the near future, all of us will have 2010 or the latest version.

Since both Bill and Jeff have worked from the beginning of this question, I plan to divide the points in half for both of the experts.

Thanks,
William Sherman
EE-Version-WorkingGroups2.mdb
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
Comment Utility
OK...

Because your controls are "Stacked", then you will get empty spaces, because the "Record is still there
And also note that if you are including the ID, ...which is NOT duplicated, hence it will always display as well.

And unfortunately there is no "Hide Duplicate" property of a subreport.

For me at least, there is too much going on there that I do not understand...
...and that is just in the Main Report's source Query...
The subreport and it's source are another story.
(I tend to avoid subreports like the plague, unless there is no other way...)

So let's go back to BillDenver's "Grouped Report" suggestion.

With a typical "Grouped report (made via the Report wizard, you select the fields you want from both the Parent (Table or query) and the child (table/query).
Then select the way you want to "View" your data.
By the Parent fields typically.
This will group all the Parent data together once for each parent record, then show the child records with no duplicated data.

Attached, please find a basic example of such a report.
I am sure with some experimentation, you can get something like this going for your needs here.

JeffCoachman
Database106.mdb
0
 

Author Comment

by:sherman6789
Comment Utility
Thanks Jeff for your suggestions.  I will study the method of making a group report which takes information from two or more tables and creates a report without duplicates.  Since both you and Bill have mentioned this method on several occasions, it must be the way to go.

While you were writing your response to my DB sample, I figured that I should let you know why I created the sub-form in the first place.  I wrote the information below before I received your suggestions and database.  Your solution may prevent problems in the future, as well.

When I first set up the database, the rule was that there would be a maximum of three (3) members in any work group.  That was no problem because I created three fields: Member-1, Member-2, and Member-3.  After a short while, management decided that each group could have anywhere from 1 - 15 members.  The members would vote on a "Lead person" and a "Backup".  I thought that it would not make sense for me to make fifteen fields that would be blank for most of the records.  I then got the idea of creating a sub-form where we could add as many names and titles as necessary.  It would be set to "can grow" and the printout would get as long as necessary.  I had no problem with the sub-form when people were using the main form on the screen.  When viewing the main form on the screen it would be easy to add members.  My problem came when I wanted to have a printout which included the members of each group.

There are 14 different reports being used daily by management and many more fields that were removed from the sample that I sent in.  Each member enters the information into the main forms using blanks or pull-downs.  I left only a few of the blanks and pulldowns on the sample that i submitted.

This weekend, I am going to take Bill's and Jeff's advice and study the method of "grouping".  I will also study the sample that you attached.  I will need to figure out how the "input form" will look since that input must be simple, easy to use and used by many people to populate the tables.

I see that you have a customer table where customer names and ID numbers are listed and used on the report.  I am guessing that another table needs to be made with all employee names and ID numbers are listed.

I must keep in mind that I need tables, reports and "Input Forms".  The input form is also very important because it will be used daily by employees and managers for confirmation and other information about the working groups and knowing which employees are members.  A given employee may be a lead in five groups, a backup in several other groups and just a members in several other groups.  Therefore, each group must show the basic information plus each employee name and position if they have one.  All of that must also showup on one or more reports.

I hope that this makes sense.  I will have to think on this, sleep on it and try to get a good result this weekend, if possible. The end report is just one of the things that I must make sure works.  I will send a report next week on what I have learned and I will try to cut down on the number of words that I write. I do realize that I am "too wordy". (Smile)

Thanks for your assistance.

WRS
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
To be clear, there is nothing wrong with using a sub report, in fact in some cases this is the better option.
For example you can "control" a subreport in the same way you can a regular report (VBA Automation, Properties, ...etc)

the other thing here is that it is not clear what your skill level is with "Normalization" and/or table relationships is.

Meaning,  that if the data in the tables is normalized, and the tables are related properly, creating "any" report should be fairly straightforward.

Keep us posted

;-)

Jeff
0
 

Author Comment

by:sherman6789
Comment Utility
Here is some good news for Jeff and Bill.  Because of you two, I've spent a good amount of time this weekend studying the information, samples and other information concerning the "Grouping" method of making reports.  I think that I understand most of it.  I took the report that I had created and changed the Property Sheet.  I went to the Record Source box and replaced the information with the SELECT statement that is attached.  I used the sameple that Jeff attached and modified the statement based on the results you received.

I made several modifications to the statement; because, I received some of the following error messges:  "Syntax error in FROM clause", "Invalid use of ',' in Query Expression", "JOIN Expression not supported". "Misspelled name or missing prompt".  All of these messages indicate that I did something wrong.  However, I may be getting close to doing something correct.  I used table instead of query files.

Please look at the attachments.  You may see something that I missed.

I am trying to keep my messages shorter so they will not be so boring.

Thanks again.

WRS
SELECT-statement-samples.docx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Again a sample database would be *Much* easier to work with.

Just looking at SQL with no data is not really the best way to troubleshoot an issue like this...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
In any event, ...here is the original sample I posted.
Note the new query ("qryCustomerOrders") and the SQL on which it is built.

Then note the "Grouped" report I made from this query.
It is basically identical to the report I made form the two individual tables...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
newer sample:
Database106.mdb
0
 

Author Comment

by:sherman6789
Comment Utility
Hello everyone,

I had an urgent project to work on, out of town.  I will return to the office by Wednesday 4-4-12 and will followup or end the question link.

Thank you.

WRS
0
 

Author Comment

by:sherman6789
Comment Utility
Still working on project.  Will come back soon.  Thanks.  -WS
0
 

Author Comment

by:sherman6789
Comment Utility
The project works very well and my supervisors are super pleased.  I want to thank Jeff (boag2000) and Bill (BillDenver) for their sincere support, masterful suggestions and examples.  I have been working on other emergency projects and this project is running smoothly.  I need to close this link and award the points.  As I promised, I will divide the points equally between Bill and Jeff.

Thanks again.

sherman6789
0
 

Author Closing Comment

by:sherman6789
Comment Utility
Thanks to Experts-Exchange for providing users like me with the ability to question experts and present our problems to the world.  boag2000 and BillDenver were two great helps.  I also like the fact that they worked together to give me pointers and suggestions.  Jeff also sent examples and sample databases to assist.  Once again, thank you.<br />WRS<br />sherman6789
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now