Nested tables/drill down limitations...?

[connexion]

Hi there,

I'm using access 2000 for starters.

I have created one to many relationships for each of 10 cascading tables dependent on related records in each preceeding table and was expecting to be able to drill down into them in the standard database window prior to creating a data access form to work with.

However i have found that tables 1 through 7 are visible, but access doens't give a little "+" in the corner of table 8 to get to tables 9 and 10. i.e, i can't seem can't seem to drill down beyond table 8 to view related records in tables 9 then 10.

Does this mean that there is a limit to how far you can drill down through table relationships within access?

Thanks
Vince

 

[The_Doc_Man]

Does this mean that there is a limit to how far you can drill down through table relationships within access?

Yes. Damned if I know what it is, though. I'm not convinced you reached it.

There is also a table property called SubDatasheetName, which needs to be set to AUTO in order for the table in question to support the subdatasheet feature. Before you get all groused about this possible limit, check the appropriate property.

Also, you can specify the subdatasheetname by pointing to a form in datasheet mode. Finally, the Auto only works if the subdatasheet is for a table with which the higher-level table (the parent) has a one-to-one or one-to-many relationship. If the "parent" is on the "many" side of the relationship, the autoexpand feature cannot automagically display the subdatasheets.

 

[connexion]

I reckon i've reached it

The tables are all so tiny i can't believe it isn't straight forward

All tables are linked so that each parent is on the one side of a one to many relationship, so that checks out.

From table 8 (the one that can't see table 9) i have gone in and the auto property was set correctly. I've changed the property and pointed it right at the next table along from table 8 to 9 and from 9 to 10 and it still doesn't work.

I'll get round this, but to be honest i was only doing this as a quick way of having a look at how a series of questions would be organised with this many levels involved. Seems like i've got a bit more of a job on my hands now though!?

Cheers Doc

Vince

 

[The_Doc_Man]

Vince, I wish I could tell you the real size of that display limit but the places I looked didn't help me. It is possible that my library is outdated (gasp!). Have you done a search of the MS Knowledge base for "limit on subdatasheet display" or other similar topics? Perhaps just a search for "subdatasheet" and then read the titles to find ones that look promising.

As to your problem, I would take the "divide and conquer" method. Make notes on however far you can go. Then close the top level table, open your level 7 table, and see where it goes from there. Do your evaluation 7 levels at a time if needed.

Also, I don't normally suggest this, but it might be productive in your case. In the menu bar, take path Tools >> Options and find the check box that shows you System objects. There is a table holding relationships. The columns are directional and show you which side is one, which side is many, the table names, and the field names. Plus the flags associated with Referential Integrity options. It might be possible to examine this table to generate a report that would help you in your assessment.

Finally, I'll offer this thought. You are describing a heirarchical layout when you have a relational tool. Yes, Access will handle it for you, within limits, but you would do better to think of this in other ways. Hierarchies tend to proliferate tables.

Search this forum for topics including the word "questionnaire" and variants thereof. A few months ago we had a batch of questions on how to lay out databases for questionnaires. They included some suggested structures that were neither flat nor fully hierarchical, but were somewhere in between.

 

[connexion]

Blimey Doc, that's a little deeper than i thought it would be.

You're not bad at guessing though, i am looking at having a development company build a system incorporating a questionnaire (done using SQL rather than access though so it will hierarchical). I thought i'd just look into it a little first to be able to tell them how many levels deep it needs to go.

Maybe my answer should be..."errr? maybe over 7!" ?

As always though i am interested in some of the ideas you've suggested and will do a little more looking into this just so that i can get a better grasp of what solutions others have come up with.

Your help is greatly appreciated.

Vince

 

[The_Doc_Man]

done using SQL rather than access though so it will hierarchical

non-sequitur

The concept is hierarchical, perhaps, but the representation does not have to be. Having once implemented a hierarchical real-time database manager, I can tell you it was no fun to traverse, search, or manipulate the hierarchy. If it hadn't been for certain real-time and dynamic memory management mapping issues, I would never have done it that way.

When you look at questions and answers that {imply, enable, condone} other questions, you are looking at something that does not HAVE to be stored as a hierarchy. Let's start with a concept for demonstration.

Suppose you have a list of questions with ONLY yes/no answers. The first question to be asked is always question 1. Thereafter, ...

tblQuest
QuesID, autonumber, PK
Question, text or memo
AnsYes, text explaining implications of selecting YES
AnsNo, text explaining implications of selecting NO

tblSequence
ThisID, LONG, FK - the question just asked
NextIDY, LONG, FK, the next question to ask if this answer was YES
NextIDN, LONG, FK, the next question to ask if this answer was NO
* and NextIDY or NextIDN is 0 when there IS no next question.

tblAnswers
TestTaker, LONG, FK (to a test-taker identification table that I am omitting.)
QuesID, LONG, FK
AnswerWas, yes/no

Assumptions: Date of taking the test is associated with TestTaker if at all.

Now if you think about this, you quickly realize that this structure can have questions 10 levels deep - or 10,000 levels deep! And it doesn't matter whether the questionnaire is symmetrical (I.e. same number of questions regardless of yes/no answers.)

This isn't the easiest setup in the world to manipulate, but we are talking structure and concept, not details. There are ways to add fields to these things so you can "build" your questionnaires one layer at a time, perhaps by adding keywords to your questions so you can search for them when creating the sequencer table.

You can also decide what answers you condone for a given question if it is multiple-choice (a,b,c,d) instead of yes/no. Just adds a few fields to the sequencer table. The ONLY problem you would have making a good questionnaire is if you condoned free-form answers in the middle of a logical sequence and still had to proceed down a sequencer chain.

The question structure is hierarchical, true, but there is no need to represent it that way. In fact, there is a normalization argument that suggests you should NEVER have two or more different tables to drive a question/answer series when the only difference between the questions is the order in which they appear.

Also consider this: If your questionnaire is 10 questions deep and you have separate tables underneath each answer, the potential exists for you to have 2^10 tables for the YES/NO case! Checking HELP topic Access Specifications, you would see that this ain't gonna get off the ground.

 

[connexion]

Deeper & Deeper (i think i'm drowning now!...glug glug...)

Firstly, i can't believe it Access 2000 help file had the answer....most unusual...

"Nesting subdatasheets
You can nest (up to eight levels) a subdatasheet within a subdatasheet. However, each datasheet or subdatasheet can have only one nested subdatasheet. For example, in the Northwind sample database, the Customers table can contain one Orders table subdatasheet, and the Orders table subdatasheet can contain one Orders Details subdatasheet. But the Customers table can't contain both an Orders table subdatasheet and a Salespeople query subdatasheet."

"if you condoned free-form answers in the middle of a logical sequence and still had to proceed down a sequencer chain."

Hi Doc,
Yes! you're hitting the nail on the head now. What i am looking at goes a bit further than your average test/survey. Answers supplied will include "Yes", "No", Multiple Choice (Text), Numeric, Date and free-form Text values.
Questions may terminate at any given point along a sequence and each sequence will be contained within an overall structure containing categories, sub categories, sub-sub categories, etc. etc. forming a pathway down each category to the end (first QuesID in the next category if current QuesID <> last QuesID last category).

Hold on...just getting a pill...(that's better!!).

So, that in mind, perhaps i should just stay the heck out of it and see what they come up with!

Vince

 

[The_Doc_Man]

This is still possible but the more things you try to include in the scope of your possible answers, the wilder it will get.

In the truly general case, you are looking at some really serious VBA programming and some really complex answer analysis.

Fer instance, if you can put any date as an answer to a date question AND the date makes a difference, you will have to define the possible answers in some reasonable way like, answer is before critical date, after critical date, on critical date, before prehistoric times, etc.

If you can have any text answer at all for some questions, how can you ever decide what you will accept?

Here is where your problem isn't the algorithm per se but the rules of the questionnaire. If you cannot define every possible action for every possible answer, you run afoul of the "old programmer's rule."

"If you can't do it on paper, you can't do it in Access."

 

[connexion]

Well i suppose that you can predict most things apart from poor spelling?

Providing the user with a list of previous answers to chose from for a relatively straight forward question allowing them to add to it you could limit the number of possibilities being entered and still allow flexibility. The search through free text answers is then a case of selecting one, a range, or all of the answers provided?

Not exactly the fastest search in the world, but pretty good at finding what you want it to??

Vince

 

[The_Doc_Man]

The search through free text answers is then a case of selecting one, a range, or all of the answers provided?

The problem with general users is that they follow the Yale Law of Animal Behavior.







OK before anyone asks.....

"When a researcher places a test animal in a tightly controlled environment in which all factors are managed including light, temperature, humidity, and other sensory stimulus; and when the test animal has been carefully bred for purity of strain for at least eight generations; and when all options available for the animal are tightly controlled; and when all observers are invisible to the test animal; it will do as it damned well pleases."

I.e. truly unpredictable. Avoiding the philosophical differences between biological determinism and religious free-will, what you get would probably be very hard to prove as NOT being the result of your users exercising individual free wills.

When you allow a user free-form input AND you want to deal with the answer any other way than just STORE it, you have to manage spelling, syntax, language, dialects, slang, general illiteracy, alternate words with similar meanings, and general perversity. If you are not up on text parsing; if you don't have a REALLY REALLY good dictionary; and if you don't have good parser tools; you will get input and general results of a quality somewhere below that of fish left in the sun three to four days.

Again, refer to the "old programmer's rule" quoted supra

I would almost NEVER give anyone free-form input if the answer had to govern a subsequent choice. I would more likely give them a list of phrases and let them choose among the lot. And if the answer is TRULY free-form and you TRULY need it, you won't be making very many decisions based on it.

OK, maybe I'm selling your design abilities short, and please don't take that as being an intentional insult. I just don't trust general user input that isn't somehow constrained in scope. I've been burned far too many times to trust that. Maybe my cynicism is getting in the way. But let's say I have deep doubts about free-form input as a way to drive a questionnaire's subsequent choices.

One last "old programmer's" aphorism:

"Artificial intelligence cannot cope with natural stupidity."