Results 1 to 14 of 14
  1. #1
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24

    How to get a room linked to a resident staying in it


    Hi Everyone

    Im having a slight issue with exactly how im going to approach this so i thought i would throw it out and see if people have ideas.

    Im building a system for a care home, they have rooms that have certain attributes like being en-suite or being a double or single room. With that in mind i made a table (tbl_rooms) with the fields that i needed. Now im tring to get a way of allowing the user to choose what room goes with what resident. So i created a subform, put a relationship in as tbl_resident.RiesidentID to tbl_rooms.residentnumber and then thought that if the user chooses the room number from the drop down list (tbl_rooms.RoomNumber) then the rest of the information would populate. However its actually creating a new record. (Hope this makes sence :S)

    How would you design this to work?

    Thanks in advance

    Chelcone

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I would NOT start with forms and subforms. I would build a data model (tables and relationships).

    I'm not sure who a user is
    "if the user chooses..."
    Seems you have 2 main entities

    Rooms and Residents(People)

    In order to assign a Resident to a Room , you need a junction table to identify the Resident_Room_Assignment at any given time.

    So, your tables would look something like

    Room(RoomId, RoomNumber, RoomType, other fields specific to the Room)

    Resident(ResidentId, ResidentName, other field specific to the Resident)

    Resident_Room_Assignment(AssignmentId,ResidentId, RoomId, AssignmentDate,other fields specific to this Resident_Room Assignment)

    Underlined fields represent the PK.
    Fields in Green represent a compound unique Index (no duplicates allowed)
    The green ResidentId is a FK to Resident
    The green RoomId is a FK to Room

    I would identify a list of facts/rules and create some test data, and then proceed to some queries and forms to ensure the structure supported what I was trying to do.
    And adjust as necessary if any issues arose.

  3. #3
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    Thanks for the reply

    By the User i mean the end user when its done.

    Never done a junction table before, I have made several databases but im more self taught than anything so not sure how i would go about making a junction table, im guessing that i would use append or update querys to pull data from the room and resident tables onto the room assignment table? Then use the room assignment table as the record source for the place people would view it?

    Thanks for the help

  4. #4
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    The junction thing is great but it is alot to take in for self tought programmers.
    Personally i would make two tables (tblRooms and tblResidents) and connect them using a Primairy key like ResidentID and RoomID by dlookup. These ID fields are both unique and thus it is fairly easy to lookup the data from one table to another.

    So i would have a form on wich i can look up a user, Fields would be ResidentID, Name, initials, age, RoomID etc etc
    Then you make fields on that form that covers everything you want to see. So Name, age, gender, etc etc but also the info on the room and connect this all by dlookup.

    This way you would have all the info of the occupant and all the info on the room itself together on one form.

    The info on the user would be bound fields. Theroom info i would dlookup like this

    me.fldRoomSort = Dlookup("RoomSort", "tblRooms", "RoomID = me.fldRoomID")
    me.fldRoomNumber = Dlookup("RoomNumber", "tblRooms", "RoomID = me.fldRoomID")

    Maybe not better coding then the join like Orange mentioned, but easier i think.

  5. #5
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    Both of these sounds like good ideas to me, im trying to look up some more info on both so i can understand them a bit better, my VB knowledge is there but limited and my SQL knowledge is about the same level. Im slightly more comfortable with SQL than VM and JeroenMioch your solution if im not mistaken is VB?

    Could you go into more detail RE: the Dlookup? As in what is a Dlookup, and what would you do to implement one? would it be coded into the VB script on the form? or into SQL through a query?

    Slight Noob syndrome here.

    Thank you

  6. #6
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Dlookup is really all that it does. It looks up values.
    You tell it where the value can be found dlookup("YOUR_FIELD_NAME", "YOUR_TABLE_NAME") and the last part is a where clause. If i dont tell the dlookup the where clause, it would return the first value of the field we specified.
    So, ill tell it ; dlookup("YOUR_FIELD_NAME", "YOUR_TABLE_NAME", "ID= me.FIELD_ID")
    Thus it knows the fieldname, it knows wich table it should look and im telling it wich record it should pick because i say "the ID field of the record is the same value as the one on my form (me.FIELD_ID)

    This is just an example of course, in your case the code would be more as i mentioned in my earlier post

    Oh its not SQL allthough the syntaxis is slightly similair. Its Visual Basic

  7. #7
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Orange is telling you the correct way to build your DB. If you're trying to learn DB design Learn it correctly. Read up on 3rd Normal form. Anything less will give you problems later.

  8. #8
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    After looking further into it I agree that Orange's solution will give me the most robust result, I looked into the 3rd normal form and that's a great reference! thank you! I will learn and absorb! I'm trying to find a good resource to work from to learn this, could you recommend one Ray?

  9. #9
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Best book I ever read was Database Design for Mere Mortals By Michael J. Hernandez. It does not directly address 3rd Normal Form as the definition is a little confusing. A database is in 3rd Normal form If and only if it is in 2nd normal form and every non-key attribute is nontransitively dependent on the primary Key. However in that book he derives his own definition which is "A table should have a fielld that uniquely identifies each of its records and each field in the table should describe the subject that the table represents." That is a little easier to understand and he goes into more detail throughout the book. My copy is 7 years old and well worn.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can't go wrong with Michael Hernandez.

    Also there is material here
    http://www.rogersaccesslibrary.com/forum/topic238.html

    Here are a number of free video tutorials related to database design concepts.

    These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

    http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

  11. #11
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    I had a bit of a play last night and managed to get the idea of this by making a very small test database (as suggested by you guys)

    I made three tables; tblCustomer, tblItems, tblOrder

    PK's tblCustomer.CustomerID, tblItems.ItemID, tblOrder.OrderID

    Set one to many relationships on the tblOrder from the other two tables and then managed to get a query to pull of the details from all three tables into one query. I think this is defiantly the way i need to go!!

    Thank you for all the resources and all the information! You guys were so helpful! im getting there now and im sure i'll figure it out in the end now haha.

    Thanks again!

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The Customer, Order, OrderItems annd Items is the theme of the sample in the videos. I hope they were helpful.

    We'd still like to see your data model as you devlop your Residents and Rooms application.

  13. #13
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    Ok i think im getting how im going to do it. So i'll keep you updated!

    One question, the rooms i would like to be able to select the room number and then have all other details auto populate, also would like to prevent additions to the table, what would be the best way of structuring that. My plan so far is this:

    tblResident (PK RedidentID) tblRoom (PK RoomID) tblFee (PK FeeID) tblBilling (PK BillID) tblBilling will then be the Junction table puling the details together so i can get the resident, what room there in, and how much they are paying, then using the details i can produce the invoices and so on.

    So if i wanted tblRoom to essentially be a look up that dumps all the room details onto the form when you select the room number from a drop down list, with no other Resident able to use that room once it has been taken what would be the best structure to achieve this?

  14. #14
    Chelcone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24
    Wait!! I Think i cracked it!!!
    Click image for larger version. 

Name:	relatioships.PNG 
Views:	29 
Size:	32.2 KB 
ID:	7705
    Using this relationship setup I can use a query to access data from any of the tables, to populate the room details I would get them to look up the ID of the room and then it will pull the other details out when the relationship is made?

    Am i on the right lines there?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Template for a Discussion Forum or Chat Room
    By Whizbang in forum Access
    Replies: 4
    Last Post: 05-18-2012, 06:52 AM
  2. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  3. linked fields
    By blue4512 in forum Forms
    Replies: 3
    Last Post: 06-09-2011, 05:05 PM
  4. Staying on Same Record
    By mwabbe in forum Access
    Replies: 12
    Last Post: 09-02-2010, 08:28 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