I'm creating a database to inventory lodging structures and bedrooms. Included in this database are the inspection dates for the structures and bedrooms.
Some structures are single cabins with one or more bedrooms. These building types will have the same inspection date for the entire structure regardless of the number of bedrooms.
Some structures are lodges, hotels, motels, etc. These structures will have inspection dates for the individual rooms, not the entire lodge. Not all rooms are inspected during the visit.
Additionally, some establishments will have a lodge and multiple cabins with various bedrooms for inspection.
How can I relate these tables so that a report is generated to show:
Cabin Name and the inspection date; and
One line labeled lodge, with all the rooms following and their respective inspection dates.
Additionally, I need a structure count and bedroom count at the end of the report.
These counts will rarely be same.
A report similar to what I need is attached as a PDF.
I used a query to generate the structure count because running a total count would count the same cabin multiple times if there is more than one bedroom in a given cabin.blank_lodging_report.pdf
I appreciate any help.
blank_lodging_report.pdf