how to avoid duplicate column values in access reports?

[karoly]

I have a report which has say two columns - staff number and visit date.
For a staff number (which is unique) there may be more than one visit date. But I want to list a staff number only once and not multiple times in the column (but want all the visit dates of course). How can I do that?

[pkstormy]

Setup grouping (ie. sorting/grouping popup) within the main report so it groups on staff number (putting staff number in the grouping section) and then the visit dates in the detail section.

Or have your main report based uniquely on the staff number and have a sub-report within that report based on the visit dates (linking to the main report by staff number).

[RuralGuy]

I would think simply setting the HideDuplicates property of the control to YES would achieve the results desired. It is on the Format tab of the property sheet of each control.

[karoly]

Can you answer one more question?

I want to count the number of records in each group (that is number of records with a duplicate field ) and display it in a third field. How can I do that?

[RuralGuy]

You can put an invisible TextBox in the detail section with the ControlSource set to =1 and Sum Over the Group.

[SteveH2508]

Can you answer one more question?

I want to count the number of records in each group (that is number of records with a duplicate field ) and display it in a third field. How can I do that?

Put a textbox in the group footer with the controlsource =Count(*)

[karoly]

You can put an invisible TextBox in the detail section with the ControlSource set to =1 and Sum Over the Group.

Thanks Rural Guy

But how to sum over the group? Do you mean to make a group footer?(which will but cause the count to come in a separate row which i dont prefer). Even For that what is the need for invisible tex tbox, as count(*) will do right? Without group footer how to specify a group?

[RuralGuy]

Sum is one of the properties on the Data tab of the control.

[karoly]

Sum is one of the properties on the Data tab of the control.

Is there a way I can avoid duplicate values in a field across groups (not in a single group, which I can do by using the property Hide duplicates)?

[RuralGuy]

You lost me there. If you have grouped on a field then *all* of those values will be together, not scattered in several groups.

[tarhim47]

I would think simply setting the HideDuplicates property of the control to YES would achieve the results desired. It is on the Format tab of the property sheet of each control.

I tried this and it works great. It hides all the duplicate values. However it leaves a blank space where the duplicate would otherwise be placed. For example, if I have for "John"s in my table, the report shows only one John however leaves three blank spaces bfore showing "Ken."

How can I fix this?

Thanks for yuor help!

[RuralGuy]

How about showing us what you would like the report to look like with some sample dummy data typed in. The "blank" spaces are probably additional records.

[apr pillai]

When 'Hide Duplicate' Property value is set to Yes the text box itself will disappear, till the field value changes. If you need it replaced with an empty rectangle then you should draw a rectangle of the same size and place it behind the textbox. This will fill the empty space with a box of the same size of text box and the report will look better. I used to do this trick.

Or

I used to draw a vertical line in the detail Section and align it at the left border of the text box, if it is the left-most field, and this will draw a left vertical border rather than leaving it open at the left side.

[tarhim47]

Hi RuralGuy,

I apologize for not replying earlier but I have since figured out the problem.

Thanks for all your help!

[RuralGuy]

Thanks for posting back.