Need to hide duplicate lines on Access Report
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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.
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.
ASKER
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
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
ASKER
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.
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
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
<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
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
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
<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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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
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
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...
Just looking at SQL with no data is not really the best way to troubleshoot an issue like this...
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...
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...
newer sample:
Database106.mdb
Database106.mdb
ASKER
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
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
ASKER
Still working on project. Will come back soon. Thanks. -WS
ASKER
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
Thanks again.
sherman6789
ASKER
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
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