Available status of a Rental Item

[Pip''''n]

I have a rental program to rent and reserve rental units.
Bookings entered contain a [DateOut], [DateIn], and [DateReturned] for the
date of rental, expected date of return, and actual date returned
respectively.

I would like to display the current status of each unit.
The values that I would like to display for each unit is: Available,
Unavailable, Return Today, or Out Today.

These values are updated as the user chooses a date to search by. Each unit
is listed in a listbox. And all units are to be listed even if they do not
have any current or future bookings or reservations.

It is possible to have a booking that does not have a [DateIn]. In this
case, and the date chosen is after the [DateOut] the unit is to be marked as
"Unavailable" for all dates searched after this point until the unit is
actually returned.

Each unit can have multiple reservations (over different date ranges) but
can only have one current rental that is not returned.

I have made this work but it runs through 4 queries and is very confusing to
me. I'd like to simplify it but don't know how. I can do all the pieces
separately but dont know how to put them all together at once.

Any assistance is greatly appreciated,

Pip''n

 

[KARL DEWEY]

The values that I would like to display for each unit is: Available,
Unavailable, Return Today, or Out Today.

The problem is that Available, Return Today, or Out Today are actually the
same in that they are oppsite of Unavailable. Out Today could be
Unavailable also.

Need more criteria. So what order/precedence do you want them?
Unavailable - out with no DateIn (not expected)
Available - returned before today (clean and ready)
Out Today - DateIn = today (due today)
Return Today - part day rental (may need cleaning)

 

[Pip''''n]

Karl,

The status values would be set in this way:
cleaning isn't an issue really, and the turn around isn't tight. It is most
likely that a unit won't be rented the same day it is returned. There isn't a
specific time of day for return. Also, even though the unit is expected to
return on the [dateIN] it is possible that the renter might keep it and
return the unit the next day.

Unavailable: currently rented (could be that there is no [DateIN] entered
meaning that it is an extended rental. Could be a replacement unit while
theirs is waiting for parts and won't be returned until parts arrive and are
installed. (unknown time period)

Due Today: (unavailable) means that the unit is expected, according to the
[DateIn], to be returned that day.

Available: unit has no reservation for this day and is not currently rented.

Rented Today: (Available) unit has a reservation scheduled for this day but
is not currently rented. When customer arrives and rents unit, will change to
"unavailable"

there is also "Overdue" sorry, forgot that one. (Unavailable) Overdue means
that the rental has passed the "Due Today" or [DateIn] date.

Do you still require more information?
Thanks for your reply,

Pip''n

Unavailable, Return Today, or Out Today.

The problem is that Available, Return Today, or Out Today are actually the
same in that they are oppsite of Unavailable. Out Today could be
Unavailable also.

Need more criteria. So what order/precedence do you want them?
Unavailable - out with no DateIn (not expected)
Available - returned before today (clean and ready)
Out Today - DateIn = today (due today)
Return Today - part day rental (may need cleaning)

I have a rental program to rent and reserve rental units.
Bookings entered contain a [DateOut], [DateIn], and [DateReturned] for the
date of rental, expected date of return, and actual date returned
respectively.

I would like to display the current status of each unit.
The values that I would like to display for each unit is: Available,
Unavailable, Return Today, or Out Today.

These values are updated as the user chooses a date to search by. Each unit
is listed in a listbox. And all units are to be listed even if they do not
have any current or future bookings or reservations.

It is possible to have a booking that does not have a [DateIn]. In this
case, and the date chosen is after the [DateOut] the unit is to be marked as
"Unavailable" for all dates searched after this point until the unit is
actually returned.

Each unit can have multiple reservations (over different date ranges) but
can only have one current rental that is not returned.

I have made this work but it runs through 4 queries and is very confusing to
me. I'd like to simplify it but don't know how. I can do all the pieces
separately but dont know how to put them all together at once.

Any assistance is greatly appreciated,

Pip''n

 

[KARL DEWEY]

I used two queries, the first named Booking List.

SELECT Bookings.Item, Max([DateOut])-1 AS [Less Than]
FROM Bookings
GROUP BY Bookings.Item;


SELECT Bookings.Item, Bookings.DateOut, Bookings.DateIn,
Bookings.DateReturned, IIf([DateIn] Is Null And [DateOut] Is
Null,"Available",IIf([dateout] Is Null And
[datein]>=Date(),"Available",IIf([datereturned]<=Date(),"Available","Unavailable")))
AS Availability, IIf([dateout] Is Null And [datein] Is Null And
[datereturned] Is Null,"",IIf([Datein]<Date() And [datereturned] Is
Null,"Overdue",IIf([datein]=Date() And [datereturned] Is Null,"Due
Today","Out Today"))) AS Expected
FROM Bookings LEFT JOIN [Booking List] ON Bookings.Item = [Booking List].Item
WHERE (((Bookings.Item)=IIf([Booking List].[Item] Is Null,"*",[Booking
List].[Item])) AND ((Bookings.DateOut)>[Less Than])) OR (((Bookings.DateOut)
Is Null));

Karl,

The status values would be set in this way:
cleaning isn't an issue really, and the turn around isn't tight. It is most
likely that a unit won't be rented the same day it is returned. There isn't a
specific time of day for return. Also, even though the unit is expected to
return on the [dateIN] it is possible that the renter might keep it and
return the unit the next day.

Unavailable: currently rented (could be that there is no [DateIN] entered
meaning that it is an extended rental. Could be a replacement unit while
theirs is waiting for parts and won't be returned until parts arrive and are
installed. (unknown time period)

Due Today: (unavailable) means that the unit is expected, according to the
[DateIn], to be returned that day.

Available: unit has no reservation for this day and is not currently rented.

Rented Today: (Available) unit has a reservation scheduled for this day but
is not currently rented. When customer arrives and rents unit, will change to
"unavailable"

there is also "Overdue" sorry, forgot that one. (Unavailable) Overdue means
that the rental has passed the "Due Today" or [DateIn] date.

Do you still require more information?
Thanks for your reply,

Pip''n

The values that I would like to display for each unit is: Available,

Unavailable, Return Today, or Out Today.

The problem is that Available, Return Today, or Out Today are actually the
same in that they are oppsite of Unavailable. Out Today could be
Unavailable also.

Need more criteria. So what order/precedence do you want them?
Unavailable - out with no DateIn (not expected)
Available - returned before today (clean and ready)
Out Today - DateIn = today (due today)
Return Today - part day rental (may need cleaning)

I have a rental program to rent and reserve rental units.
Bookings entered contain a [DateOut], [DateIn], and [DateReturned] for the
date of rental, expected date of return, and actual date returned
respectively.

I would like to display the current status of each unit.
The values that I would like to display for each unit is: Available,
Unavailable, Return Today, or Out Today.

These values are updated as the user chooses a date to search by. Each unit
is listed in a listbox. And all units are to be listed even if they do not
have any current or future bookings or reservations.

It is possible to have a booking that does not have a [DateIn]. In this
case, and the date chosen is after the [DateOut] the unit is to be marked as
"Unavailable" for all dates searched after this point until the unit is
actually returned.

Each unit can have multiple reservations (over different date ranges) but
can only have one current rental that is not returned.

I have made this work but it runs through 4 queries and is very confusing to
me. I'd like to simplify it but don't know how. I can do all the pieces
separately but dont know how to put them all together at once.

Any assistance is greatly appreciated,

Pip''n