Report with Notes (Almost like footnotes)

[Lord_Vader]

I have a basic table:

Vendor, Error Date, Error Title, Error Notes.

I would like a report that:

Groups on Vendor, Is filtered by date.

The error titles for that vendor would appear on one page. Then the error notes would appear on a subsequent page for readability. (Notes are long memo fields).

The trick is that the error titles must have the note number next to them "(See Note 2)"

Then Note 2 has to show up as note 2 on another page.

I've managed to do it with separate reports . It's tricky, because being an access database, it doesn't care about record order, so I had to write a query to give order, and then make it match the notes query.

Any ideas? on how to make this simpler? I'm using subqueries with "COUNT" to get the numbered records. I tried a subreport but then access gets angry about aggregate queries within aggregate queries.

 

[vbaInet]

Numbering your records on a report is not a problem, it can easily be done without a query.

I'm just trying to understand your requirement. Let's take an example Vendor 1, the error titles will appear will appear on page 1 (if it fits), followed by Notes 1 on a new page (hopefully page 2) and finally a third page for Notes 2?

Can you give us a visual on a word document or something? Some sample records would be useful too.

 

[Lord_Vader]

I've attached a word document of what the report would look like... kind of.

Each location can have multiple sites. The errors can affect multiple sites. The prior/current comes from when the error was found. Notice how the notes appear for both sites, but for one location on the last page.

-----

vbaInet, how can you number records on the report w/o a query? It seemed like it should be easy, but all the stuff I found when searching was for the crazy subquery/count method

 

[vbaInet]

So:

1. the errors will be a subreport on the group's Footer section.
2. number each error note (i.e. each record in the group's footer section) - as explained further down
3. refer to that erro note number in the detail section by doing this:

="(See Note " & Report.[[COLOR=Red]Subrpeort Name[/COLOR]]![[COLOR=Red]Error Note number[/COLOR]] & ")"

To number records in the Detail section, place a textbox in the detail section and set the properties below as follows:

Control Source: =1
Running Sum: Over Group