Show daily events from a date range

Hello all

I've started using FM and so far have been blown away by the power and ease of use. I've managed to get an application up and running in no time. I have previous experience with Sql scripting and MS Access, but I'd say I'm still pretty novice.

I'm building a DB that will be used for guest booking in a hostel where I work.

I have the following tables:

Guest

Rooms

I keep contact info in Guest and the room and dates they are staying in Rooms, linked by ID.

I want to map out per day who is in what room. As we have multiple people in each room, i want to see how many people are in room 3 on a given date. I'd like to display this in a rolling calendar view across the top. So you can see by scrolling along how many people we have in each of the rooms along the dates.

I think i need to get another table with Date, Room, count(GuestID) for each of the days of the year, but Don't known how to break down the date range in the Rooms table to show a per day value.

And I don't even know where to actually start looking for a solution.

Can someone put me on the right path, so as I can at least know what I'm looking to try to do.

So to simplify.

I have Bob staying in Room 1 from 1feb to 10feb

I have Jane staying in Room 1 from 4feb to 7feb.

I'd like to see:

Feb 1 2 3 4 5 6 7 8 9 10

Rm1: 1 1 1 2 2 2 1 1 1 1

But the dates will need to run across the top for as long as we have bookings.

Thanks in advance!