Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2017
    Posts
    2

    Table Relationships for Lodging / Cabin / Hotel Bedrooms

    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.

    Click image for larger version. 

Name:	database_02.jpg 
Views:	16 
Size:	79.6 KB 
ID:	28027

    blank_lodging_report.pdf

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Then I would alter the relation, put tCabinInspection as a child of tCabinRoom. (no need to change the table name)
    THEN, put a flag on the tCabinName table: CabinInspect (y/n)
    1. all cabins with CabinInspect =false , must be inspected room by room, a form shows each child room record in the cabin to inspect

    2. but the CabinInspect =true, a form with a single inspection date, the 'save' button would run an append query to add all room records at once to the tCabinInspection all set to the form date.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sounds like you have three entities

    primary address, cabins, rooms

    where rooms can either be in the primary address or a cabin

    so rather than your present structure of primary address>cabins>rooms

    I would have Primary address>rooms>cabins

    where the cabin fk in rooms indicates its location and would be null if the room is in the primary residence, or you can add the primary residence as being a 'large cabin'

    I agree with ranman regarding the linking of cabininspect to cabinroom

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    or tCabinInspection DOESNT need to be a child table of any of them.
    instead its a log.
    this way, you dont need to record every room inspection for full cabin inspection
    but can add individual rooms also.

    cabin, room, InspectDate
    03, 01, 1/1/17
    03, 02, 1/1/17
    01, all, 1/1/17

  5. #5
    Join Date
    Mar 2017
    Posts
    2
    Hi Ranman256:

    I will try your suggestions.

    After made I my initial post, it occurred to me that the post from hhound004 is quite similar to my issue - I believe. I did read the paper Entity Relationship Diagramming as suggested in the post: https://www.accessforums.net/showthread.php?t=65188

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding instance of 2 bedrooms per location
    By briang in forum Access
    Replies: 3
    Last Post: 09-23-2016, 05:31 PM
  2. Replies: 2
    Last Post: 04-14-2015, 08:28 AM
  3. Hotel Availability
    By masterdaave in forum Queries
    Replies: 4
    Last Post: 01-26-2015, 06:47 PM
  4. Hotel database
    By femiores in forum Database Design
    Replies: 1
    Last Post: 03-24-2012, 01:13 PM
  5. hotel reservation, please help
    By forgotten in forum Database Design
    Replies: 1
    Last Post: 08-12-2009, 10:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums