allocating rooms!

[Guest]

I'm making a database for a housing company where i have to keep a record of
each tenent and also of each room.

I have set up records for each and every room and have them in a table
called StudentRooms, this is related to a table in the Students table which
has a copy of the primary key from the students room. In this case the
primary key is a combination key of the Building, Block, Flat and RoomNo.

Each student has a student Reference (studentRef) which is used as their PK.
What was needed was a way to allocate student to an unoccupied room, which
ive done by using the Tenent property of each room, which has a copy of the
studentRef if occupied.

Each students also has a StartTenencyDate and EndTenencyDate to show when
the room is going to be empty (a search for a specified date will yield which
rooms are empty and which can be allocated).

The problem is that the company takes bookings in advcance of the next
academic year and need to be able to allocate rooms. Howver ive set it up so
that they can only allocate empty rooms (so not to double book) but this is
also stopping them from booking the room for next year as they arent being
shown all the rooms, only the empty ones even though on that future date the
room will be empty.

I feel i have all the peices but im just not able to see how to make it work
properly.

Ill be happy to add any extra information.

Wtih Regards

Amit

 

[SUZYQ]

How about adding a field for year to the table with building, block,
flat, etc. and make it part of the key. Then alter your queries to
show empty rooms by year.

Does this help?

Regards,

Susan

 

[Guest]

I was thinking about something like that but it would mean creating a new set
of rooms for each consectuive year, though that might be useful for tracking
changes to that particular room.

Just having problems with changing the PK and also all the references to the
PK throughout the database.

Thanks Susan

 

[Guest]

I was trying to do as you suggested and it just felt like a very clumsy way
of geting around the problem. The problems its caused in terms of updating
other parts of the database has meant that its unfeasible.

IM thinking that i need to have a query that checks when a room is available
(which i already have) by using the StartDate and EndDate of the student and
then use this value for that partiular room to see if the room is free on the
startdate of the newStudent.

But thanks for your help, it gave me someideas to work with.

Amit

 

[Van T. Dinh]

I am not sure I understood your Table description correctly but it sounds to
me that you have a Many-to-Many relationship (each Student / Person can be a
Tenant for different Accommodation Place / Room at different time / same
time and each Accommodation Place / Room can be occupied by different
Persons at different time. To accommodate the Many-to-Many relationship
between 2 entities, you need to use 3 Tables:

* tblPerson with PersonID as the PK.
* tblAccommodationPlace with AccPlaceID as the PK.
* tblTenancy with TenancyID as the PK and both PersonID and AccPlaceID as
ForeignKeys in this Table. This is also where you store the StartDate and
EndDate of the Tenancy.

Once you have the correct Table Structure in place, you should be able to
create a Query that show available / unoccupied AccommodationPlaces for a
given period of time, possibly using a Left Outer Join or a Not In clause in
the SQL.

 

[Guest]

Hi Van,

No its not a many to many relationship, it should works out that each room
can have many tenents over a period of time (Years).

The Building,block,Flat,RoomNo is a foreign key in the Students table and im
thinking of using the following logic to allocate rooms:

If any other studnts have this room
if the end date of their tenency < start date of the new student
associate the student with the room
else
Room is occupied when the student wants to move in
end if
else
add student
end

I'm using a query to see which rooms are unallocated and checking to see if
the room i want to occupy is on that list, if if is then i want to check the
end dates and start dates of the two students, but im not sure how to do this.

The startdate of the new student is coming off the registration form so all
of this has to work in the background and give a result that i can use with
the form.

Thinking of using VBA to do the logic structure and using queries to get the
results i need, however how would i import the results of the query into the
VBA and use it like that?

With much thanks

Amit

 

[Van T. Dinh]

Confirmed: A Room can be occupied by different Students in different years,
i.e. One Room is related to Many different Students.

Question: Do you want to allow a Student coming back years after years to
occupy different Rooms in different years, i.e. One Student is related to
Many Rooms?

If Yes (which I think would be reasonable) to the above question, you
definitely need M-to-M relationship.

Check the JET SQL Help on Left Outer Join and the (Not) In clause (which I
mentioned in my earlier post).