ACCESS FEED: Re: [MS_AccessPros] Summing from a hierarchy

John,

I understand. The poor explanation is my fault. I changed some of the names here to simplify, and also obfuscate sensitive info. I made some mistakes in the translation from the real data to here, and the structure evolved through this conversation. I apologize for that.

The key fields in tblAccounts are AcctNo and ProductType. I went away from using ClientID because I already had a unique combination of fields (above). I try to keep things as lean as possible, both part of my nature, and good database design. I'm working with data from AS400 and other sources. I don't get to decide how that's designed. AS400 uses a new ClientID for some products, and an existing one for others. And for some products (which exist outside of AS400), a company might have multiples of that product, each with its own ID. Building the hierarchy is another challenge. That doesn't exist in any of the mainframe systems, at least the ones I have access to. I'm getting most of that through their SalesForce data (disconnected from the mainframe data), with some manual massaging.

Even though I ended up figuring most of this out myself, it was the process of going through this with you that helped me figure it out. I thank you for letting me bounce ideas off of you.

Adam

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

Adam-

It makes no sense to combine tblClients and tblAccounts if a client can have more than one account. ClientName could get repeated multiple times - and entering a value (other than a key) more than once is a sign of a bad relational design.

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 27, 2015, at 5:49 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Forget the original list. "I realize that tblAccounts and tblClients should be combined into a single table."
tblAccounts
   *ClientID (auto)
ClientName (text)
   ParentID (long)
   *AcctNo (text, alphanumeric)
AcctName
   ProductType (text)
*ClientID and AcctNo make the primary key.

What I've done so far is

qryParentParent (a list of all parents with parents) - SELECT tblParentList_1.ParentID, tblParentList_1.ParentParent AS ParentParent
FROM tblParentList AS tblParentList_1 INNER JOIN tblParentList ON tblParentList_1.[ParentParent] = tblParentList.ParentID

qryNodesAccts - SELECT DISTINCT tblParentList.ParentID AS TopParent, tblAccounts.AcctNo, tblParentList.ParentID AS DirParent
FROM tblParentList INNER JOIN tblAccounts ON tblParentList.ParentID = tblAccounts.ParentID
UNION SELECT DISTINCT qryParentParent.ParentParent AS TopParent, tblAccounts.AcctNo, qryParentParent.ParentID as DirParent
FROM tblAccounts INNER JOIN qryParentParent ON tblAccounts.ParentID = qryParentParent.ParentID
The first part of the union gives the DirParent  (direct, or immediate parent) with their direct AcctNos (TopParent = DirParent). The second part gives the AcctNos of those with grandchildren (and great etc.) at each level. I'm then going to link qryNodesAccts with tblRevenue. I could include tblRevenue in qryNodesAccts, but I wanted to test this intermediate step first.

I think this gets me what I want. If so, then I'll base the subform (main form is based on tblParents) on this; I won't need to create a Nodes table. I don't think I need the level, but I may play around with that.

Thanks
Adam

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

Adam-

I think what's confusing me is your original list of fields below doesn't show a link between Clients and Accounts. Is there a ClientID in tblAccounts? If so then the relationships are:

tblParents ——> tblClients ——> tblAccounts
^ |
| |
+——+

Correct?

You need a basic query that joins tblParents LEFT JOIN tblClients LEFT JOIN tblAccounts.

Include that in another query that does a filter on tblParents.ParentParent IS NULL to find the root node. Then include the query again as many times as you have levels. Start with the first query and LEFT JOIN it from query1.ParentID to query2.ParentParent. Continue with a join from query2.ParentID to query3.ParentParent, and so on.

That will "walk" you down the levels, but everything will be horizontal, not vertical. I suppose you could then "pivot" that using a UNION query, and identify the level with a literal.

Is that what you're trying to do with your Nodes table?

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 10:49 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

No. It links to a ParentID in tblParents. In tblParents you have a company (made up) Giant Bank (GB). GB has a number of subsidiaries, See a sample hierarchy below
>GB - ID1
       Acct#ABC001
       Acct#ABC002
   >GB Mortgage - ID2
      Acct#ABC123
       Acct#ABC345
   >GB Auto Finance - ID3
      Acct#DEF123
   >GB Collections - ID4
      Acct#GHI123
   >Medium Bank 1 - ID5
         Acct#ABD345
      >MB1 Mortgage - ID6
             Acct#ABD678
         >MB1 Loan Servicing - ID7
            Acct#ABD999
   >Medium Bank 2 (industry M&As) - ID8
      ...
> represents a Parent with a ParentID autonumber. ParentID would be unique/primary in tblParents. The field I called Parent (which I now realize I can't use in Access/SQL, so I now call ParentParent) is a long that points to another ParentID. For example,:
ParentID; Name; ParentParent
1; GB; null
2; GB Mortgage; 1
3; GB Auto Finance; 1
4; GB Collections; 1
5; Medium Bank 1; 1
6; MB1 Mortgage; 5
7; MB1 Loan Servicing; 6
8; Medium Bank 2; 1

tblNodes would have the following records:
ParentID; AcctNo
5; ABD345
5; ABD678
5; ABD999
6; ABD678
6; ABD999
7; ABD999
1; ABC001
1; ABC001
etc.

Some Parents would only have a single AcctNo.

Regardless of the details of the setup, is this a good approach, to build (VBA) tblNodes via loops through recordsets? Is there a better/easier way?

Currently, there are about 6,000 records in tblAccounts, 4,500 records in tblParents, only a couple of hundred of which are children of other parents. I'm guessing tblNodes would end up with about 8,000 records.

P.S. Your adopted country need some serious PR.

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

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@... [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