Access form problems with many to many relationship

[ittechguy]

I understand what you mean when you say I don't need forms at this point. I wouldn't want to completely setup a complicated form setup... and find its not working because my relationships are wrong. However, I'm working with forms because I'm trying to learn them as well. If I found I wasted my time and learned something, it wasn't time wasted.

Anyhow, I'm having a hard time figuring out how I should set up the relationship between rooms and switches while planning for the future (to allow me to have different types of equipment).

I thought about having a junction table between rooms and switches. I'd call it "equipment" and it would contain the PK from a switches table, routers table, etc. This doesn't seem practical because not a rooms have routers.

Another idea was for me to have a single equipment table containing all our equipment and then a field for equipment type, and then I'd specify router, switch, etc. That table would end up having a lot if duplicate data and might not be the best approach as far as normalization is concerned.

I might just have a separate table for each type of equipment and each have their own junction table which links the PK to the room id.



What would you do in a situation like this?

[ittechguy]

The main reason I don't want to have one equipment table that stores all our equipment, is that some equipment requires we store different information. For example, switches have one I address which needs to be stored. We have equipment that has several.

[orange]

I would not start by saying I don't want a single Equipment table. Let your analysis and requirement unfold. You'll find some options -- some feasible and some not. Work your way through those that are feasible in due time.

You could start with a single Equipment table. Make sure that table has all the fields (attributes) you need to handle Switches. Planning ahead, you could work from the general Equipment table and develop a SuperType/Subtype set up. This also is not a trivial concept. I used it in the Customers(supertype) could be decomposed into PCO(subtype) and FacilityMGr_PCO (also a subtype). This set up was a complete subtype in that all possibilities are covered. Below are some links to info on super/subtypes -- it's good reading at the moment but I would not focus there at this point. (In my view there are still requirements to be discovered, and you may also be faced with some priorities that will influence your development.)

As for using your database to assist/support the network engineer -- could be a requirement --more like a would be nice to have. Many of these things we referred to as smop (small matter of programming). If you can define it, someone will/could/might program it. It may be totally impractical (cost prohibitive, requires spcial equipment..). This is really a question of scope. What is in scope? What isn't? You could map this into your conceptual model -just a black box or two.

Several years ago we needed to have special presentations to show trend analysis, and some what if scenarios to senior management. Someone had OpenGL or VRML on a laptop with some modelling software??? and could use a csv file for data to superimpose over a map or graphic. We took data from database(s) into a csv and passed parameters to that package. It's impressive (at least in the early 90's) to show how the sales of beer change by week and you display it as a varying size 3D state image. Or ExportSales by SIC by Province..etc All that to say there is probably a graph package (Vision.. and others) that could chart out a network based on some simple grid mechanism. (could be data from your database).


Subclassing - mvps

SubSupertypes - sqlmag
-AppendixB
-youtube re concepts
-learndatamodelling

[ittechguy]

My database is coming along nicely. I've attached it here. BuildingsSwitches3 (1).zip I've ran several tests on it and it all looks good.

I'm having some small issues getting my forms to work the way I want to, but its coming along.

[orange]

Glad to hear things are procressing.

I'm having some small issues getting my forms to work the way I want to

In general,that all seems positive. However, if readers are going to offer focused help/advice, you need to be specific.
eg On form X I can't get the YY to represent the WW. I need to have the proper WW for this Q in order to...

I did try something and got a message that table or query RoomName could not be found. This is the related SQL. I don't see such a table in your db.

SELECT Rooms.RoomsID, Rooms.BuildingID, Rooms.RoomID, Building.BuildingName, RoomName.RoomName
FROM RoomName INNER JOIN (Building INNER JOIN Rooms ON Building.BuildingID = Rooms.BuildingID) ON RoomName.RoomNameID = Rooms.RoomID;

I realize you understand your db/environment and test data and what you expect better than all readers, but if you have test scenarios written down, it would be useful to post them.
Similarly, for clarity, for future documentation and for future training, an overview of the purpose of forms , queries etc would be useful to you and your project. When you're developing and modifying a few things it can be helpful to to reassure yourself and others-- "what was the purpose of this form? What was the reason for XX?"
Some things are obvious, but others (many) can get lost in the "fog".

To put this in perspective, just think of the things you would want if you were take over this project from someone else. That is, suppose John Doe had this project and was pulled away, and the project was given to you- exactly at the stage it is now. Interesting isn't it?

Anyway, I have not kept up with your project. I got rid of some of the things I had. That's why in recent posts, I asked for a current copy of the db.
I'll save a copy so I can relate to the same data/structure etc, if you have specific questions.

[ittechguy]

I deleted my roomname table. I thought I deleted all the related queries, but I'm sorry, it seems I've missed one. Also, I'm going to start keeping a log for this database. Where would you keep it? I think it might be best to incorporate that inside of the database. Rather than a file outside the database which talks about the database.

Anyways, here is a link to my current problem. https://www.accessforums.net/forms/m...tml#post292493