Access form problems with many to many relationship

[ittechguy]

I've been trying to create a database that stores customer information, building/room information, and equipment (which is inside the rooms) information. I want a main form which contains customer information and two subforms, one for building/room and one for equipment.

From day 1 I knew this was a many to many relationship so I created a relationship between customers and a table called Rooms. Rooms contained building number as a field and room number as a field. However when I created the subform and made a combobox for building number, it was showing duplicate values because building name was not unique in my rooms table. So I could select a building name from the combo box, but access had no idea which primary key ID that went to and thus, what room and other information was associated with it.

To fix this issue, I put building in a separate table, like this:

I thought this would work, but then I discovered that because customer Bob owns building 1 and building 1 is assigned to room 1. If room 2 is also assigned to building 1, by default, bob also owns room 2. That is not the case. One customer could own one building/room or many. And one building/room may have many customers. So, I redesigned my database relationship to look like this:

I thought this would work, but I was having problems getting my subform to work properly. It was configured as a datasheet with building first, then room number, information about room, etc. If I selected a room it would automatically change the building name to whatever building name was associated with that room. I need to be able to select a building and then select a room.



An idea I had was to have three tables linked together in my many to many junction table. But I have never seen this done and I'm not sure how to create the subform for it, or if its even a good idea to do it this way.



I really appreciate your help. I've been working on this for almost a month now. Its so frustrating.

[orange]

Do these rules/facts match your set up:

You have Customers
Customers have Buildings
Buildings have Rooms
Rooms have Switches ( Switches are located in Rooms)

You do NOT start with I want a form....
You must get your tables and relationships designed to meet your business facts.


You want to know which Customers own which Switches?
Where is Switch XX?
How many Switches in Building ZZZ?

[ittechguy]

Do these rules/facts match your set up:

You have Customers
Customers have Buildings
Buildings have Rooms
Rooms have Switches ( Switches are located in Rooms)

You do NOT start with I want a form....
You must get your tables and relationships designed to meet your business facts.


You want to know which Customers own which Switches?
Where is Switch XX?
How many Switches in Building ZZZ?

You are 100% correct. I need to store all that information and I want to be able to search by building, room, or switch/equipment name. That is, "where is switch XXX?"... I need to search for it and see switch XXX. "What is John Doe's number?" I need to search for that and find it easily. Or "Who is the POC for room XXX in building X?" In addition I also need to see who I need to be able to search for that. I forgot to mention, I also want to be able to see who is a facility manager for the entire building. Just like with rooms, one customer could be the facility manager for many buildings, and one building can have many facility managers. At this point my understanding of access is so small I'm learning slowly and trying to figure out rooms first. Not the best approach, I know. Then again, seems I know more than some guys at work I got advice from. I was told I should have everything in one table, not to have a many to many relationship and use ; separators instead.

I was planning on using a main form and two subforms for building/room and switches. I might create a couple search boxes in the main form to search the subform.

Thanks a lot for your help.

[orange]

Work through this tutorial. It starts with a description of the business. Then leads you through a series of steps to identify the proper tables and the relationships between them. There is a solution included. But, if you work through the tutorial, you will learn how to design a database.

See this if you need more info on Normalization

Good luck. Post back after you've finished the tutorial. ( It should take ~ 40-60 minutes)

[ittechguy]

Thats definitely the most help anyone's been for this project. Thank you.

I've read through that tutorial. I've created a list of relationship requirements:

One Customer can be the Facility Manager for many buildings
One Building can have many facility managers (customers)

One Customer can be the POC for many Rooms
One Room can have many POCs (customers)

One Building can have many rooms

One room can have many switches.


With that in mind, I created this relationship. Am I correct?

[orange]

Did you work through the tutorial?

What is POC? (point of contact?)
Do you have Customers who are not Facility Managers?
Have you tried to write a 2-3 line description of each of these "entities"? Great exercise

I don't think your relationships are correct. But I don't know all of your details.
A Building has many Rooms

Every room is inside a building, right? So once you know the building, you can identify the room.
Just knowing a room number does not uniquely identify that room. You need to know the Bldg
eg All buildings could have room #1 or room #25.

Are facility managers assigned to Buildings or to rooms within buildings?

Need more details.

Great start.

[ittechguy]

I read it well, wrote stuff down, etc. I didn't actually create the database in Access. Maybe I should.

Yes, Point of Contact.

Yes, I do have customers who are not facility managers. I also have customers who are facility managers and the POC of one or more rooms which may or may not be in the building for which they are the facility manager for.

Yes, a building has many rooms. You are also correct, a rooms name can be the same in different buildings. Room #1 can be in both building 1 and building 2, but they're completely different buildings/rooms.

Facility managers should be assigned to the entire building, not a room inside a building. And as I said, a facility manager might also be the POC of a room.

[orange]

What do you need to record about a Building?
What do you need to record about a Room?

Is a Customer something you record info about?
If so, is it different info than you would record for a Facility Manager?

What is a Facility Manager compared to a Point of Contact?
What info would you record about a POC different than a Facility Manager?

--so a FM is assigned/associated with a Building

At this point, there appears to be 3 things you "interact" with - Customers, Facility managers and Points of Contact. The secret is to find out what makes these things different. So define each such that any reader could distinguish one from the other.

It would be good use of an hour or so to actually work through the tutorial. And there are others on the same page. Once you get familiar with the process, you can use it to design any database.

[ittechguy]

I only need to record the building name. I do need the rooms within the building but that's another table. If anything I might have a field for building floor plans. I wouldn't cry if that didn't happen.

In rooms, I need to record how the room is secured (a key, pin, combo, or contact POC for entrance). I also need to record the cabinet key # (if there is a cabinet inside the room, some rooms don't have cabinets). I do not see the need to have a separate table for cabinets as we do not ever have more than 1 cabinet per room. Most rooms have none.

For customers, I need to store organization, shop name, office symbol (usually a 5 letter code), rank, last name, first name, phone number, email.

The only difference between a room POC and a facility manager is the facility manager is the POC for the entire building, whereas a room POC is the point of contact for a room.

So yes, a facility manager is associated with a building.

I'm going to work through that tutorial. I agree it'd be good for me.

Also, if you can't tell, this is for a military base.

[ittechguy]

Basically, as for the three things we'we're interacting with...

All customers are POCs. It's not even necessary to mention that in the database because the users would know this. These customers are either a POC for an entire building, or for a room. Or for both a building and a room, or several buildings and/or rooms. There will be no customers not associated to a building or room.

[orange]

I'm attaching a logical model based on our posts.

It is basically saying:
All Customers are either FM of Building OR POC of Room in Building
Each Customer has the coordinates you suggested (name, rank...)
Each Building has a Name
Each Building contains Rooms
There is a list of room security options
Some Rooms may be secured
Some Rooms have a Cabinet
Cabinets have an ID and KeyNo

It's been a while since I used that software. Use this as a starting point. Take some sample test data and make sure you can get to the info you need.
Good luck

[ittechguy]

Thanks a lot! I'll work on this tomorrow.

The only thing I don't see on your diagram is switches. The only thing in my switches table that is not unique is the ios software version. Although I might add a switch model and switch type. So I will create multiple tables for this.

One thing I'm not sure of though. A switch is either inside of a open room, or inside a cabinet. There can be more than one switch in a cabinet. Would you link switch name to both rooms and cabinet?

[orange]

Yes, missed the switches -was focusing on latest posts. But just saw your post.

I am attaching a model of the physical layout. As I mentioned it's been a while since I've used this software so there are a few things it is doing for me that I'd rather do myself (names/keys). This should have all the tables that we have discussed. You may want to add more fields to meet your needs. This should give you a good start.

Again create some test data and make sure you can get out what you need.

I am attaching a database (inside the zip) that was generated from the physical model. You may want to adjust column sizes and add others as necessary. Because of the compound keys, Access does a poor job of laying out the relationships. Haven't tried generating one of these for years... so feel free to adjust as necessary.
Good luck

[ittechguy]

Thanks a lot!

One question. I do not understand why you have a one to one relationship between customers and POC as well as customers and buildings. What is the benefit to this? I thought that if one customer can be the POC for many rooms and one room can have many customers, it was a many to many.

[ittechguy]

It's clear to me this project is way too advanced for my little knowledge of database.

I added data into the tables and everything seemed to work great. Then I tried creating a form that would show point of contact building and room location. I haven't the slightest clue how it's done. It hurts my head. I simply do not know how to make one form for several tables. And honestly it doesn't make any sense that it'd be this hard. I've read that it's best to have one form per function or table. But then I'd have over a dozen forms.

I've tried using one query across many tables but I end up with a read only query. It just doesn't work.

Do you have more tutorials? I feel like I should probably put the project on hold and just learn access.