Duplicates and formating 1

[UHNSTrust]

I want to hide duplicates so that my date will look like this

John Test1 10
20
Test2 5
Fred Test1 15
Test2 10
20

This I have done. My problem is with formating it. If I just hide duplicates it still keeps the border of the text box of the one showing. I can remove the borders of this box but then there is no lines between any of the rows. Much like the example above.

Using the example data above (John) - What I want is to have a box around 'John' that is 3 lines deep and a box arount Test1 that is 2 lines deep and a box around Test2. The numbers would all have boxes around them. Fred would have a box 3 lines deep with Test1 having a box around it. Test2 would have a box 2 lines deep and the numbers each having a box around them.

Hope this makes sense.

Thanks in advance
Jonathan

 

[dhookom]

This could probably be done using the Line method in the On Format or On Print event of the detail section of your report. It wouldn't be easy so you need to have a very good reason to display your report like that.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[UHNSTrust]

Hi Duane,
Thanks for your reply. I have used the Line method previously but am struggling to work out when the last record of a particular group appears to add the line.

I have been asked to show the report grouped as it would appear in an Excel pivot table.

If you have any ideas it would be appreciated.

Jonathan

 

[dhookom]

I would group by each field that I want to "box". Add a group header section. Add a text box to the group header that counts the number of records in the group. Add running sum text boxes in the detail section to number each of the groups. Set the Control Source of each running sum text box to =1 and Running Sum to Over Group. Use code in the On Print event of the detail to compare the value of the count in the group header with the running sum. When the group count and running sum are the same, draw your line.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[UHNSTrust]

Duane,

Thanks for this. Works great. A star for you.

Jonathan