ms-accessreporting

in report, repeat only certain columns after first row


I'm tasked with replicating in Access a particular report running in a Filemaker database. The output should look like this:

NAME       DATE       ITEM(S)
Joe      5/2/2012     Gum
                      Tape
                      Soap
Ann      5/1/2012     Paper
Bob      5/1/2012     Pen
                      Paper

where the records look like this (it's a query joining the "person" and "items" tables, which have a 1:many relationship):

NAME       DATE       ITEM(S)
Joe      5/2/2012     Gum
Joe      5/2/2012     Tape
Joe      5/2/2012     Soap
Ann      5/1/2012     Paper
Bob      5/1/2012     Pen
Bob      5/1/2012     Paper

In the report, I need the first ITEM always to show up in the first, complete row; if there are more items, only the items should be listed in subsequent rows (the NAME and DATE values should not be repeated).

I'm not quite sure which tree to bark up:

The only thing I've managed so far is to group on the person, with the NAME and DATE in the Group Header, and then put all of the items in the detail, but that's not exactly what I have been asked to do.

Thanks in anticipation of "a-ha!".

Chris


Solution

    1. It's the same in the reports, you can't apply conditional formatting the way you're able to in RDL.
    2. This seems like the natural answer. It's essentially a grouping operation, so group by name and date, and put those two in the header, then put the items in the detail. The only thing is that, as I think you noted, the items would be offset by one row relative to what you're being asked.
    3. You can put conditional logic in the query of course, but I don't think that works here, because the show/hide logic depends on the group query. See #2.