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