Results 1 to 3 of 3
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Customers - More subsidiaries

    Hi all,

    I have run into another design issue which I would like to briefly discuss if possible.

    We have B2B customers (companies). Sometimes, they are big companies with multiple subsidiaries. These subsidiaries sometimes act on their own. There are different people, they are insterested in different stuff and so on.

    Basically, I want to be able to treat subsidiaries as stand alone customers, but at the same time be able to see the global profile with cummulated sales, people and so on.

    For example, we sell to a University. This university has multiple faculties. Faculty 1 is interested in Product A and Person 1 is our go-to guy there. Faculty 2 is interested in Product B and Person 2 is the guy.

    My idea so far:
    • only 1 table - tblCustomers
    • field: MotherID (self join)
    • if MotherID is null, then this is the root company, not a subsidiary
    • if MotherID is not null, then this is a subsidiary of the company with this ID


    What I want to be able to do:
    • When inserting new data (lead, sales order...), I select the customer
    • If this customer does have subsidiaries --- If DCOUNT("*","tblCustomers","MotherID=" & Me.CustomerID) > 0 ---, then show another combo box
    • Then actually link the order to the subsidiary, not the mother



    • In the customer list, I will have multiple choices: VIEW ALL, MOTHERS ONLY, SUBSIDIARIES ONLY
      • a simple matter of SELECT statements



    • I want to be able to aggregate sales results for the mother. I guess I would need multiple queries for that?





    Is my approach to this correct? Thanks for any insights.

    Best regards,
    Tomas

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    That is almost the way I would do it. But instead of leaving the motherID field null if a parent, populate it with the companyID - then a parent is identified as one where companyID=motherID rather than where motherID is null - your dcount would then be >1 and your combo rowsource would (perhaps) need to exclude the mother record - depends if you sell to mother as well as the children

    aggregation then becomes very simple

    your example assumes only two levels of record, mother and child. If the child can also have children, it becomes more complicated and you will need to look at a recursive function to do your aggregation

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Ah, what a neat idea! Thanks a lot, that's great.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 11-01-2020, 01:50 PM
  2. Replies: 6
    Last Post: 06-23-2015, 12:11 PM
  3. Replies: 1
    Last Post: 09-09-2014, 11:29 PM
  4. Keep History of Customers
    By imtiaz703 in forum Access
    Replies: 4
    Last Post: 02-23-2012, 02:57 PM
  5. Help me please w/ combining like customers
    By lsulaurie in forum Queries
    Replies: 2
    Last Post: 06-30-2008, 02:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums