subqueries to find tenants in the same room at the same time!

[DowningDevelopments]

I have a table called students which shows information about students on a
database for a student housing company. I am trying to figure out which
students have been allocated to the same room at the same time.

Every student has a [students].[RoomRef] which says where they are staying
and ive done a find duplicates query of the RoomRef.

However this also shows previous tenants in the same room which is fine,
what im after is students who are in teh room at the same time
(misallocated). To do this i want to use the [students].[startDate] and
[students].[endDate] to compare if the end date of one student overlaps with
the startdate of the next, if there is then there has been a misallocation.

So far my duplicates query looks like this:

SELECT Students.RoomRefer, Students.StudentRef, Students.StartDate,
Students.EndDate
FROM Students
WHERE (((Students.RoomRefer) In (SELECT [RoomRefer] FROM [Students] As Tmp
GROUP BY [RoomRefer] HAVING Count(*)>1 )))
ORDER BY Students.RoomRefer;

How could i change this to show if there are overlaps for tenants of the
same room?

With much thanks for any help

Amit

 

[Allen Browne]

There is an overlap if:
A starts before B ends,
AND
B starts before A ends.

One way to find all overlaps is to drag a second copy of the Student table
into the query design window. Access will alias it as Student_1. Set up your
criteria like this:
Field Criteria
==== =====
Student.StartDate: < Student_1.EndDate
Student_1.StartDate: < Student.EndDate
Student.RoomRef: Student_1.RoomRef
Student.StudentID: <> Student_1.StudentID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[Tom Ellison]

Dear Amit:

First a small detail. If a student has a certain EndDate and another
student has that as a StartDate, is this an overlap?

Now, to do this, I think you need to join the Students table on itself.
This means you consider the Students table twice in the same query.

It may be like this:

SELECT S1.RoomRefer, S1.StudentRef, S2.StudentRef
FROM Students S1
INNER JOIN Students S2
ON S1.RoomRefer = S2.RoomRefer

At this point, the query will show all students that have ever occupied the
same room in all combinations of pairs. This will ignore (for the moment)
whether the stays overlapped.

Now, to filter out those that do not overlap.

To find overlaps, add this to the above (at the end):

WHERE (S1.StartDate >= S2.StartDate
AND S1.StartDate <= S2.EndDate)
OR (S1.EndDate >= S2.StartDate
AND S1.EndDate <= S2.EndDate)

Finally, you need to eliminate the overlap of a student with himself:

AND S1.StudentRef <> S2.StudentRef

Then add your ORDER BY if you wish.

If you want to ignore when the end date of one student is the start date of
another, change the second part:

WHERE (S1.StartDate >= S2.StartDate
AND S1.StartDate < S2.EndDate)
OR (S1.EndDate > S2.StartDate
AND S1.EndDate <= S2.EndDate)

Now, this says Joe and Mark shared the room, but also Mark and Joe shared
it. To eliminate this duplication, change the last line I gave you:

AND S1.StudentRef < S2.StudentRef

Finally, add S1.StartDate, S1.EndDate, S2.StartDate, and S2.EndDate to the
SELECT clause if you want to see them. You will probably want to add
aliases.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

I have a table called students which shows information about students on a
database for a student housing company. I am trying to figure out which
students have been allocated to the same room at the same time.

Every student has a [students].[RoomRef] which says where they are staying
and ive done a find duplicates query of the RoomRef.

However this also shows previous tenants in the same room which is fine,
what im after is students who are in teh room at the same time
(misallocated). To do this i want to use the [students].[startDate] and
[students].[endDate] to compare if the end date of one student overlaps
with
the startdate of the next, if there is then there has been a
misallocation.

So far my duplicates query looks like this:

SELECT Students.RoomRefer, Students.StudentRef, Students.StartDate,
Students.EndDate
FROM Students
WHERE (((Students.RoomRefer) In (SELECT [RoomRefer] FROM [Students] As Tmp
GROUP BY [RoomRefer] HAVING Count(*)>1 )))
ORDER BY Students.RoomRefer;

How could i change this to show if there are overlaps for tenants of the
same room?

With much thanks for any help

Amit

 

[DowningDevelopments]

That was ingenious!!! i never thought of using the same table twice in the
same query! but its worked a treat and ive got a nice recordset that we can
deal with.

with great thanks to both yourself and Mr Browne

Amit

 

[Tom Ellison]

Dear Amit:

Any time you need to consider two records at the same time, you need to have
available two copies of the same table. There are various ways this can be
done: the inner join or other joins, or a subquery, for example.

With a little experience, you should be able to anticipate this and see how
to do it. I agree it's ingenious. I didn't invent it.

Tom Ellison