simple (to everybody else) query about a query

[dee dee]

for a hotel database i have check in date and check out date

how do i get a list of current occupants, ie a search using todays date

sorry if this is simple, but i am not great at access. i hate vb code as well

 

[Dennisk]

test the [checkout date] is null and the [checkin Date] is not null

 

[dee dee]

i have a long list of check in dates, some from the past, some the future.

same for check out


i need a search to give me a list of customers presently there

 

[Michael J Ross]

Try <=Date() as the criteria under CheckIn and >=Date() under CheckOut ,

this should just select the current occupants.

 

[dee dee]

hey it works!! thanks

right, to ask a more advanced question.


supposing i had four hotels

• red
• blue
• green
• yellow

each with 20 beds. how could i add up the number of customers on a particular date and produce a report showing empty beds on a certain day

 

[The_Doc_Man]

IF you always have a check-in and check-out date (even if the latter is just an estimate), you can look at selecting all records where the current date is BETWEEN the check-in date AND the check-out date. Look up operator BETWEEN ... AND ... to see more on how this works.

 

[Michael J Ross]

Hi,

Ok for your second question, group by hotel in your query and add a calculated field to calculate the available occupancy something like the following

AvailableOccupancy: 20-Sum([NoofGuests*])

* or whatever you call your field that stores the number of customers booked in for each booking.

HTH