Looking for the best way to calculate ANY intersection between MS Access date ranges

Hi all,

I am helping out with an accomodation booking system of sorts. The schema has already been decided upon whereby one or more persons P(x) are accomodated with a particular householder H. Each householder accomodates up to P(n) persons on any one date.

The schema outline is:
--------------------------- Householder entity type -------------------------
HouseholderID Autonumber PK
Householder Text
Address Text
Accomodates Integer (the maximum number of persons that can be accomodated at any one time)
.... other Householder specific attributes.

--------------------------- Person entity type -------------------------
PersonID Autonumber PK
Firstname Text
Surname Text
.... other Householder specific attributes

--------------------------- Householder accomodates person entity relationship type -------------------------
HouseholderID  - composite PK -
PersonID           - composite PK -
StartDate Date

What I am looking for is the most efficient way to determine the availability of householders to provide accomodation, such that:

Assuming I want to accomodate a new person P(z) on a particular Date D for N days, I want to query the relationship Householder Inner Join (Householder accomodates person) Inner Join Person so that D+N does not intersect StartDate+NumberOfDays, or if it does, it returns the number of days that each householder could provide ( ranked by available days)
LVL 13
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAsked:
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
hi fredthered,
its been  a long time....

this link will get you started...


better if you have some data available... for testing
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
Hi Capicorn1,

It most certainly has been a long time. I won't say that i've been going onwards and upwards, rather shuffling sidewards. Your solution looks pretty much what I am looking for, but I will have to pass this by project owner and test same; leave with me for a couple of days.

John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
While this isn't exactly what I was looking for, it is; as you say; a good start capicorn1.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.