Arthur Fuller
artful at rogers.com
Thu Jul 22 12:06:50 CDT 2004
Thanks! I think you may be onto something here. There's no reason that a view can't be a relational multiplication. The trick is going to be devising the unique keys of its rows, and relating those to level 4, but I think that I can pull that off. Thanks again for the pointer! Arthur -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Thursday, July 22, 2004 11:43 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Treeview Problems Hi Arthur Yes I can see your problem at connecting level 3 and 4. Although your stomach may face a hard time I think that's a possible route, though you - as far as I can see - wouldn't need (to fill) a bridging table but could use a view or query where you bring the PK of the orderdetail record forward together with the list of all orderdetail types. Then you will at level 4 have that PK and all order types and can select any of these even if the picked combination doesn't carry any actual orderdetails of that type. I haven't your code at hand so I cannot verify if this is possible. /gustav > Thanks for replying, Gustav. The part I'm having difficulty getting my > head around is this. Suppose 4 levels in the tree: > Customer > Order > All Possible Detail Types > Specific Detail of type x > My code can handle levels 1 and 2 with one statement each. No problem. > Level 3 I can write with individual "Add" statements. But now what? > How can I relate level 4, which should be as easily handled as levels > 1 and 2, i.e. with one statement that mentions the parent key? I don't > have a parent key, or maybe I do, but I can't see it. > At the moment, I'm thinking that the only way to achieve what I want > is to manufacture a bridge table called OrderDetailTypes and > automatically add N rows to this every time a new Order is created. > Such a solution makes my stomach turn, but at the moment I don't see > another solution. > Any advice appreciated, > Arthur > P.S. > Thanks for the rationalization (justification) you offered. Next time > I fight with my girl friend, I'll request more rationalizations :) > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav > Brock > Sent: Thursday, July 22, 2004 4:27 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Treeview Problems > Hi Arthur > Your problem is that the tree structure you aim for is not a true > branching tree structure as you at level 3 wish to list all possible > order detail types instead of those actually used. > However, it could be easily solved; you don't even need a union query, > you just need to - as you state - list all possible types, regardless > if they are used or not (that means just list them). I guess it will > break the system in your fancy tree code but you can justify that for > yourself by remembering that the code was created for the clean tree > structure. > /gustav >> Some of you may have read my stuff about data-driven treeviews. >> Lately >> I have run into a problem using my own code, and am looking for a >> graceful way out. >> If you've read or at least run the code, then you know that it >> reduces >> the population of any given level to a simple select statement, using >> a query, a table, a view or a UDF. >> However, my current problem is this: >> Let's suppose that you have parent table P and child table C, which >> references an FK F (i.e. OrderDetailType.... Assuming that there can >> be several types of OrderDetail, say CarRental, CampGroundSite, >> CanoeRental, etc. ((hey, it's summer and I need to revisit Killarney >> Park, one of the most beautiful places in the world, so forgive my >> examples)). >> Assume that Treeview level 1 is Customer. TV level 2 is Orders. >> Treeview Level 3 is all the possible order detail types. TV 4 is the >> detail types arranged under TV3. >> I.e. >> 1: >> ABC Company >> DEF Company >> 2: >> Order 123 from ABC >> Order 125 from ABC >> Order 126 from DEF >> Order 139 from DEF >> 3: >> CarRental >> CampGroundSite >> CanoeRental >> Etc. >> 4: >> Specific instances of CarRental, CampGroundSite, CanoeRental, etc. >> My code so far can handle levels 1 and 2 easily, one statement per >> level. >> The problem is at level 3. I want to populate the treeview at level 3 >> with all the order detail types (CarRental, CampGroundSite, >> CanoeRental, >> etc.) for each Order. It's a simple UNION query to do this, but where > do >> I get the unique key? >> Generalizing my problem, I have a simple (and dare I say it pretty >> cool) way of populating the data-driven levels of a treeview. But >> that's insufficient for my current problem. I need to populate the >> OrderDetailTypes level with all possible order detail types and then >> sub-populate level 4 with the corresponding children. I'm at a loss >> how to achieve this. >> One way to do it, which would fall conveniently into my current >> scheme, is to populate a bridge table (OrderDetailTypes) with one of >> each OrderDetailType... But that sucks, IMO. It stores a bunch of data >> that is most of the time unnecessary. I HATE unnecessary detail! I'll >> go there if I have to, but it makes me gag. >> Generalizing the problem.... >> Given: code that can populate any tree-level with a simple select How >> to insert a level that is not data-driven or alternatively is >> data-driven but not with related FKs Treeview nodes insist upon unique >> keys (understandably so). But how should I manufacture said unique >> keys given that no physical rows correspond to them. >> Maybe the generalization is: how to relate a grandchild to a parent in >> the absence of parental data? That might be an incorrect phrasing, but >> that's what I mean. >> I hope that I have stated the problem clearly enough for you to >> follow >> the logic. If not, please reply for further clarification. >> Any suggestions much appreciated. >> TIA, >> Arthur -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com