Hi
I am new to access and on a steep learning curve. I don't use VBA or SQL (Yet).
I am trying to create a booking management system for our 3 room small B & B. I have created and populated all the tables and relationships and everything talks to each other. However I have made an oversight and missed a trick.
Bookings each have a guest_ name, start_date, end_date and_room allocated etc.
Rooms can be closed out for a night. The close outs use the same start_date and room_allocated. All of these dates are kept in seperate linked tables to prevent duplication.
I need to be able to count the number of individual room nights between a variable number of days in any given period. I have used a parameter query that can count the number of room nights closed out, but I cannot count the number of room nights as any query I make only looks to a start date or end date falling in the given period, not any days in between.
For example, if I have a guest staying between 1-April-12 to 13-April-12 and I am searching for occupied rooms between 3/4/12 and 10/4/12 it wouldn't pick this guest (or the occupied rooms) up. If I was searching between 30th March and 3rd April it would.
I have assumed that I could create a query to build a table of dates for each booking, but I am not sure if this would be wasteful and would rather get the result each time I query, rather than create a new table. Anyhow, I can't seem to creat the table anyway, and to go outside to build it in excel and import seems a bit defeatist.
My head hurts, can anyone suggest an Access solution (not for the headache!)