Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2015
    Posts
    20

    Unanswered: problems with many to many relationships

    I'm new to access and trying to build a table that stores our customers info as well as the buildings/rooms that they're the POC for. Plus info about our comm equipment in the rooms.

    I have a many to many relationship between customers and buildings table. I use a main form for customers info and a subform for building name, which is unique.

    I have a rooms table which I link to buildings using a one to many relationship. I'm having several problems trying to get it to work.

    First off I have many different rooms with the same name that are associated to different buildings. If I have a field in rooms for a room name I'd have to have duplicate entries and that would make it confusing for my users. I have no idea how to avoid this.

    Secondly I've tried to have building name and room name in the same subform, but as its on a different table I can't seem to get it to work. I need to be able to select the room name and have the building id table inserted into the building id field in the rooms table.


    I really appreciate the help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Many tomany relationships in a RDBMS are usually implemented as two (or more) parent tables, and an intersection table which contains the specifics of that intersection. Sometimes thats just the primary keys of the 'parent' tables, but it can also include any data relevant to that intersection.

    from what you have said so far id expect to see a table for:-
    Buildings
    Rooms (with a FĶ to buildings identifying what room is in what building)
    Customers
    (Possibly) types of equipment
    Models of equipment

    ...then an intersection table that identifies what equipment is in what room.

    Where you have the FK to the customer is down to you. It could be at the building level, so selecting a customer, provides a list if buildings in a combo or list box, which in turn provides a list of rooms for that customer in that building
    Or the customer could be an ekement of the rooms data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2015
    Posts
    20
    Thank you for your reply.

    I have my tables and relationships set up much like that. I'm having some problems creating a form for my buildings and rooms.


    What I'm trying to do is have a main form that contains customer info, and a subform that contains both building and room info.

    I really want to have my building and room info in the same datasheet. I tried making a main form for buildings and subform for rooms and pitting them both as a subform inside my main customers form, but it was giving me errors about duplicate data in indexed fields. Not sure why.

    But the other reason I want building to be in a datasheet next to rooms is because I need to be able to search for a customer and see how many buildings and rooms they're a POC for. It is usually more than one.


    So I need to figure out how to put that all on one form. Right now I have a table for customers, a junction table, a buildings table, and a rooms table. Buildings table only contains one field and is a text primary key.

  4. #4
    Join Date
    Aug 2015
    Posts
    20
    I am aware I can do this with combo boxes. But I do not know how to create a combo box that doesn't have duplicate data.

    If I used a combo box in a datasheet, building name would be on the far left. Say I selected building 100. On the rooms field to the right, I should have a combo box that only shows rooms in building 100. Also as I said before a lot of rooms have the same name in different buildings, but they are different rooms.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Read up on cascading combo / list boxes
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2015
    Posts
    20
    Thanks. I've been doing that.

    I found out one of the reasons it wasn't working is my relationships were wrong. I had a many to many between customers and buildings and a one to many between buildings and room. I didn't realize it but if a room was owned by a building, and that building was owned by a customer, then due to the one to many relationship any customer who owns a building also owns all of the rooms in that building.

    Thus the customer ID needs to be linked to the room id, not building.

  7. #7
    Join Date
    Aug 2015
    Posts
    20
    I do have one other question.

    I basically have three main types of data here. Customer info, building/room info, and equipment info.

    I'm working on a main form for customer and two subforms for building/room and equipment info.

    The problem here is that I can easily search for a customers name if I wanted to find a customer, and then I could see all the buildings that he/she owns and the equipment. But I wouldn't be able to easily search by room or equipment without creating entirely separate queries and forms.


    How can I have this all on one form?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In access you are better off using one form per function, and that usually means one form per table or query. Multiple dependent / relate tables are often modeled asssub forms embedded in parent forms, somtimes on tabs.

    Exceptions to this are search / enquiry forms
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •