Build a single report using multiple tables that have one field in common

I am trying to build a report that combines multiple tables and will give me the report below:

Report should be sorted by contractor doing the work, then by permit type, then by permit number

Company ABC (Contractor doing work)
   - Road Permits (open permits listed and sorted by permit #)
          - RD-0120,  road A1
          - RD-0121, road A2
  - Confined Space Permit (open permits listed and sorted by permit #)
           - CS-0120, location AB
           - CS-0121, location DF

Company AABB (Contractor doing work)
   - Road Permits (open permits listed and sorted by permit #)
          - RD-0115,  road B4
          - RD-0130, road C3
  - PMC Permit (open permits listed and sorted by permit #)
           - PMC-0105, location KS
           - PMC-0161, location LL

Company XYZ (contractor doing work)
   - Road Permits (open permits listed and sorted by permit #)
          - RD-0123,  road A4
          - RD-0125, road A3
  - PMC Permit (open permits listed and sorted by permit #)
           - PMC-0118, location DV
           - PMC-0119, location ED

The problem I have is this.  I have a table that lists the contractors.  Then I have multiple tables for each type of permit.  The permits types each have unique ID numbers that I use an auto counter to provide the next permit number.  If I try to create a query, cannot get it to work correctly.  See the attached file.

What I would like to see is the information broken out like above.  One contractor, and each line showing a different permit that is open.  There should not be info in the Road Permit Number field if there is information in the PMC Permit Number field.  (I also noted that all the permit number titles became "Permit Number Issued by PMC" not sure why that happened.)

Any help would be appreciated.



Start Free Trial