Impossible structure? Subforms, subsubforms, separate tables, or something else?

[lirantha]

(If this would belong better over in the "Forms" area, feel free to move it.)



I'm a real newbie to Access, who grasped the possibilities of a relational database last fall and said, "Ooh! That's what I need to organize the data for my dissertation!"

After working on creating a database for more weeks than I would like to count, I have discovered this afternoon that I might not have understood those possibilities correctly. Or that it may take a lot more effort, or an entirely different approach, to get Access to do what I have envisioned.

The problem:

I am creating an architectural catalog, using my database as a way to enter detailed information about different types of buildings, parts of those buildings, and things found in and around those buildings. A (simplified) example of the kind of structure I wanted to achieve, using one-to-many relationships for each master to child:

Portico (Master)
--> Rooms (Child to Portico)
-------> Columns (Child to Rooms)
-------> Drains (Child to Rooms)
-------> Finds (Child to Rooms)
-------> Sculpture (Child to Rooms)
--> Columns (Child to Portico)
--> Drains (Child to Portico)
-------> Finds (Child to Drains)
-------> Sculpture (Child to Drains)
--> Finds (Child to Portico)
--> Sculpture (Child to Portico)
--> Some more fields unique to Porticoes

CultStructure (Master)
--> Rooms (Child to CultStructure)
-------> Columns (Child to Rooms)
-------> Drains (Child to Rooms)
-------> Finds (Child to Rooms)
-------> Sculpture (Child to Rooms)
--> Columns (Child to CultStructure)
--> Drains (Child to CultStructure)
-------> Finds (Child to Drains)
-------> Sculpture (Child to Drains)
--> Finds (Child to CultStructure)
--> Sculpture (Child to CultStructure)
--> Some more fields unique to CultStructure

I would then have one Main form that would pull all of these buildings together, as well as adding information unique to the Main form, like so:

Main
--> Portico
--> CultStructure
--> Some more fields unique to Main

Eventually, I want to use Reports to analyze and collate all of this data quickly, which is why I haven't just dropped the project and gone back to using notecards.

I set up all my relationships (across 25 tables, each with a single associated form). I then dragged and dropped forms into other forms to create subforms (and thereby subsubforms), relying on all my elaborately and carefully constructed one-to-many relationships to keep everything in order. When I first tried to enter data into my forms, Access freaked out and gave me an error about not being able to open any more databases. I then drastically simplified the structure in an attempt to isolate the problem, and reduced the whole thing to 3 one-to-many relationships, as follows:

Ancillary (Master)
--> Rooms (Child to Ancillary)
------> Columns (Child to Rooms)
--> Columns (Child to Ancillary)

At this point, when I tried to enter data in either the Columns subform (Child to Ancillary) or the Columns subsubform (Child to Rooms), I got this error: "You cannot add or change a record because a related record is required in ___" (either Ancillary or Rooms - wherever I was not attempting to enter data). I figure this is because Access wants Columns to be linked to a record in both Ancillary AND Rooms, when I just want it to be linked to Ancillary OR Rooms (which is then linked to Ancillary or to another table like Portico or CultStructure).

My questions:

1) Is the kind of structure I outlined originally even possible? And if so, how do I get it to work?

2) If it isn't possible to have a table that is both in a many-to-one relationship with Child table A and in a many-to-one relationship with the Master table of Child table A, what would be the best way to approximate that? Creating duplicate tables for each sort of relationship seems terribly inelegant, but at this point I'm getting desperate (and will need to show my committee that I've been doing *something* pretty soon).

I've had several people (including the person who convinced me that a database was the way to go) take a look at what I'm trying to do and throw up their hands at the complexity. I would be very grateful for any suggestions.

[NTC]

Fundamentally a database can do what you need. Access is far & away the best PC database and today's PCs are very powerful. But one can still hit the wall if you attempt to fix the relationships and force open too many data fields. For instance I can interrelate every component of a home - and if that means when I open 'flooring' that every component table of the entire house also opens - it is just too many data fields.

Many level drill downs can be very challenging to display and design. You will want to auto close objects rather than allow too many to get opened at once after all the human can't see 6+ screens at the same time. Beyond that it is very situational and the precise implementation is dependent on the data and user experience you are aiming for.

[rpeare]

Your description does not go into detail of your actual data structure but here is What I would do in terms of structure, and it may simplify your data entry:

CatID  SubCatName ----> other subcategory fields
1         Room
2         Column
3         Drain
4         Find
5         Sculpture

SCXref  CatID  CatID_Sub  (this table is a junction table just to tell your database which categories have subcategories of their own)
1       1      2
2       1      3
3       1      4
4       1      5
5       3      4
6       3      5

RoomID  RoomName ---> Other room related fields
1       Portico
2       CultStructure

NOTE: The Sub Category Cross Reference table (SCXref) is only to help limit which items will be available for which subcategories)

Then you could actually have the structure you want doing this:

BuildingID  BuildingName  --->  other building related fields
1           Building A
2           Building B

BP_ID  BuildingID  RoomID  ----> other building and room specific fields
1      1           1
2      1           2 

BRC_ID  BP_ID  CatID  ---->  other building and room specific related to subcategory
1       1      1
2       1      2
3       1      3
4       1      4
5       1      5

BRSC_ID  BRC_ID  SubCatID ----> other building/room/category/subcategory related fields
1        1       2
2        1       3
3        1       4 
4        1       5
5        3       4
6        3       5

You could likely combine the categories/subcategories in the same table too. This structure on a bound form might be complex though, I am not sure how many levels of subforms within forms you can have.

[lirantha]

Your description does not go into detail of your actual data structure but here is What I would do in terms of structure, and it may simplify your data entry:

Thanks for the response! I think I understand the structure you're outlining - a way of representing how all of the various tables are related to each other without relying just on linking fields. What I'm not sure about is how to turn that into something usable without running into the same problem I did above, since the one-to-many relationships would still be the same. Or is the error I was encountering about a related record being required in another table simply the result of loading a subform twice at once (at different levels of subordination) on the same form? I.e., would I need to solve this by ensuring that only one instance of a subform was ever open at once?

The cross reference tables would serve as a quick way to tell Access which subforms to load on a given form - is that correct? So I could use a list box or combo box to select the type of building, which would refer to the cross reference table and load the appropriate set of subforms? Would that in and of itself be enough to get around the problem of loading the same subform twice?

(Sorry for any horrible confusion in my questions; I've only managed to teach myself just enough to throw around some terms without fully understanding some of the distinctions between them or their implications.)

[rpeare]

I don't use forms/subforms, all my databases use unbound controls so someone may be better able to answer the form/subform questions, however, you have the same general structure for every building, at least in the way you have described it on this thread (i.e. ROOMS will always have subgroups of column, drain, find, sculpture) In other words for some subcategories (ROOMS, DRAINS) your subform would be active, for other subcategories (Columns, Find, Sculpture) the subform would NOT be active to prevent accidental data entry. And based on what is chosen on the 'main' subform you could (or should) requery the list of 'available' items in the sub-subform. Again keep in mind I do not use forms/subforms.

Here's a simple example

Lirantha.zip

I didn't spend a lot of time cleaning up the code but you should get a fairly good idea of what to do.

[orange]

lirantha,

A few comments that my help put things into perspective. As NTC said, and as a colleague once said to me, just about anything you need can be designed and programmed - it's SMOP. Just a small matter of programming. In reality it takes a lot of analysis after a clear understanding of requirements; then a translation of that design into appropriate structures and processes supported by a data base management system; and some proficiency in database generally and the selected DBMS specifically. None of this is trivial - there are concepts and there is experience from previous databases and applications involved.

As you are finding, and as most here have found (even if it's not always admitted) - design, programming and testing can take "enormous amount of time"--usually much more than anticipated. And the sad part is, it' only after having done things the "wrong/hard way" that one stumbles upon shortcuts or rules to make things better.

I certainly don't understand architecture sufficiently well to offer advice or criticism. However, regarding database it is usually much more efficient to build a solid description of the "business" of a issue/opportunity in plain English, to identify the various rules between the things you have identified, then to build a data model based on that business description and rules. Create some test data and scenarios and "test the data model" to make sure it matches your business requirements. This is a general approach and certainly overview many complexities.

There is a good tutorial on this process here but it you must work through it, and it is quite basic to the task you are facing.


I see hierarchy in your subject matter and think you may get some ideas from this article and database.

I am not trying to dissuade you, but just trying to emphasize analysis and design is a major step before getting too involved with Access or any database management system. In addition, as NTC suggested, you seem to have a greater than usual level of complexity with drill downs etc. All of these things will take time.

Good luck with your dissertation.

[orange]

Sorry, resulted in a double post when I received a PM while editing?????

[lirantha]

I didn't spend a lot of time cleaning up the code but you should get a fairly good idea of what to do.

Ah, thanks! Very helpful to see that in practice. I'll study it some more tomorrow and see if I have any other questions.

There is a good tutorial on this process here but it you must work through it, and it is quite basic to the task you are facing.

That's an excellent link - thank you. I'd worked through normalizing my tables* already, but the entity-relationship model aligns neatly what rpeare was recommending, I think. I had built all of the tables to store information about different building/building elements, and was relying entirely on relationships between primary and foreign keys to express the way each of those tables were interrelated, rather than creating additional tables to express the data hierarchy. Just for laughs, here's a screenshot of my original relationships screen, showing about half of the existing tables:



I reworked it on paper this afternoon and I think I can remove a fair bit of that snarl by creating those cross reference tables. I also see that I was violating some normalization rules by entering certain fields in my tables simply for ease of reference - e.g., BuildingName on child tables, when BuildingName really depended on the master table - because I had gotten it into my head somehow that each form and every control on that form had to be bound to fields on a single table.

* Normalizing for the most part, at any rate... I got a bit lazy on one because normalizing my Finds table would easily double the number of tables and forms in the database, and I don't expect to search or otherwise manipulate 95% of the data I'm entering there.

I see hierarchy in your subject matter and think you may get some ideas from this article and database.

Ohhhh... oh, that's awesome. That's exactly the sort of hierarchy I was thinking about. I guess the problem was that I was, in effect, unintentionally creating a bunch of many-to-many relationships without using a proper junction table... which, naturally, got Access all tied in knots.

My redesign this afternoon was an attempt to keep building parts from appearing at multiple indenture levels. I simplified the hierarchy into Buildings/Building Complexes > Structures (e.g., Porticoes, CultStructures) > Elements (e.g. Rooms) > Features (e.g. Columns, Drains, Finds, Sculptures) and inserted catch-all tables labeled something like "UnspecifiedElement" as a kind of hierarchical spacer - to serve as the parent for Feature information that really applies to a Structure as a whole. That's a rather clumsy workaround, but I think it would eliminate the error I was getting. On the other hand, this indenture level thing looks brilliant -but I'm a bit worried about the complexity of implementing it on top of everything else I'm trying to figure out. The deciding factor may be whether or not the following statement is really accurate: "reporting the entire structure [of tables with children and grandchildren] from the top down goes beyond the capabilities of subforms or subreports." If that's really the case, then I think I'll have to try the more complex solution.

I am not trying to dissuade you, but just trying to emphasize analysis and design is a major step before getting too involved with Access or any database management system. In addition, as NTC suggested, you seem to have a greater than usual level of complexity with drill downs etc. All of these things will take time.

Point very much taken. I did try to design it cleanly when I started, but I found as I went on that there were just so many things I had initially overlooked. This was a much less ambitious project to begin with, when I didn't think I wanted a great degree of normalization. Once I decided to split measurements and descriptions out of long text boxes into searchable fields, the whole thing just began to balloon out of control and hasn't stopped yet.

Good luck with your dissertation.

Many thanks!