Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17

    Architects room data sheets

    Hi, i think I'm starting to get to grips with access but I'm stuck or possibly do something the wrong way.

    I have produced all my tables for my room data sheets (i only have 2, one for general room information which contains unique text or yes/no check boxes and the second for equipment which will be within each room) but i am now a little stuck with the room information input form (my main form which will allow users to input the data for each room and add or delete existing rooms).

    Within my room information input form i have the room number and name at the top and have tabbed pages for each section of the room data sheets, piped services, wastes, power and comms...etc. I haven't had any problems setting these up so far and all seem to work fine.

    I now need to add the relevant equipment which will be within each room. I have created a separate table for each piece of equipment with the relevant information eg name, manufacturer, size, required services...etc. I would like a tabbed page within my room information input form which produces a drop down list where i can select a piece of equipment (from my equipment table) and type in the quantity of that piece of equipment within the room, once i have selected a piece of equipment i want it to produce a second drop down list where i could add a second or third if required.



    Currently when i insert the table it does just that and gives me a list of all the items within my equipment table. This tells me i should not be importing the table but I'm not sure what i need to produce (table, query,form)

    Any help would be greatly appreciated.

    And apologies for confusion in my explanation and any errors in nomenclature.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not following some of the details. However, your form seems quite complex - nothing wrong with that, but just emphasizing the fact. I'm sure you understand that Tables store data. Forms (no matter how simple or complex) are a means of displaying/portraying data to the user.

    It sounds to me (and it may be obvious to others) that you have a Room and a List of all possible equipment. In order to save the equipment that goes in a specific Room, you would have a junction table that associates RoomId and EquipmentId. And have a compound unique index on this combination of RoomId +EquipmentId.

    Perhaps you could confirm/adjust this comment, and post a picture of your Tables and Relationships.

    Good luck with your project.

  3. #3
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17
    orange, thanks for the speedy post and again appologies for any confusion, I havent used access for nearly 10 years back when I was at school.

    Am I doing wrong by creating a form for users to input data in the first place? Is there a better way? The other users will not have had any previous access experience. I was thinking the form would give users a single page to input all the data without having to even touch the tables or creating more forms. This would then be linked to a report for printing.

    I have attached a couple of images of the form and tables i'm working on.

    Click image for larger version. 

Name:	Table Room.jpg 
Views:	16 
Size:	53.2 KB 
ID:	9137Click image for larger version. 

Name:	Table Equipment.jpg 
Views:	15 
Size:	76.7 KB 
ID:	9138Click image for larger version. 

Name:	Form 1st Page.jpg 
Views:	17 
Size:	87.4 KB 
ID:	9139Click image for larger version. 

Name:	Form 2nd Page.jpg 
Views:	15 
Size:	90.6 KB 
ID:	9140

    By junction table do you mean relationship?

    Thanks

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The Form is the way to display data from tables or collect info from the user. Users should never have direct access to tables. It appears you are spending most of your time creating form(s) for data collection and entry. But have you considered exactly where you are going to store that data?

    I would suggest you put some focus on creating a data model. This will help you understand the "things" involved in your business and how those things relate.
    Once you have your tables and relationships designed, it will facilitate creation of forms, queries and reports.

    You may wish to research Normalization and Entity Relationship Diagramming. The first few topics here have good explanations of these subjects.

    http://www.rogersaccesslibrary.com/forum/topic238.html

    junction table info at http://www.foresightsoftware.com/Acc...tionTables.htm

    There are many free data models at http://www.databaseanswers.org


  5. #5
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17
    I have quickly mocked up a very rough data model to chat over. Do i need a table for each section of the room data sheets as attached? The information i need to capture would be as the the attached, general room information and the services within the rooms and then quipment. As the services are mainly yes/no entries do they need to be in a separate table? I am probably completely wrong but it seems to be addaing additional complexity to a group of information that will be unique to each room? Creating a separate table for say piped services will yeild X amount of entries as there are combinations of yes/no answers for all fields in that table.Data Model.pdf

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you select a room and just in English tell me what "information" your form would collect to be stored in tables?

    Lets just call it Room100 and see what evolves.

  7. #7
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17
    Thanks and sorry.

    Ok, my room is room number 100 and it is a kitchenette.

    My form would allow me to input a room number, room name and a brief description of the room (all unique to this one room, these 3 bits of information will NEVER be duplicated in THESE fields again).

    I would then populate the department/classification for each room, this will be one of around half a dozen possible selections eg office, circulation, storage...etc. I would then start to populate the required physical parameters of the room. Take piped services for a kitchenette I would probably need cold, hot and potable water supplies. I would see the choice of these services as being a yes/no tick box. Any piped service not required in my kitchenette but that might be required elsewhere in the building would appear on my schedule but be left un-checked.

    I would then start to populate the requirements for lighting. I would want natural lighting so I would check the natural lighting box. Then I would start to define the lighting level required by inputting the required lux level into the luminance box, say around 300 lux for a kitchenette (this could range in value from around 300 - 1000 depending on the room).

    I would then repeat these actions for the other aspects of the room wastes services, heating and cooling. All these would require similar a similar input method.

    I would then assign furniture to the room. My kitchen has a worktop, the worktop is 2.4m long, 600mm deep and 900m high. The worktop has a sink and microwave on it (I think I have this table done already).

    I would them start over again with room number 101 an office...

    I hope this helps and thank you for your patience and help.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ok, without thinking specifically of a form and
    start to populate the required physical parameters of the room and start to populate the required physical parameters of the room
    . This sort of is the HOW. To build the data model we have to think WHAT - WHAT are the things (Entities) I am dealing with).

    We are talking about ROOMS. Each ROOM has a unique identifier which distinguishes it from every other ROOM. Each ROOM has a RoomNumber, a RoomName and a RoomDescription (we'll investigate this one later). Since the RoomNumber is used by people, I recommend you create a key called ROOMId which will be assigned by Access and used for system purposes. The RoomId will be associated with RoomNumber. People will often refer to the RoomName or RoomNumber, but Access will use RoomId.
    Each ROOM is assigned 1 or more CLASSIFICATIONs from a CLASSIFICATION LIST which contains things like office, circulation, storage (you make a "lookup Table for these values. The Lookup Table would contain a unique ClassId and a ClassName. For example, ClassId is used by the system to identify the ClassName. The table would contain values such as 1 Office; 2 Circulation....etc. If you want to add a new Classification, you simply add a new record to this LookUpTable. Your reference to Physical parameters PipedServices, which sounds like a series of attributes that you have in a List. I suggest this would become a LookupTable of some sort with a structure similar to the other Lookup table. PipedServiceId, PipedServiceName with values eg 1 - cold potable water, 2 hot potable water,....all the pipedServices you have and you can add new one when/if required.
    Each ROOM has 1 or more LIGHTING requirements/attributes --- and another LookUP Table specific to LightingAttributes.... Same general approach to WASTEServices...HeatingAndCoolingServices, same for Furniture A lookup Table defining/describing Furniture that is within your project scope with a FurnitureId, FurnitureName and possibly other features specific to Furniture.

    I think you may be missing some major Entities such as Building, Floor.. and for individual rooms you may want their physical dimensions 3m X5m X2.5m????

    These are the WHAT things you are dealing with. How you populate the tables representing these things can be Form, paper collection mechanism...can be a number of different methods.

    Anyway, that is just to show one person's view of possible tables (someone who knows nothing of your room or environment --just listening and visualizing the data involved.

    Any comments?

  9. #9
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17
    Orange,

    Thanks, it's starting to make so much more sense with your help.

    I am still stuck on one thing.


    Quote Originally Posted by orange View Post
    PipedServiceId, PipedServiceName with values eg 1 - cold potable water, 2 hot potable water,....

    The piped services will include pressure values which will vary from room to room. Does this mean I will need a unique rows for each value or can this be an unassociated value/note only entered on the input form? And displayed in the report?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are talking about attribute of a ROOM primarily.
    I saw things like

    1 - cold water potable
    2 - hot water potable
    3 - cold water not potable dealing with water, but you are dealing with terminology (and possibly jargon associated with a professional group)

    that may be a slightly different concept than PipedService.

    Maybe if you list all the things a pipedService could involve we could see or concoct some pattern.

    I don't know your general background, but if you scientific in some sense, think of a taxonomy (classification system). There is room within the structure to accommodate most anything -- and that may be what you're facing.

    Typing as I'm thinking

    PipedService
    - Water
    - Potablity
    -potable
    -non potable
    - Salinity
    - Chemistry
    - Pressure
    - Opacity
    I'm not sure if that's where you want to go--but I don't think so.

    If you have a set of terms that makes sense to your project ( and anything you interact with) then you may want to devise some structure to meet your needs.

  11. #11
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17
    Thanks for all the advice, ive had my head down working away for a couple of days and am pretty happy where i am.

    I am now trying to allocate equipment to rooms. I have a table with:

    tblEquipmentCatalogue
    EquipmentCode (KEY)
    ConstructionIdustryCode
    PowerRating
    Width
    Depth
    Height

    I am trying to form a relationship with each room which says

    RoomNumber 001
    "EquipmentCode#1 Quantity 10"
    "EquipmentCode#2 Quantity 3"
    "EquipmentCode#3 Quantity 5"

    RoomNumber 002
    "EquipmentCode#4 Quantity 1"

    etc...

    I just cant see how to even start, reading through some guides whcih touch on similar subjects makes some sense but when it actually comes to doing mine i get a form of writters block.

    ps you will be glad to know the access bokk i ordered should be arriving later this week, hopefully reduce my posts asking for help.

  12. #12
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17

    progress

    I think i am making progress.

    I now have EquipmentRegister table which lists each individual piece of equipment, my EquipmentRegister table whcih stores information relating to which room has which piece of equipment and how many. I also have my EquipmentCatalogueInput form whcih allows me to input which room has which equipment.

    I now have the problem when i open up my form to input the equipment to a room i cannot see the previous input information (see atached)

    slide 1 - i input the equipment and quantity to a room via my EquipmentCatalogueInput form

    slide 2 - the equipment is logged to the room in my EquipmentRegister table

    slide 3 - if i return to my EquipmentCatalogueInput form the previous data is no long displayed.

    it would be very helpful if the equipment and the quantities i had previously input would remain when i open my EquipmentCatalogueInput form. It would also be helpful if once i have input the equipment type and quantity a second combo box opened up then a third once that has been populated and so on.

    Any help would be greatly appeciated.

    Click image for larger version. 

Name:	Slide 1.jpg 
Views:	14 
Size:	64.6 KB 
ID:	9202 Click image for larger version. 

Name:	Slide 2.jpg 
Views:	14 
Size:	43.3 KB 
ID:	9203 Click image for larger version. 

Name:	Slide 3.jpg 
Views:	14 
Size:	70.6 KB 
ID:	9204

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you adjusted/refined your data model?

  14. #14
    ThatSkepTicGuy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2012
    Posts
    17
    Do you mean generally or specific to my equipment scheduling? I have refined to a point to where it all seems to work, as to weather its the right way (if there is only one right way to do it) im not sure. I have been hand drawing data models to help me get my head around the whole process.

    Ive been doing the tutorials you linked me and others to which are helping.

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you gone thru the ER Diagramming on this site?

    http://www.rogersaccesslibrary.com/forum/topic238.html

    You could also look at the 12 steps topic as well.
    I don't usually get into Forms until I have some sort of model that works with some concocted test data. There really isn't just one way to do most anything. It's a certain comfort with an approach based on some experiences.
    You can't have the absolute perfect model before you start, and you can't have all perfect forms without a model, so it's a bit of trial and error. Or, perhaps a little analysis. a little design, some testing/prototyping, refinement and repeat the loop.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Architects Room Data Sheets
    By ThatSkepTicGuy in forum Database Design
    Replies: 3
    Last Post: 09-10-2012, 10:37 AM
  2. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  3. How to get a room linked to a resident staying in it
    By Chelcone in forum Database Design
    Replies: 13
    Last Post: 05-18-2012, 09:28 AM
  4. Template for a Discussion Forum or Chat Room
    By Whizbang in forum Access
    Replies: 4
    Last Post: 05-18-2012, 06:52 AM
  5. Need help with room update query.
    By DevilMaster18 in forum Queries
    Replies: 3
    Last Post: 04-30-2010, 02:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums