Re: [MS_AccessPros] Summing from a hierarchy

 

Adam-

I don't get what tblParents is doing.  That looks the same to me as your proposed tblNodes.  Doesn't the ParentID in tblClients link to another client?  That's the chain I would think you would follow.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Mar 26, 2015, at 9:09 PM, runuphillracing@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Think of it is somewhat like a BoM.

One problem with changing it on the fly, I think, is how do I write that SQL if there can be 0 to infinite (theoretically) levels of children - an unknown number of "In (SELECT" nests.

Another is that, in addition to viewing each Parent individually, I also want to view them as a list, ranked by revenue (descending).

What do you think of the idea of creating an additional table, let's call it tblNodes?

tblNodes

   *ParentID (long, link to tblParents)

   *AcctNo(text, link to tblAccounts)

*Combined make the primary key

This would contain a list of all parents (nodes), and all AcctNos under that node. I would create this table by looping through the tblParents, tblClients, and tblAccounts. I would have to recreate this table each time a new account or client is added/deleted or hierarchy changed, but that's much less frequent than every time my forms are current. The forms (list and individual Parent view) would be based on a query combining tblNodes, tblAccounts^, and tblRevenue.

^I realize that tblAccounts and tblClients should be combined into a single table:

   *ClientID (auto)

   ClientName (text)

   ParentID (long)

   *AcctNo (text, alphanumeric)

   AcctName

   ProductType (text)

*ClientID and AcctNo make the primary key.

What do you think of this approach? Is there a better way? What else can I do to explain this better?

Thanks

Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

It's not clear what you're trying to do.

Yes, you can change the Record Source of a Form on the fly by assigning the appropriate SQL to the RecordSource property.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Mar 26, 2015, at 4:40 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Repost. I'm looking for help in summing from children, grandchildren, etc., in a hierarchical relationship. See original post below.

I know how to create a recordset with this data, but I don't know how to have a form's recordsource be based on a recordset created at that time. I suppose I could make a table each time, but that seems inefficient. Is that how I should go? Is there another way?

---In MS_Access_Professionals@yahoogroups.com, <runuphillracing@...> wrote :

I have clients set up in a hierarchical relationship. I want to be able to move (on a form) up and down the tree levels showing totals at each node for all the children, grandchildren, etc., under that node.

The key tables and fields (not all fields listed, just enough to explain, I hope) I have (so far) are:

tblClients:

  ClientID (auto)

  ClientName (text)

  ParentID (long)

tblAccounts (a client may have multiple accts)

  AcctNo (text, alphanumeric)

  AcctName

  ProductType (text)

tblParents

  ParentID (auto)

  ParentName (text)

  Parent (long, ParentID of this parent, if there is one)

  SalesRep

tblRevenue

  AcctNo (text)

  Revenue (currency)

I know how to sum from one level below, but not sure how to get beyond that.

Thanks

Adam Feerst

Denver, CO

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___