Availability between dates

[Eddie Holder]

Hi Guys - I hope someone can help. I have a database that hires out products,
and need help to try and find which products are available for hire between
date A and data b. Here it goes

I have a database with 2 tables.
Table 1 - this holds product information with each product having a unique
ID and belonging to a category, e.g. seats, chairs, tables, etc

Table 2 - This holds all the hired out products and the date from and the
date to when it's hired out, e.g. Product 1, in seats category is hired out
from the 01/01/09 to the 15/01/09

When I have a request for a new hire, I would like to be able to run a query
that will return which products can be hired based on the hire request date
from and request date to, e.g. I would like to hire a seat from the 10/01/09
to the 13/01/09.

As we will have multiple products in each category, I would like to search
by category to find which products are available. e.g. show me all seat
products that can be hired on the hire request date.

Any advice or help will be appreciated.


Thank you in advanced!
Eddie

 

[Michel Walsh]

The interval [start, end] does not overlap at all the interval [fromThis,
toThis] if, and only if


fromThis > end OR toThis < start


and there is some overlap (partial or total) with the negation of the
previous statement, or if you prefer, if:


fromThis <= end AND toThis >= start


(using the so called De Morgan's law)



So, a possible solution is to check all items with a possible overlap, then,
make a second query which will look at all the items not in the first set.
The first query is thus:

SELECT itemID
FROM table2
WHERE hireoutFrom <= [periodEnd] AND hireoutUntil >= [periodStart]


which returns items 'hired' within (some part of) the period
[periodStart], [periodEnd] . Assume you save this query as q1. The second
and final query is:



SELECT itemID
FROM table1 LEFT JOIN q1
ON table1.itemID = q1.itemID
WHERE category = [wantedCategory]
AND q1.itemID IS NULL


would return the items not hired for any part of the said period, said
[wantedCategory].






Vanderghast, Access MVP

 

[Eddie Holder]

Thanks - I will give this a go. Thaks a million!

Cheers
Eddie
--


Alwyas Trying
Eddie

The interval [start, end] does not overlap at all the interval [fromThis,
toThis] if, and only if


fromThis > end OR toThis < start


and there is some overlap (partial or total) with the negation of the
previous statement, or if you prefer, if:


fromThis <= end AND toThis >= start


(using the so called De Morgan's law)



So, a possible solution is to check all items with a possible overlap, then,
make a second query which will look at all the items not in the first set.
The first query is thus:

SELECT itemID
FROM table2
WHERE hireoutFrom <= [periodEnd] AND hireoutUntil >= [periodStart]


which returns items 'hired' within (some part of) the period
[periodStart], [periodEnd] . Assume you save this query as q1. The second
and final query is:



SELECT itemID
FROM table1 LEFT JOIN q1
ON table1.itemID = q1.itemID
WHERE category = [wantedCategory]
AND q1.itemID IS NULL


would return the items not hired for any part of the said period, said
[wantedCategory].






Vanderghast, Access MVP

Hi Guys - I hope someone can help. I have a database that hires out
products,
and need help to try and find which products are available for hire
between
date A and data b. Here it goes

I have a database with 2 tables.
Table 1 - this holds product information with each product having a unique
ID and belonging to a category, e.g. seats, chairs, tables, etc

Table 2 - This holds all the hired out products and the date from and the
date to when it's hired out, e.g. Product 1, in seats category is hired
out
from the 01/01/09 to the 15/01/09

When I have a request for a new hire, I would like to be able to run a
query
that will return which products can be hired based on the hire request
date
from and request date to, e.g. I would like to hire a seat from the
10/01/09
to the 13/01/09.

As we will have multiple products in each category, I would like to search
by category to find which products are available. e.g. show me all seat
products that can be hired on the hire request date.

Any advice or help will be appreciated.


Thank you in advanced!
Eddie

 

[Ken Sheridan]

Eddie:

You might find the following query helpful as a model. It is for returning
rooms of a specific type vacant for the whole of a date range, so is
analogous to what you want:

PARAMETERS [Enter Start Date:] DATETIME,
[Enter End Date:] DATETIME,
[Enter Room Type:] TEXT(50);
SELECT RoomNumber
FROM Rooms
WHERE RoomType = [Enter Room Type:]
AND NOT EXISTS
(SELECT *
FROM RoomOccupations, Calendar
WHERE CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND CalDate >= EntryDate
AND (CalDate <= DepartureDate OR DepartureDate IS NULL)
AND RoomOccupations.RoomNumber = Rooms.RoomNumber)
ORDER BY RoomNumber;

The Rooms table is your Products table, the RoomOccupations table your Hires
table. RoomNumber = your Product; RoomType = your ProductCategory;
EntryDate = your HiredFrom date; DepartureDate = your HiredTo date.

It also uses a table Calendar, with a column CalDate. This is simply an
auxiliary table of all dates over a range, 10 years say, and is very easily
created by serially filling down a column in Excel and then importing it into
Access as a table. A calendar table like this is useful for many purposes.

The query also caters for open-ended bookings, e.g. if a product is hired
today until further notice, so the HireTo date column is left Null, that
product won't be returned if a date range which includes any date on r after
today is specified.

Ken Sheridan
Stafford, England

 

[Eddie Holder]

Ken, thank you so much for the answers. Although late, you have just saved us
loads of time and your instructions were spot on!

Thanks

Eddie
--


Alwyas Trying
Eddie

Eddie:

You might find the following query helpful as a model. It is for returning
rooms of a specific type vacant for the whole of a date range, so is
analogous to what you want:

PARAMETERS [Enter Start Date:] DATETIME,
[Enter End Date:] DATETIME,
[Enter Room Type:] TEXT(50);
SELECT RoomNumber
FROM Rooms
WHERE RoomType = [Enter Room Type:]
AND NOT EXISTS
(SELECT *
FROM RoomOccupations, Calendar
WHERE CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND CalDate >= EntryDate
AND (CalDate <= DepartureDate OR DepartureDate IS NULL)
AND RoomOccupations.RoomNumber = Rooms.RoomNumber)
ORDER BY RoomNumber;

The Rooms table is your Products table, the RoomOccupations table your Hires
table. RoomNumber = your Product; RoomType = your ProductCategory;
EntryDate = your HiredFrom date; DepartureDate = your HiredTo date.

It also uses a table Calendar, with a column CalDate. This is simply an
auxiliary table of all dates over a range, 10 years say, and is very easily
created by serially filling down a column in Excel and then importing it into
Access as a table. A calendar table like this is useful for many purposes.

The query also caters for open-ended bookings, e.g. if a product is hired
today until further notice, so the HireTo date column is left Null, that
product won't be returned if a date range which includes any date on r after
today is specified.

Ken Sheridan
Stafford, England

Hi Guys - I hope someone can help. I have a database that hires out products,
and need help to try and find which products are available for hire between
date A and data b. Here it goes

I have a database with 2 tables.
Table 1 - this holds product information with each product having a unique
ID and belonging to a category, e.g. seats, chairs, tables, etc

Table 2 - This holds all the hired out products and the date from and the
date to when it's hired out, e.g. Product 1, in seats category is hired out
from the 01/01/09 to the 15/01/09

When I have a request for a new hire, I would like to be able to run a query
that will return which products can be hired based on the hire request date
from and request date to, e.g. I would like to hire a seat from the 10/01/09
to the 13/01/09.

As we will have multiple products in each category, I would like to search
by category to find which products are available. e.g. show me all seat
products that can be hired on the hire request date.

Any advice or help will be appreciated.


Thank you in advanced!
Eddie