Solved Need help regarding my databse. Seeking experts advice. (1 Viewer)

[wikihow]

I am preparing a database. In databse, I have two linked tables. One parent and one child table with more records. Parent table would have some records of similar items, for those child table will have to have same record except the primary key. I want to avoid time to type same information in child table again and again for similar parent table records.

Tables are Units (Parent) and Rooms (Child).

Where Units have different identity for each Apartment, while they are grouped as per design i.e. Two or more apartments can be of same design Type A.
Type A apartment will have same type of rooms 3 bedrooms and bathrooms unlike other type B, C etc. I want rooms table to have same record for all these Type A apartments.

I want to write code or append query which can add same records for each type in child table.

Thanks in advance.

 

[MajP]

I want to write code or append query which can add same records for each type in child table

I doubt that is the correct approach. It sounds to me you need a simple junction table to do a many to many relationship.
So A unit can be related to a lot of room types and a room type can be related to a lot of rooms

tblUnit_Rooms
-UnitID_FK
-RooID_FK
-... other fields unique to that specific unit room relation

 

[wikihow]

Thank you for your reply. Sorry i am not expert in access. Yes, I have used one to many relationship and not many to many. You may be able to get better idea from the database. I am attaching herewith. There is chain of relationship i.e. Client --> project --> unit types --> units --> rooms (rooms in unit). I am just stuck because of apartment grouping. I will try to work as you have advised above with a junction table and will get back to you.

Thanks again for helping out.

 

[MajP]

I will try to work as you have advised above with a junction table and will get back to you.

Without looking at the DB, that was just a guess based on the limited description. You would have to confirm if the assumption is correct. It may or may not be correct. I will take a look.

 

[MajP]

wo or more apartments can be of same design Type A.
Type A apartment will have same type of rooms 3 bedrooms and bathrooms unlike other type B, C etc. I want rooms table to have same record for all these Type A apartments.

This is different from what I thougt. Based on that description and your tables it looks like a room is related to a unit type and not specific to a unit. So in your room table have a foreign key to Unittype and get rid of unitID.

Unit_Type_FK
Make a composite index by the unit_type_fk and the unit name. This way you can have many Bedroom1. But only one Bedroom1 for unit type A. Get rid of the unit ID.
That is based on what I see. You currently are not tracking specific information about that bedroom in that specific unit. You only need to know that unit is of type A and type A has 3 bedrooms.

 

[MajP]

Just to be clear. Currently the only thing you are tracking is the room type. So the relationship I describe would work. However if your plan is to have specific details about a room in a specific unit (paint color, carpet age, date cleaned....etc) then you will need your original relationship. In that case you would use an insert query. You would pick the unit type and it would create records for each type of room for that unit (unit type). Then you have a unique room unit combination. Currently that level does not seem needed.

 

[wikihow]

Thank you MajP for your precious time for the review and the advice. I will work as per your method. BTW, i also need to add components to each room in each apartment unit. Each type of group i.e. Type A, B or C will have same layout of rooms and components as well.

 

[MajP]

I will work as per your method.

Not sure which one that is and it is fundamentally important to get it right now. If you need specific details about a bedroom in a specific unit you need your original design. If you only need to know that a unit is of unittype A and unittype A has 3 bedrooms, kitchen, .... then use my other suggestion. It depends on the level of detail you are tracking.

BTW, i also need to add components to each room in each apartment unit. Each type of group i.e. Type A, B or C will have same layout of rooms and components as we

So again this depends on the above decision. That sounds a little confusing. Are components unique to a room of a unit type, or are components unique to a room in a specific unit? Assuming all rooms of a unit type have the same components.
Then table components would be related to a roomID. Since a room is related to UnitType. Then any unit of that unit type would get the same room configuration and each of those rooms the same components.

 

[wikihow]

Not sure which one that is and it is fundamentally important to get it right now. If you need specific details about a bedroom in a specific unit you need your original design. If you only need to know that a unit is of unittype A and unittype A has 3 bedrooms, kitchen, .... then use my other suggestion. It depends on the level of detail you are tracking.


So again this depends on the above decision. That sounds a little confusing. Are components unique to a room of a unit type, or are components unique to a room in a specific unit? Assuming all rooms of a unit type have the same components.
Then table components would be related to a roomID. Since a room is related to UnitType. Then any unit of that unit type would get the same room configuration and each of those rooms the same components.

Let me explain it to you. Assuming there is an apartment complex, which have 30 apartments. We know that there are 30 individual apartment units, but we do not want to fill in details of each apartment manually by typing. We also know that there are 3 Types of design of apartments. Type A is apartment 1 to 10, Type B is apartment 11 to 20 and Type C is apartment 21 to 30


Type A will have 3 bedrooms, two bathrooms, one lobby, 12 doors, 5 windows etc., Type B will have 2 bedrooms, one bathroom, no lobby, 8 doors, 3 windows, Type C will have one bedroom, one bathroom, no lobby, 6 doors, 2 windows.

So the aim is to reduce the typing work by entering individual records for each of the 30 units, but if it is possible to enter multiple records for these same 10 apartment types.

In my Table structure, I have used the relationship in a row. Client -->Project -->Apt. Types -->Apt Unit -->Rooms, whereas in rooms table, I want to add records based on criteria of Grouping from Apt. Types.

I am able to create an append query which can filter an existing entry of Apt type i.e. G101 and can add those records to rooms table, but the only problem i am having is that it copies records intact with AptID 1, while i need new records under AptID 2.

Do you have any idea on how to Append records but change/update one column value simultaneously. I tried append query without UnitID column, but getting an error.

Images attached:

Image 1 - UnitID column - Append works but wrong ID copied to new record. Need ID 2 and not 1. If any method to change or update while append processs?

Image 2 - if i remove UnitID from append

Image 3 - Getting error after removing UnitID

 

[MajP]

In my Table structure, I have used the relationship in a row. Client -->Project -->Apt. Types -->Apt Unit -->Rooms, whereas in rooms table, I want to add records based on criteria of Grouping from Apt. Types

Again. I do not agree that this is correct. You have not shown anything that suggests that is the correct relationship. As far as I can tell a unit has a set configuration and that is all the level of detail needed. For each specific room you do not track any information. If you are needing to do that where is it done? So there is no reason for this structure. I am not saying I am right, just saying everything you have shown so far says I am.
The relationship is from rooms (configuration) to unit types not to a specific unit.

The table should more likely look like this IMO.

Then simply query and you get
Query3

UnitName	UnitType	Room_or_Component	RoomComponentType	Quantity
101	Type B	Room	Bathroom	1​
101	Type B	Room	Bedroom	2​
101	Type B	Component	Doors	8​
101	Type B	Component	Windows	3​
102	Type B	Room	Bathroom	1​
102	Type B	Room	Bedroom	2​
102	Type B	Component	Doors	8​
102	Type B	Component	Windows	3​
G101	Type A	Room	Bathroom	1​
G101	Type A	Room	Bedroom	3​
G101	Type A	Component	Doors	5​
G101	Type A	Component	Lobby	1​
G101	Type A	Component	Windows	12​
G102	Type A	Room	Bathroom	1​
G102	Type A	Room	Bedroom	3​
G102	Type A	Component	Doors	5​
G102	Type A	Component	Lobby	1​
G102	Type A	Component	Windows	12​

Am I missing something, because you seem to be making this way to difficult, unless there is more to it and I am not seeing it.
Or a simple report

 

[wikihow]

Thank you MajP for looking into it so deeply. I appreciate all your efforts.
What i am trying to create here is an imitation of an existing web database. I am trying to create same or better options within access. I have attached few images for the information.

My final target is the not the rooms, but the items/components inside the rooms i.e. Bedroom 1 may have wardrobe, storage units etc.
Bedroom 2 can have a laundry, bathroom can have shower screen.


For creating a database, it is necessary to understand the process. I apologies for not clearing it all earlier.
Process is as given below:

1. Entering client details in client table i.e. creation of clients

2. Creating a project for any selected client

3. A project will have apartment units (need to have a unit schedule for it). Units are individual in general, but we are adding unit types so that we can go for any time saving approach so that we can add details about same type of units faster.

4. A single Apartment unit will have separate rooms. Bedroom 1, Bedroom 2, Lobby, Bathroom etc.

5. A rooms will have individual items like wardrobes, laundry, storage, mirror etc.

6. After working out the total schedule, we just need to add a price to the items to get a final value of project.


This is the starter milestone i am aiming for now. There is much more to go later regarding the prices and the specifications of item, but i will think of it after i am able to achieve my first milestone. The basic structure of tables.

It is much easy in excel, but requires a lot of duplicate data entry and excel is not worthy if we need to manipulate data as per our needs.

 

[MajP]

Here is the posted images for others to see.

 

[MajP]

So need to know if a Unit of a give type ALWAYS has the same room configuration. If so then the room configuration is related to the type. But it has to 100% of the time. If it can be configurable then you either need a new type or the configuration has to be related to the Unit even if most of the time they all have the same configuration. In other words can a Type A unit be slightly different than another Type A unit.

The next thing to decide is do you need to save the individual rooms or just the Configuration. If there is a small set of potential room configurations then I would have a table called RoomConfigurations like the list shown and similar to the table Rooms I posted. So instead of saving child records for each room for a unit you just need to save the configuration ID.
so Config 1 (using the image is)
2 Bedrooms
1 Linen
1 Bathroom
1 Ensuite

The next question is also important. Are the components (Robes, Mirrors) ALWAYS the same. In other words will Bedroom 1 of Type A always get 1 robe.

The big difference here is not so much the amount of data to enter, but how much is stored. To explain. If you said a Type A unit ALWAYS gets X configuration, I would not have to select a room configuration because all Type A rooms get the same configuration. I just relate through the type. If sometimes it changes a little or you need to apply details to the specific room, then when I create a unit of type A it would run an insert query based on the standard type A configuration. Now instead of a pointer you have a record for each room type. This way you can add, edit, delete items.

Do you have excel data? That may help to understand the level of detail needed and the needed flexibility.

 

[wikihow]

Hello MajP,

Tried some new relationships. I want to know if these would work or can give any problem further? Sorry I am no expert and just learning and understanding. Sharing with you for valuable feedback. I can get all the details required in the query. Only issue is arranging data entering form with nominal time consumption tactics.


Thank you once again.

 

[wikihow]

So need to know if a Unit of a give type ALWAYS has the same room configuration. If so then the room configuration is related to the type. But it has to 100% of the time. If it can be configurable then you either need a new type or the configuration has to be related to the Unit even if most of the time they all have the same configuration. In other words can a Type A unit be slightly different than another Type A unit.

The next thing to decide is do you need to save the individual rooms or just the Configuration. If there is a small set of potential room configurations then I would have a table called RoomConfigurations like the list shown and similar to the table Rooms I posted. So instead of saving child records for each room for a unit you just need to save the configuration ID.
so Config 1 (using the image is)
2 Bedrooms
1 Linen
1 Bathroom
1 Ensuite

The next question is also important. Are the components (Robes, Mirrors) ALWAYS the same. In other words will Bedroom 1 of Type A always get 1 robe.

The big difference here is not so much the amount of data to enter, but how much is stored. To explain. If you said a Type A unit ALWAYS gets X configuration, I would not have to select a room configuration because all Type A rooms get the same configuration. I just relate through the type. If sometimes it changes a little or you need to apply details to the specific room, then when I create a unit of type A it would run an insert query based on the standard type A configuration. Now instead of a pointer you have a record for each room type. This way you can add, edit, delete items.

Do you have excel data? That may help to understand the level of detail needed and the needed flexibility.

Thanks for the reply.

If a room is Type A, then all type A rooms will be identical. Same number of bedrooms, bathrooms etc, and same number of furniture inside, same size ditto. This is why grouping is required, since all units are same, I need to findout a way to take advantage of it and reduce time consumption. Entering 200 records for 200 apartments, or just entering all those records in some unique table structure or just any code to replicate all those records in easy way. Any such method is important.


Room can be grouped according to types, but need to keep individual rooms in track since some of them can be on different floors. Ground level unit can be type A ,,, or second floor unit can also be type A same time. so I should be able to work on individual reports too.

I can work with anything including the combined configuration code as well as long as it suits the purpose of easily entering data into system, and get all the details out from system.

This is basically for estimation purpose and so we can keep the records at one place. We used to do estimate on excel previously and i am attaching an example of what we used to do on the excel.

 

[wikihow]

Thanks for the reply.

If a room is Type A, then all type A rooms will be identical. Same number of bedrooms, bathrooms etc, and same number of furniture inside, same size ditto. This is why grouping is required, since all units are same, I need to findout a way to take advantage of it and reduce time consumption. Entering 200 records for 200 apartments, or just entering all those records in some unique table structure or just any code to replicate all those records in easy way. Any such method is important.


Room can be grouped according to types, but need to keep individual rooms in track since some of them can be on different floors. Ground level unit can be type A ,,, or second floor unit can also be type A same time. so I should be able to work on individual reports too.

I can work with anything including the combined configuration code as well as long as it suits the purpose of easily entering data into system, and get all the details out from system.

This is basically for estimation purpose and so we can keep the records at one place. We used to do estimate on excel previously and i am attaching an example of what we used to do on the excel.

Correction: It should be Apartment and not just the room. If an apartment unit type is A, then all Type A apartment units will be identical, no change. 100% same. If there is any difference, we can change its Type to Type A1 before adding data in database. In same type Apartments, everything will be same, Room numbers, room layout, furniture etc.

And yes, Different room of an apartment to be mentioned in Database. Bedroom 1, Bedroom 2 ... bathroom, Ensuite etc...


Any item wardrobe, mirror etc, if not in same type of apartment, Then it can be different in size or quantity. all type A will have same size and quantity of items, but Type B can have different size or quantity. Some can even have wardrobe, and other type may not have wardrobe, but just mirror etc.

 

[MajP]

If I understand everything correctly, and all rules hold then if you properly normalize this to meet your needs you should have very little data entry.

This is my thought, but you will have to verify if it meets your needs.

I renamed table rooms to tblRoomConfigurations to be a little more descriptive. If an apartment gets the same room configuration for all rooms then

If an apartment unit type is A, then all Type A apartment units will be identical, no change.

tblRoomConfiguration relates back to UnitType and a Unit relates to a UnitType. So the table tblRoomConfiguration looks like this


tblRoomConfigurations


So this is the configuration fo

RoomID	UnitTypeID_FK	RoomName	Room_Quantity
1​	1​	Bedroom 1	1​
2​	1​	Bedroom 2	1​
3​	1​	Bathroom 1	1​
7​	1​	Bathroom 2	1​
8​	1​	Ensuite	1​

so this is the configuration for 1 (type A). Since each room has different components you need to enter each room. Now I put a quantity field, and that was because you mentioned windows and doors. I do not think you want to enter 12 different windows here since I doubt windows have seperate components. But if you have to add components to individual windows then you will need 12 records. If not add Windows with quanity of 12.

If I understand correctly if room gets components which are mirrors, dressers, linen cabinets. So you need your component table
Something like
tblComponents

ComponentID	ComponentName	ComponentCost
1​	Wardrobe 1130	$100.00​
2​	Wardrobe 1390	$200.00​
3​	Wardrobe 1465	$300.00​
4​	Mirror xxx	$100.00​
5​	Mirror yyy	$200.00​
6​	Mirror zzz	$400.00​

I see lots of different sizes and types of wardrobes and mirrors and assume they have different costs. So you need to enter all the types. There may be more fields in this table. Probably at least need a ComponentType (Mirror, Wardrobe), and maybe dimensions.

Now you need a junction table. To assign components to Rooms. When I say room it is a room in a unit type. So room configuration 1 is the components in all bedroom 1 of unit type A. Which I assume is different from the components in bedroom 1 in unit type B,C.....

tbl_Rooms_Components

ID	RoomID_FK	ComponentID_FK	Component_Quantity
1​	1​	1​	1​
2​	1​	4​	2​
3​	2​	2​	1​
4​	2​	5​	3​

Your junction table looks like this.
Room configuration 1 has components 1, 4. 2 has 2,5

In a query putting it together
qryData

UnitName	UnitType	RoomName	ComponentName	ComponentCost	Component_Quantity	TotalCost
G101	Type A	Bedroom 1	Mirror xxx	$100.00​	2​	$200.00​
G101	Type A	Bedroom 1	Wardrobe 1130	$100.00​	1​	$100.00​
G101	Type A	Bedroom 2	Mirror yyy	$200.00​	3​	$600.00​
G101	Type A	Bedroom 2	Wardrobe 1390	$200.00​	1​	$200.00​
G102	Type A	Bedroom 1	Mirror xxx	$100.00​	2​	$200.00​
G102	Type A	Bedroom 1	Wardrobe 1130	$100.00​	1​	$100.00​
G102	Type A	Bedroom 2	Mirror yyy	$200.00​	3​	$600.00​
G102	Type A	Bedroom 2	Wardrobe 1390	$200.00​	1​	$200.00​

Any future unit of type A gets all of this information.
Does this make sense, for what you are thinking?
If so the only tedious part is populating the initial reference tables. You probably want a nice form to make this easier. However, you populate that once and you are done. Adding future data is simply assiging a unit to a unit type.

 

[wikihow]

Thanks MajP, Can you post the relationship window screenshot? I want to know, how you have related all tables currently. Have you made relationship of UnitTypes to both Units and tblroomconfiguration both?

If possible, can you attach your copy of DB?

 

[MajP]

Here is the DB. To demo the concept. Go to the form and change the Unit Type to A (Only thing with data at this time). Or create a new unit of type A.

 

[wikihow]

Thank you. I will check it with entering more data and checkout what results I get. The query shown in previous post was looking accurate of what is needed. I will just check it with more data rows. Will get back to you asap.