How to create a report based upon 3 tables with correct grouping? RRS feed

  • Question

  • I’m trying to create a report on archaeological sites which contains data from three different tables:  [Sites], [Maps], and [Photos].  Each Site can have multiple Photos and multiple Maps.  I’ve created the appropriate relationships and everything's working great.  I've successfully created reports for Sites with Maps, as well as for Sites with Photos, but can't figure out Sites with Photos AND Maps....

    I’d like the report to print one page with the parent record’s information, followed by as many pages needed for the maps, and then as many pages needed for the photos, before moving to the next record and printing in the same pattern.  I need the [Sites]Site_Name field at the top of each page, along with a title like "Site Listing With Photos and Maps."

    I've worked with grouping and different sections, but have ended up with some funny results, lol.

    Sorry about the ASCII art, but I kept getting an error trying to post my carefully-prepared graphic of the layout.  :(

    Design View:


    .   info from [Sites]        .

    .     (text, etc.)          .
    .     ----------------      .

    .            Maps           .

    .    -----------------      .

    .          Photos           .

    So if a Site had 1 map and 3 photos, the report would be 5 pages long:

         Page 1:  information from the Sites table, mostly text.

         Page 2:  one map, with a couple associated fields like description, etc.

         Pages 3-5:  each page would have one photo, together with its description (text) field.

    Thanks in advance for any help you can give me.  I've spent some time googling/youtube this, but have failed to find any ideas.  (On a related note, does anyone know why I can't post pics to this forum?)

    Regards,  RC

    Friday, June 16, 2017 4:56 AM

All replies