Prev: Pie Chart Formating
Next: Detect when detail section pushes to 2nd page
From: David K on 17 Apr 2010 22:45
I have a special kind of “eliminate blank lines” question. Here's a
simplified version of the issue.

I have a query with multiple joins which returns a key value (Name), one or
more (there will always be at least one) “children” values and then another
column pulling different children into the join. The query works fine but my
problem comes with trying to clean up the output. Consider that the
following is coming back from the query.

Name Repeat1 Repeat 2
1 R1 RA
1 R1 RB
1 R1 RC
1 R2 RA
1 R2 RB
1 R2 RC
2 R2 RD
2 R2 RE
2 R3 RD
2 R3 RE

Within the report designer, if I group on Name and set Hide Dups to Yes on
property sheet for each columns I can get this:

Name Repeat1 Repeat 2
1 R1 RA
RB
RC
R2 RA
RB
RC

2 R2 RD
RE
R3 RD
RE

But what I really would like is (the line between the groups is a nice to
have but I can live without it…can't find a way to edit the group footer
unless I add totals)

Name Repeat1 Repeat 2
1 R1 RA
R2 RB
RC
2 R2 RD
R3 RE

I can think of some ways to do this with a lot of custom programming but I'm
wondering if there's a way to do this directly in the reporting tool.
From: Duane Hookom on 18 Apr 2010 14:54
I would use a main report based on just the [Name] field (IMO, change this
to something that won't bite you later). Then create two subreports based on
totals queries that group by the new Name field and Repeat1 then Repeat2.


--
Duane Hookom
MS Access MVP


"David K" <DavidK(a)discussions.microsoft.com> wrote in message
news:8DADA0E6-1B8A-4C28-934D-5EDD5157832A(a)microsoft.com...
> I have a special kind of “eliminate blank lines” question. Here's a
> simplified version of the issue.
>
> I have a query with multiple joins which returns a key value (Name), one
> or
> more (there will always be at least one) “children” values and then
> another
> column pulling different children into the join. The query works fine but
> my
> problem comes with trying to clean up the output. Consider that the
> following is coming back from the query.
>
> Name Repeat1 Repeat 2
> 1 R1 RA
> 1 R1 RB
> 1 R1 RC
> 1 R2 RA
> 1 R2 RB
> 1 R2 RC
> 2 R2 RD
> 2 R2 RE
> 2 R3 RD
> 2 R3 RE
>
> Within the report designer, if I group on Name and set Hide Dups to Yes on
> property sheet for each columns I can get this:
>
> Name Repeat1 Repeat 2
> 1 R1 RA
> RB
> RC
> R2 RA
> RB
> RC
>
> 2 R2 RD
> RE
> R3 RD
> RE
>
> But what I really would like is (the line between the groups is a nice to
> have but I can live without it…can't find a way to edit the group footer
> unless I add totals)
>
> Name Repeat1 Repeat 2
> 1 R1 RA
> R2 RB
> RC
> 2 R2 RD
> R3 RE
>
> I can think of some ways to do this with a lot of custom programming but I'm
> wondering if there's a way to do this directly in the reporting tool.

 | 
Pages: 1
Prev: Pie Chart Formating
Next: Detect when detail section pushes to 2nd page