Can you omit duplicates within a group??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following records:

Rec1 Meeting1 Cook Sales
Rec2 Meeting1 Smith Sales
Rec3 Meeting1 Cook Production
Rec4 Meeting1 Smith Production

I want to print a report that is grouped by Col 1 (Meeting). Is there a way
to produce a report like:

Meeting1
Cook Sales
Smith Production

The 2nd and 3rd report columns are independent of one another, i.e., I don't
care if Production is next to Cook or next to Smith. Aside from a lengthy
explanation of why I'm doing this, is there a way to suppress (in a report)
the duplicate lines that normally get generated:

Meeting1
Cook Sales
Production
Smith Sales
Production

I'm wondering if there is some type of Iif statement that looks for repeats
within a group and deposts a NULL value...

Thank you.
 
Use the Sorting and Grouping option (View|Sorting and Grouping or use the
menu button) of the report in design view. Group on the field that has
Meeting1. Tell it that you want a Group Header and place a textbox in that
header to print Meeting1. Set the sorting for the other fields as desired in
the same Grouping and Sorting dialog box. When you set any grouping or
sorting in this dialog, the sorting in the report's record source query is
ignored, so you have to set all of the sorting in this dialog.

Another item you may find useful is that textboxes in a report have a
property to not display duplicates (Format tab|Hide Duplicates). This will
leave a blank space where the duplicated data would be, it just doesn't
print it.
 
Wayne -
I may be missing your point, but even if I group on Meeting, then on "Cook"
(for example), Access is still forced to print:

Meeting1
Cook Sales
Production
Smith Sales
Production

because as you see on the actual table records, there is a record for
Meeting1 with Cook and with Sales, as well as a record for Meeting1 with Cook
and with Production, and likewise with Smith. Even if I group on "Sales", the
same will happen with Smith and Cook.

Again, I'm trying to get to:

Meeting1
Cook Sales
Smith Production

George
 
Yes, a report will do that if you do the grouping in the report itself.
Meeting1 would be printed in the group header and it would only print one
time for Meeting1. Meeting2 would then trigger the header again and would
print one time. You could create a second group header for Cook/Smith. This
would be like a "nested loop" inside the "meeting loop". Sales and
Production would be printed in the Detail section of the report.
 
I'll take one more pass - don't want to drive you nuts.

I actually have been doing exactly what you describe: Main group/loop:
Meeting, another group header for Cook/Smith, then Sales/Production appear in
the detail. The result, as I would expect:

Meeting1
Cook
Sales
Production
Smith
Sales
Production
Suppressing printing of duplicates doesn't work, as these are not duplicates
(Sales & Production only repeat for a new group, i.e., Smith).

George
 
Ok, I'm not following you I guess. Yes, the results you say you would expect
are also what I would expect from the grouping in the report. What are you
getting?
 
This:

Meeting1
is what I am getting. What I want to get is:
Meeting1
Cook Sales
Smith Production
Click to expand...

Going back to the original data:

Rec1 Meeting1 Cook Sales
Rec2 Meeting1 Smith Sales
Rec3 Meeting1 Cook Production
Rec4 Meeting1 Smith Production

I'm not sure if there's a way (via report "modifiers") to avoid what I'm
getting. (i.e., stripping out one of the occurrances of Sales, and one of the
occurrances of Production within the report.
 
No, not without programming to override the normal behavior of a report. You
would have to program it to skip printing records until you got the next
record condition that you wanted. There is a Cancel parameter for the Format
event of the Detail section. You may be able to use Static variables to keep
track of the last values and Cancel the format if it's not what you want.
 
Back
Top