I have no table 1. Was planning on have a new database file for each project. so only have table 2. And maybe not hundredS of fields per room, but certainly 100.
newbie want to get basics right
- Thread starter littleme
- Start date
I have no table 1. Was planning on have a new database file for each project. so only have table 2. And maybe not hundredS of fields per room, but certainly 100.
Excellent start!
I see from your Database, you have already broken the Room Table into logical tables. That will make the creation of forms simple. The report as well will be pretty easy using the query that joins the tables (the query is your one big Room table). The management of many databases from a folder seems extreme. Having a project table and project id as a component of the Room tables allows Access to manage the projects, and you will find the form/subform combination (Form has project/Subform has Room) again relatively easy to use. I really believe you can do all you want without using any program code.
If you have more questions...feel free to ask.
Smiles
Bob
PS: Use Command Buttons with Form Navigation to Open forms, close forms, etc. They are very useful.
I see from your Database, you have already broken the Room Table into logical tables. That will make the creation of forms simple. The report as well will be pretty easy using the query that joins the tables (the query is your one big Room table). The management of many databases from a folder seems extreme. Having a project table and project id as a component of the Room tables allows Access to manage the projects, and you will find the form/subform combination (Form has project/Subform has Room) again relatively easy to use. I really believe you can do all you want without using any program code.
If you have more questions...feel free to ask.
Smiles
Bob
PS: Use Command Buttons with Form Navigation to Open forms, close forms, etc. They are very useful.
Last edited:
- Local time
- Today, 19:41
- Joined
- Sep 12, 2006
- Messages
- 16,024
why necesarily tonnes of fields per room
surely there cannot be more than 20-30 characteristics to totally describe a room?
what are the main characteristics of a room
eg
doors
windows
electric sockets
data sockets
aircon units
eg dont store door details separately IN the room.
have another table for doors LINKED to the room. Then each door shares the same characteristics - eg doortype, size, architrave, furniture style, lock y/n, - indeed some of these may be associated with the doortype
so you have
1 building - many rooms
1 room - many doors (although there may only be one door)
1 room - many windows
1 room - many power sockets
etc etc
so you have a room table, then you have a doors table, linked to the room, a windows table linked to the room, a sockets table linked to the room.
What other things are you considering as storing as room attributes? Its a bit of a tautology but normalisation requires that only things that are functionally dependent on the room should be stored as attributes of the room. eg power socket ratings, single/double gangs, socket finish etc, are not dependent on the room, but are dependent on the socket.
-------
Overall this seems to me a VERY complex project, requiring significant understanding and analysis. For example the above may well not be the correct way of considering a building. Buildings tend to have a consistent style for all internal doors, external windows etc, It may be that it is appropriate to have a data structure that stores the window/door STYLE linked to ther building not the room. That way, if a client decides he wants a different style of window throughout eg brown frames instead of white, you can change the whole building style with one modification, rather than having to consider and process every room individually.
surely there cannot be more than 20-30 characteristics to totally describe a room?
what are the main characteristics of a room
eg
doors
windows
electric sockets
data sockets
aircon units
eg dont store door details separately IN the room.
have another table for doors LINKED to the room. Then each door shares the same characteristics - eg doortype, size, architrave, furniture style, lock y/n, - indeed some of these may be associated with the doortype
so you have
1 building - many rooms
1 room - many doors (although there may only be one door)
1 room - many windows
1 room - many power sockets
etc etc
so you have a room table, then you have a doors table, linked to the room, a windows table linked to the room, a sockets table linked to the room.
What other things are you considering as storing as room attributes? Its a bit of a tautology but normalisation requires that only things that are functionally dependent on the room should be stored as attributes of the room. eg power socket ratings, single/double gangs, socket finish etc, are not dependent on the room, but are dependent on the socket.
-------
Overall this seems to me a VERY complex project, requiring significant understanding and analysis. For example the above may well not be the correct way of considering a building. Buildings tend to have a consistent style for all internal doors, external windows etc, It may be that it is appropriate to have a data structure that stores the window/door STYLE linked to ther building not the room. That way, if a client decides he wants a different style of window throughout eg brown frames instead of white, you can change the whole building style with one modification, rather than having to consider and process every room individually.
I see where youre going, but what you're desribing is not really how we work. I agree with you that there will be certain Styles (be it doors, windows or entire rooms) that can be defined "outside" of the main table. Thats my next step. We work alot with predefined national styles already; thats what my Code-fields are/ will be. They refer to an externally defined description.
However, we design alot of hospitals and your be surprised att the number of limitations, requirements and requests there are. It is to compile the functions and requirements of each room that we need this database so that we have something to base the design on.
With that in mind, do you think you might have time to take a look at my database. Im still very unsure if I should split the room info into tables based on category, (what then happens when a room gets deleted) or to make on table. I cant really grasp the consequences...
However, we design alot of hospitals and your be surprised att the number of limitations, requirements and requests there are. It is to compile the functions and requirements of each room that we need this database so that we have something to base the design on.
With that in mind, do you think you might have time to take a look at my database. Im still very unsure if I should split the room info into tables based on category, (what then happens when a room gets deleted) or to make on table. I cant really grasp the consequences...
Last edited:
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 11:41
- Joined
- Dec 21, 2005
- Messages
- 1,582
I think a little more description of your real world situation would help enormously.
Let's say you have a table called 'tblRooms'
This table should have one record for each room. And each record/room needs an identifier that allows the database to know exactly what room you're referring to. This is called a primary key field.
You might think something like 'Dining Room' might be a good candidate for a primary key. But what if you're designing a mansion with 3 Dining Rooms? Obviously this won't work to uniquely identify the specific dining room you're talking about.
The best solution is to use a meaningless number that is automatically generated by the database to fill this role. These are called autonumber fields in Access.
So, your table so far is
Ok, now, there a variety of information about this room you might want to store. Some of that information will only exist once for each room. Some of it might exist several times for each room.
For example, you might want to store the room's intended function, like 'Dining Room', or 'Bed Room'. If your real world rules state a room can only ever have one 'function', then you'd want to store this information in a field in tblRooms. (Actually, you'd probably store a link to another table that stores Functions with one function per record and an autonumber pk). Another example field for this would be the square footage of a room. It can only ever be one value. So that would undoubtedly be a field in tblRooms.
ie.
On the other hand, if you want the capacity to say, room x serves a function as a dining room AND a living room AND a kitchen....then you have a one to many relationship between room and functions. THAT calls for another table, which stores one record for each function that a given room serves, and along with it a reference field to tell the db which room this function is associated with.
So,
ie.
Ok, so you can probably take that approach with every bit of information about a room. determine if it's one to one, or a potential one to many situation and either set up new tables, or add extra fields to tblRooms.
This may lead to an explosion in the number of tables you need. For example, furniture items? Could be many in a room so you'll likely need a table of Furniture, and another table to link multiple furniture items to a room. Similarly for electrical outlets (regular, gfci, 3 phase, etc). Some of these table that you might build to handle a one to many relationship might also benefit from an additional quantity field.
An alternative (second) approach would be to get a bit more abstract.
For example.
Now this simple but very abstract model essentially calls all of your field headings 'descriptors', and they all get added to tblDescriptors.
You can add as many descriptors to a room as you like, each with their own quantity, and you can always add more descriptors as you need them.
HOWEVER, if you take this approach, you would probably need to learn about crosstab queries to reassemble the information into anything like a spreadsheet format that you're likely more familiar with.
Personally, I think the former approach is the correct way to go; as it maximizes the power of a relational db for reporting and analysis. Unfortunately, it requires a very good analysis of the data model to separate out data entities that need to be placed in their own tables (one to many between room and <whatever>) versus those that are strictly one to one between room and <whatever>. and therefore can be stored as an extra field in tblRooms.
Let's say you have a table called 'tblRooms'
This table should have one record for each room. And each record/room needs an identifier that allows the database to know exactly what room you're referring to. This is called a primary key field.
You might think something like 'Dining Room' might be a good candidate for a primary key. But what if you're designing a mansion with 3 Dining Rooms? Obviously this won't work to uniquely identify the specific dining room you're talking about.
The best solution is to use a meaningless number that is automatically generated by the database to fill this role. These are called autonumber fields in Access.
So, your table so far is
Code:
tblRooms
RoomID (autonumber, pk)
ProjectID (fk)Ok, now, there a variety of information about this room you might want to store. Some of that information will only exist once for each room. Some of it might exist several times for each room.
For example, you might want to store the room's intended function, like 'Dining Room', or 'Bed Room'. If your real world rules state a room can only ever have one 'function', then you'd want to store this information in a field in tblRooms. (Actually, you'd probably store a link to another table that stores Functions with one function per record and an autonumber pk). Another example field for this would be the square footage of a room. It can only ever be one value. So that would undoubtedly be a field in tblRooms.
ie.
Code:
tblRooms
RoomID (auto, pk)
ProjectID (fk)
FunctionID (foreign key)
Room_SquareFeet (LongInt)
tblFunctions
FunctionID (auto, pk)
FunctionName (Text)On the other hand, if you want the capacity to say, room x serves a function as a dining room AND a living room AND a kitchen....then you have a one to many relationship between room and functions. THAT calls for another table, which stores one record for each function that a given room serves, and along with it a reference field to tell the db which room this function is associated with.
So,
ie.
Code:
tblRooms
RoomID (auto, pk)
ProjectID (fk)
Room_SquareFeet (LongInt)
tblRoomFunctions
RoomFunctionID (Auto, pk)
RoomID (fk)
FunctionID (fk)
tblFunctions
FunctionID (auto, pk)
FunctionName (Text)Ok, so you can probably take that approach with every bit of information about a room. determine if it's one to one, or a potential one to many situation and either set up new tables, or add extra fields to tblRooms.
This may lead to an explosion in the number of tables you need. For example, furniture items? Could be many in a room so you'll likely need a table of Furniture, and another table to link multiple furniture items to a room. Similarly for electrical outlets (regular, gfci, 3 phase, etc). Some of these table that you might build to handle a one to many relationship might also benefit from an additional quantity field.
An alternative (second) approach would be to get a bit more abstract.
For example.
Code:
tblRooms
RoomID (auto, pk)
ProjectID (fk)
Room_SquareFeet (LongInt)
tblDecriptors
DescriptorID (auto, pk)
DescriptionName
tblRoomDescriptors
RoomDescriptorID (auto, pk)
RoomID (fk)
DescriptorID (fk)
Quantity (integer)You can add as many descriptors to a room as you like, each with their own quantity, and you can always add more descriptors as you need them.
HOWEVER, if you take this approach, you would probably need to learn about crosstab queries to reassemble the information into anything like a spreadsheet format that you're likely more familiar with.
Personally, I think the former approach is the correct way to go; as it maximizes the power of a relational db for reporting and analysis. Unfortunately, it requires a very good analysis of the data model to separate out data entities that need to be placed in their own tables (one to many between room and <whatever>) versus those that are strictly one to one between room and <whatever>. and therefore can be stored as an extra field in tblRooms.
Hey guys, slow down and look at the audience here....you are giving advanced table design advice to a new non technical user who simply wants to get started with Access to simplify a horrendous task. We want to take baby steps.
So far, by breaking one huge table into logical groups, littleme has done a wonderful design to allow the input, update of data and printing of the document identified as the requirement.
Furniture at the bottom of the pdf, may require a furniture table with many occurances for a room...I agree, otherwise the tables defined so far in the attachment will allow for a) easy form design b) easy report design and c) future enhancements.
The keyword is easy! Let's get littleme happy with Access as a tool, then with the appropriate courses, the 1st step database can grow to accomodate the wonderful advise you have submitted (probably way over littleme's head at the moment).
So far, by breaking one huge table into logical groups, littleme has done a wonderful design to allow the input, update of data and printing of the document identified as the requirement.
Furniture at the bottom of the pdf, may require a furniture table with many occurances for a room...I agree, otherwise the tables defined so far in the attachment will allow for a) easy form design b) easy report design and c) future enhancements.
The keyword is easy! Let's get littleme happy with Access as a tool, then with the appropriate courses, the 1st step database can grow to accomodate the wonderful advise you have submitted (probably way over littleme's head at the moment).
Thank you for the input. A bit mind boggling at first sight but I hope I'll get it once I sit down with it.
>>I think a little more description of your real world situation would help enormously.
Ok: Each room has a unique room number. After having created a rough plan of a building, we sit down with the client/user adn establish exactly what they plan to use the room for and what their requirements will be: If its examination room they'll need examination lamp, desk, internet access, sterilasation equipment, 3 cupboards, socket in each corner, certain pascal and ventilation, and a sink and be painted off-white, quality 8.
Want to be able to go through room by room, go down the list and check if they need handicapp sink? no, regular sink? yes, Sound/ Noise? 25dB...
Nobody can remember all of this. So it has to be documented somehow so that we don't miss anything. And instead of looking through a word document (im not joking) looking for the mentioning of "sterilisation equipment," we need the information a bit easier at hand.
I dont know is this helps...
>>I think a little more description of your real world situation would help enormously.
Ok: Each room has a unique room number. After having created a rough plan of a building, we sit down with the client/user adn establish exactly what they plan to use the room for and what their requirements will be: If its examination room they'll need examination lamp, desk, internet access, sterilasation equipment, 3 cupboards, socket in each corner, certain pascal and ventilation, and a sink and be painted off-white, quality 8.
Want to be able to go through room by room, go down the list and check if they need handicapp sink? no, regular sink? yes, Sound/ Noise? 25dB...
Nobody can remember all of this. So it has to be documented somehow so that we don't miss anything. And instead of looking through a word document (im not joking) looking for the mentioning of "sterilisation equipment," we need the information a bit easier at hand.
I dont know is this helps...
Here is my idea on how to structure the tables and forms. Ventilation is an example that can be used for the other components (Gas, Electric, etc). I created a form called Room...open that form to see the handling of the tables.
Thank you all of you, but especially MagicMan. I think I finally get it! The penny has dropped....
I really appreciate all the help and admire the fact that you all took time and energy to help a complete stranger. A small step towards world peace.
I really appreciate all the help and admire the fact that you all took time and energy to help a complete stranger. A small step towards world peace.
You very welcome...and welcome to the forums.
By the way, that example was meant to stir the pot, get you thinking. As you can see with the Structure table, fields/elements became data items with the table. The same could be done with other fields. The Ventilation popup could be modified to work like the room screen with the structures, with combo boxes to select the ventilation requirements. Ventilation fields, could become data. With the fields as data, you have greater flexibility, since adding a new data record requires no form changes.
Smiles
Bob
By the way, that example was meant to stir the pot, get you thinking. As you can see with the Structure table, fields/elements became data items with the table. The same could be done with other fields. The Ventilation popup could be modified to work like the room screen with the structures, with combo boxes to select the ventilation requirements. Ventilation fields, could become data. With the fields as data, you have greater flexibility, since adding a new data record requires no form changes.
Smiles
Bob
There are many catchy ways to explain it, but I like demonstrations =).
This is a small segment of information I have from a DB you might find useful in some way, it's from a retail system.
TblItemMain
lItemID
lDepartmentID
sItemShort
sItemLong
cItemPrice
I use Hungarian notation in my fields, l = Long, s = string, c normally i was tought constant but here I use it for currency.
lDepartmentID is always a number, because I have another table where I have
TblDepartment
lDepartmentID
sDepartment
Now I have a 1 to many relationship from lDepartmentID in TblDepartment to lDepartmentID in TblItemMain.
1 Music
2 Video Games
3 Movies
A record in TblItemMain would look like
1 | 2 | X-COM: UFO Defense | Turn based strategy game, 5 Stars. | $9.99
2 | 1 | Murder City Devils - In Name and Blood | Seattle Rockers | $ 14.99
My quick and dirty rule is 1.) What do all my items have in common (these go in the main table, price, department, title, etc). All other unique information goes in a table built specific to the Department of the item, # of discs, Track Lengths, Studio, for TblMusic for example. If the main table has a field where a large number of records are sharing common information, like Departments, I make a table for Departments and give it a 1 to many. All unique information stays flexible, by that I mean it is not required to be anything other than what the field allows as it is not related to a table, Price, Title, Description, etc.
I'm self taught, it shows, I picked up everything by asking questions here and I hope this is a little bit helpful to you. I also hope that I'm not making anyone experienced reading this (especially those who have helped me, ::cough:: Gemma, Boblarson, Ruralguy ::cough:
facepalm themselves and going "No, no no no"
This is a small segment of information I have from a DB you might find useful in some way, it's from a retail system.
TblItemMain
lItemID
lDepartmentID
sItemShort
sItemLong
cItemPrice
I use Hungarian notation in my fields, l = Long, s = string, c normally i was tought constant but here I use it for currency.
lDepartmentID is always a number, because I have another table where I have
TblDepartment
lDepartmentID
sDepartment
Now I have a 1 to many relationship from lDepartmentID in TblDepartment to lDepartmentID in TblItemMain.
1 Music
2 Video Games
3 Movies
A record in TblItemMain would look like
1 | 2 | X-COM: UFO Defense | Turn based strategy game, 5 Stars. | $9.99
2 | 1 | Murder City Devils - In Name and Blood | Seattle Rockers | $ 14.99
My quick and dirty rule is 1.) What do all my items have in common (these go in the main table, price, department, title, etc). All other unique information goes in a table built specific to the Department of the item, # of discs, Track Lengths, Studio, for TblMusic for example. If the main table has a field where a large number of records are sharing common information, like Departments, I make a table for Departments and give it a 1 to many. All unique information stays flexible, by that I mean it is not required to be anything other than what the field allows as it is not related to a table, Price, Title, Description, etc.
I'm self taught, it shows, I picked up everything by asking questions here and I hope this is a little bit helpful to you. I also hope that I'm not making anyone experienced reading this (especially those who have helped me, ::cough:: Gemma, Boblarson, Ruralguy ::cough:
facepalm themselves and going "No, no no no"Similar threads
