7 Replies Latest reply on Jun 23, 2011 2:17 PM by philmodjunk

    Formatting a report to show parent records and two sets of related records from different tables

    GregW_1

      Title

      Formatting a report to show parent records and two sets of related records from different tables

      Post

      I have a database that is set up for users at a property management company to enter inspection records for the units at an apartment building.  The "Unit" table has two child tables - The "UnitInspRec" table, into which the user enters individual related inspection items, the main fields being Room, Item, and Notes; and the "UnitInspection" table, into which the user enters records from a "short form" that is basically a series of boolean checkboxes for common items like "Replace Carpet", "Replace Range", etc.  In pretty much all cases, each Unit will only have one related UnitInspection record, but I want to keep the structure as is (not incorporate the UnitInspection fields into the Unit table), in case there is a need to enter another UnitInspection record for the same Unit without overwriting the existing record.

      The data entry is solid with all of these tables.  I need a report that,
      1.  Lists all of the units at the property (sorted by Unit::UnitNumber)
      2.  For each Unit, list all of the related UnitInspRec records
      3.  For each Unit, display fields from the latest related UnitInspection record

      I've tried a couple different approaches and get some of the records I need in the different formast, but not all of the records I need in the same format:
      1.  Set the layout to the option "Show records from Unit", add the layout part "Sub-summary when sorted by Unit::UnitNumber", and place the "Unit::UnitNumber" field in that part, then put fields from the UnitInspRec table in the Body.  The problem here is that only the first related UnitInspRec is listed in the Body
      2.  Set the layout to the option "Show records from UnitInspRec" with the same part arrangement.  In this case all of the related UnitInspRec records display, but for any Units that have no related UnitInspRec records, the unit is not listed on the report at all.

      I need all Units to be listed, regardless of whether they have related UnitInspRec records, and for each Unit that has related UnitInspRec, I need all of those records to display.  I'm not so concerned with the UnitInspection record, because as I mentioned it only needs to display the fields from the one related record, so if I place those fields in the same part as the Unit, then they should display the intended data.

      Thanks for any insight you can provide.