finding empty rooms!

[DowningDevelopments]

Hello everyone,

I am making a database that stores information about student tenants for a
housing company. I am building a from which will help them to see which rooms
are empty and which are taken. The rooms are kept in a table called
[StudentRooms] and the students are kept in a table called [students].
The studentRooms table has a combination key consisting of Building, Block,
Flat and RoomNo which act as foreign keys in the Students table.

I had a query which i thought worked but have now found doesnt, each student
has a [StartDate] and an [EndDate] and i was using the following query to
check to see if a room was available (it basically says, find all the rooms
where the building, block and flat are matching whats on the form and the
startdate of the new student is more than the end date of an existing
student):

SELECT StudentRooms.RoomNo, Students.EndDate, Students.StudentRef
FROM StudentRooms LEFT JOIN Students ON (StudentRooms.BuildingRef =
Students.Building) AND (StudentRooms.BlockRef = Students.BlockNo) AND
(StudentRooms.Flatref = Students.FlatNo) AND (StudentRooms.RoomNo =
Students.RoomNo)
WHERE (((StudentRooms.BuildingRef)=[forms]![StudentsNew]![Building]) AND
((StudentRooms.BlockRef)=[forms]![StudentsNew]![BlockNo]) AND
((StudentRooms.Flatref)=[forms]![StudentsNew]![FlatNo]) AND
((Students.EndDate)<[Forms]![StudentsNew]![Startdate]))
ORDER BY StudentRooms.BuildingRef;

What should i be doing to find out if a room is empty right now?

with thanks
Amit

 

[Stuart Bratesman]

Here's a partial answer. I'm guessing that the Students table will have
multiple listings for a given room - i.e. it will list a row for the student
who occupied the room this fall, and another row for the student who occupied
the same room last spring and winter, and another row for the student who
occupied the room the year before that.

Here's another wrinkle. I'm also guessing that your system will hold
information about future reservations. That is, for a given room X, your
Students table will hold information about students who used to live in that
room, and about the student who has already reserved the room for next fall
and winter.

So, assuming that this is a single-user database, here's a quick and dirty
way to solve the problem.

Add a Vacant (yes/no) flag to the StudentRooms table.

When you click on the button on your StudentsNew form to find all rooms that
match your selection criteria, use an Update query to set the Vacant value
for every room in the StudentRooms table to YES.

Then run a second update query that identifies every row in the Students
table WHERE (Students.EndDate +3) [to give you time to clean the room between
tenants] is greater-than-or-equal-to Forms!StudentsNew!StartDate AND
(Students.StartDate - 3) is less-than-or-equal-to Forms!StudentsNew!EndDate
and updates the VACANT flag in the corresponding linked row in the
StudentRooms table to NO.

Now you can use a select query to display all the remaining rooms that are
still marked VACANT = YES and that also meet all your other selection
criteria.

--
Stuart Bratesman, Jr., MPP
Muskie School of Public Service
Univ. of Southern Maine
Portland, Maine

Hello everyone,

I am making a database that stores information about student tenants for a
housing company. I am building a from which will help them to see which rooms
are empty and which are taken. The rooms are kept in a table called
[StudentRooms] and the students are kept in a table called [students].
The studentRooms table has a combination key consisting of Building, Block,
Flat and RoomNo which act as foreign keys in the Students table.

I had a query which i thought worked but have now found doesnt, each student
has a [StartDate] and an [EndDate] and i was using the following query to
check to see if a room was available (it basically says, find all the rooms
where the building, block and flat are matching whats on the form and the
startdate of the new student is more than the end date of an existing
student):

SELECT StudentRooms.RoomNo, Students.EndDate, Students.StudentRef
FROM StudentRooms LEFT JOIN Students ON (StudentRooms.BuildingRef =
Students.Building) AND (StudentRooms.BlockRef = Students.BlockNo) AND
(StudentRooms.Flatref = Students.FlatNo) AND (StudentRooms.RoomNo =
Students.RoomNo)
WHERE (((StudentRooms.BuildingRef)=[forms]![StudentsNew]![Building]) AND
((StudentRooms.BlockRef)=[forms]![StudentsNew]![BlockNo]) AND
((StudentRooms.Flatref)=[forms]![StudentsNew]![FlatNo]) AND
((Students.EndDate)<[Forms]![StudentsNew]![Startdate]))
ORDER BY StudentRooms.BuildingRef;

What should i be doing to find out if a room is empty right now?

with thanks
Amit

 

[DowningDevelopments]

thanks to stuart for trying to help out but i came up with my own method of
checking for empty rooms using a subquery to veiw all the rooms that are
taken up on the start date of the tenant and then picking out rooms not in
that subquery result set.

The codes a bit of a mouthful but thats basically what its saying. The first
3 lines and the last 2 or 3 just match up the flat to what the user has
searched for and then compares it with the subquery result set. took me
almost a day to figure this out! hope it helps some of ya out there

SELECT StudentRooms.RoomNo
FROM StudentRooms
WHERE (((StudentRooms.RoomNo) Not In (SELECT Students.RoomNo
FROM Students
WHERE (((Students.EndDate)>[forms]![studentsnew]![startdate] Or
(Students.EndDate) Is Null) AND
((Students.Building)=[forms]![StudentsNew]![Building]) AND
((Students.BlockNo)=[forms]![StudentsNew]![BlockNo]) AND
((Students.FlatNo)=[forms]![StudentsNew]![FlatNo])) OR
(((Students.StudentRef) Is Null) AND
((Students.Building)=[forms]![StudentsNew]![Building]) AND
((Students.BlockNo)=[forms]![StudentsNew]![BlockNo]) AND
((Students.FlatNo)=[forms]![StudentsNew]![FlatNo])) OR
(((Students.Proceeding)=True) AND
((Students.Year)=[forms]![StudentsNew]![year]) AND
((Students.Building)=[forms]![StudentsNew]![Building]) AND
((Students.BlockNo)=[forms]![StudentsNew]![BlockNo]) AND
((Students.FlatNo)=[forms]![StudentsNew]![FlatNo]))
ORDER BY Students.Building)) AND
((StudentRooms.BuildingRef)=[forms]![StudentsNew]![Building]) AND
((StudentRooms.BlockRef)=[forms]![StudentsNew]![BlockNo]) AND
((StudentRooms.Flatref)=[forms]![StudentsNew]![FlatNo]));