Room Distribution DB (1 Viewer)

[smtazulislam]

Hi, I want to create a DB for employees room distribution.
In this tblRoomDistribution table I put basic data.

We have 4 Buildings. And each building have 3 Floors with minimum 20 rooms capacity
Each room bed capacity minimum 2 beds.
And
1. Want to search By Building No : How many Room and total employee stayed.
2. Want to search by Room No : How many bed is filled out or empty.
3. Want to search by EmployeeID : Which Building and which numbers of room he stay.

My question:
For this 3 things how can I create table, query and relationship.

I attached DB for redesign data. Need your suggest & support.
anyone can edit field name or Add any new fields.
Any help will appreciate.

 

[mike60smart]

Hi You have problems with your Relationships. I am unable to enforce Referential Integrity between tblRoomDistribution and the related tblRoomDetails & tblEmployees.

You have records in tblRoomDistribution with no related record in tblRoomDetails. The same applies to tblEmployees.

 

[mike60smart]

Also you currently have no table for the BuildingFloors ?

 

[mike60smart]

Is this by any chance a School assignment?

 

[smtazulislam]

Hello Mike,
Thank you so much for your reply.

actually, I want below 3 things as perfect result. For that I needs advice create table, query, relationship design that all.
1. Want to search By Building No : How many Room and total employee stayed.
2. Want to search by Room No : How many bed is filled out or empty.
3. Want to search by EmployeeID : Which Building and which numbers of room he stay.

tblRoomDistribution table have basic record information.

 

[Cronk]

You indicate that the minimum number of beds is 2 per room. I would think the capacity of the rooms would be critical in allocating people to a room.

 

[smtazulislam]

You indicate that the minimum number of beds is 2 per room. I would think the capacity of the rooms would be critical in allocating people to a room.

Here I means, our some room is big size. so there 4 or 5 beds is available.

 

[Cronk]

Sorry, I now see you have data relating to number of beds in your tblRoomDetails.

I suggest your tblRoomDistribution has one date field rather than two for JoinDate and LeaveDate. If an employee stays 5 nights, there would be 5 entries in this table. It's then a lot easier to answer your questions in #1. Also provides for an employee to change rooms during a particular stay.

Also BID is superfluous in the same table. The RID identifies which building it is.

Also you mentioned multi storied buildings do you want to have that data included?

 

[smtazulislam]

Also BID is superfluous in the same table. The RID identifies which building it is.

What you means BID superfluous.
If I dont added BID how can I link with this table ?

 

[Cronk]

You join on BuildingNo. But better still would be to change the field in tblRoomDetails from buildingNo to BID