Link with tables

[spids101]

Hello Access Programmers,

I need bit guidance with my database.

I have 2 tables, one with student details and the other with apartment details. I would like to set it up so that a student can be easily assigned an apartment and when the apartment is available it can be seen in the database. Is this achievable in access?

Thanks in advance for any help.

 

[Ranman256]

yes.
I would hope, in tStudentDetail table, there is an AptID field.
This would be filled with the AptID.
A query would scan studentDtl.aptID fields to see what apts are taken.

Conversely, I would have a tApt table AND an tAptOccu table that stores each student Id IN the apt.

tAptOcc:
-------------
tAptID
tStudentID
tInDate

Then any AptID not in tAptOcc table would mean the apt is available.

 

[spids101]

yes.
I would hope, in tStudentDetail table, there is an AptID field.
This would be filled with the AptID.
A query would scan studentDtl.aptID fields to see what apts are taken.

Conversely, I would have a tApt table AND an tAptOccu table that stores each student Id IN the apt.

tAptOcc:
-------------
tAptID
tStudentID
tInDate

Then any AptID not in tAptOcc table would mean the apt is available.

Thanks Ranman for your reply.

It has been some help but still not entirely sure how to get this to work.

I only had a student details table and apartment details table. So create a query and link through the ids? Would they be manually typed into field in the student table?

 

[DavidRBoyle]

yes.
I would hope, in tStudentDetail table, there is an AptID field.
This would be filled with the AptID.
A query would scan studentDtl.aptID fields to see what apts are taken.

Conversely, I would have a tApt table AND an tAptOccu table that stores each student Id IN the apt.

tAptOcc:
-------------
tAptID
tStudentID
tInDate

Then any AptID not in tAptOcc table would mean the apt is available.

The relationship originally described is one to many with one student potentially occupying one of many apartments. There is no request for multiple students to occupy the same apartment.

The use of the junction table described above would resolve a many to many relationship which might one day become appropriate, but it is not what the OP asked originally.

 

[DavidRBoyle]

You need to create a foreign key in the Apartment table that references the student table.

Empty apartments are those where this field is null.

I think you should start as simply as possible,

 

[spids101]

You need to create a foreign key in the Apartment table that references the student table.

Empty apartments are those where this field is null.

I think you should start as simply as possible,

Thanks David for your reply.

I understand what you are saying a bit better, should be able to accomplish this. Is there anything I can do if there are multiple rooms available in an apartment? Or would I have to create separate entries like this: apart101 room1, apart101 room2, apart101 room3 etc.

Thanks again for your help its very much appreciated.

 

[GinaWhipp]

Perhaps this will help, it's a Property Management Data Model...
http://www.access-diva.com/dm3.html

 

[vbaInet]

To supplement Gina's model, here's a generalised view:
http://www.databaseanswers.org/data_models/real_estate_rentals/index.htm