If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Designing a multi-tier (downline) database
Using Access 2000 - I need to design and implement a database to track sales
and clients. The structure of the program I am supporting with this database is set up so that if we contract with person one and then person one sets up a contract with person two who contracts with person three - we can track and report on the "downline". One criteria is to set up a report that shows all top level people with their downline. I have created a table (ClientTable) which has basic contact information and has the key of ClientID. I also have a field named ReferralID in which I intend to store the ClientID of the person who signed him/her up. In creating this application, I will need to ensure that when I create my report for the downline, it shows the downline which is limited to three tiers. I am seeking advice on how to make this happen. I would also like some hints as to how to create the report for this feature. I am comfortable in setting up the remaining criteria for the database, but admit I am far from Access guru. Thanks for any help provided. Jeff |
#2
|
|||
|
|||
Designing a multi-tier (downline) database
Jeff,
I noticed that nobody answered. Hopefully I can be helpful by being direct. For a structure question (which this) you need to describe the process that you are trying to database. and what types of information you are trying to sto Clients? Contracts? In your whole post there were only about three sentences on this, the rest was sidebar stuff (including on how you tried to do it) You also said that you need to display "downline" but didn't tell us what "downline" is, forcing us to make overly vague guesses. Also didn't define whether a "Client" is a person, or an organization (which can have many people) I think that this will probably be pretty simple once you tell us those things. In fact your job of describing this per the above may be the toughest part, and may even answer your own question! Hope that helps, and would be happy to help with that info. Fred "Jeff @ CI" wrote: Using Access 2000 - I need to design and implement a database to track sales and clients. The structure of the program I am supporting with this database is set up so that if we contract with person one and then person one sets up a contract with person two who contracts with person three - we can track and report on the "downline". One criteria is to set up a report that shows all top level people with their downline. I have created a table (ClientTable) which has basic contact information and has the key of ClientID. I also have a field named ReferralID in which I intend to store the ClientID of the person who signed him/her up. In creating this application, I will need to ensure that when I create my report for the downline, it shows the downline which is limited to three tiers. I am seeking advice on how to make this happen. I would also like some hints as to how to create the report for this feature. I am comfortable in setting up the remaining criteria for the database, but admit I am far from Access guru. Thanks for any help provided. Jeff |
#3
|
|||
|
|||
Designing a multi-tier (downline) database
Thanks Fred,
Let me do my best. The database tracks sales (contracts). It also tracks clients (people). Each person can have up to three levels of people under him/her. The database will assist in computing commissions to be paid. The downline is the mulit-tier of ClientA sells package to Client B (Tier 1). Client B then sells package to Client C (Tier 2) who then sells to Client D (Tier 3). The contracts are sold in one of three levels. Level 1, Level 2, Level 3. If Client A buys Level 3, any sales he makes is commissioned at a higher level than if he had bought at Level 2 or Level 1. Commissions vary according to which tier a client in his down line makes a sale. In the above example. Client A would earn commissions for the sale to B. A and B would make money on B selling C. A, B, and C would make money on C selling D a package. The database will need to not only compute commissions, track clients, and contracts, but also provide (amongst others) reports to the clients on how their downline is doing - growing, selling, etc. It is getting to this report that I am most worried about. My level of expertise is that of a self taught, mediocre Access designer with 3 other databases under my belt. Those nearly had me on a strict regimen of "Just for Men" so that my wife would recognize me. Hope this helps. Looking forward to your help with a promise of great appreciation. Jeff ----------------------- "Fred" wrote: Jeff, I noticed that nobody answered. Hopefully I can be helpful by being direct. For a structure question (which this) you need to describe the process that you are trying to database. and what types of information you are trying to sto Clients? Contracts? In your whole post there were only about three sentences on this, the rest was sidebar stuff (including on how you tried to do it) You also said that you need to display "downline" but didn't tell us what "downline" is, forcing us to make overly vague guesses. Also didn't define whether a "Client" is a person, or an organization (which can have many people) I think that this will probably be pretty simple once you tell us those things. In fact your job of describing this per the above may be the toughest part, and may even answer your own question! Hope that helps, and would be happy to help with that info. Fred "Jeff @ CI" wrote: Using Access 2000 - I need to design and implement a database to track sales and clients. The structure of the program I am supporting with this database is set up so that if we contract with person one and then person one sets up a contract with person two who contracts with person three - we can track and report on the "downline". One criteria is to set up a report that shows all top level people with their downline. I have created a table (ClientTable) which has basic contact information and has the key of ClientID. I also have a field named ReferralID in which I intend to store the ClientID of the person who signed him/her up. In creating this application, I will need to ensure that when I create my report for the downline, it shows the downline which is limited to three tiers. I am seeking advice on how to make this happen. I would also like some hints as to how to create the report for this feature. I am comfortable in setting up the remaining criteria for the database, but admit I am far from Access guru. Thanks for any help provided. Jeff |
#4
|
|||
|
|||
Designing a multi-tier (downline) database
Hello Jeff,
First, at the 30,000 ft view level, I think that your process and db would be best visualized as the main data elements beign contracts and with clients being a sort of giant lookup table. This is just a way a viewing a fundamental structure, not to pretend that Clients aren't at the core regarding their importance and your mission. And as a recap, I think that you can have a maximum of 3 levels of contracts which means 4 levels of people. Maybe you use a different name for the bottom three levels ("Client") but it's probably best viewed as 4 levels of people. I suspect that the best solution would be one big contracts table linked to itself multiple times. Similar to a family tree database. I think that there is such an example on Allen Brown's posts or web site as a "family tree" solution. Not being fluent in that, and being Mr. Low Tech/Keep it Simple, your limitation of contracts to three tiers provides an entre' to a lower tech solution which my brain can be comfortable enough with to describe. I'm assuming that initial and top level contract is Level 1, that lower level contracts can't exists without the one(s) above them in place and that contracts can be defined from the top down. (I.E. that you can view it such that lack of levels below it doesn't have to redefine a level 1 as a level 3. (I'm only describing the linking fields, add fields for whatever data that you need to record) First I'd make "tblPeople" table, PK = PeopleID Next I'd make 3 contract tables tblTopLevelContracts including TLContractNum(=PK) TLCSellerID and TLCBuyerID fields tblMidLevelContracts including MLContractNum(=PK), TLCContractNum (linked to this field in tblTopLevelContracts) MLCSellerID and MLCBuyerID fields tblBottomLevelContracts including BLContractNum(=PK), MLCOntractNum (linked to that field in tblMidLevelContracts BLCSellerID and BLCBuyerID and fields SellerID and BuyerID fields contain the PeopleID's of the "sellers" and "buyers", and be linked for sort of a "lookup" function. Now, your main report will be Level1 Contracts which will have a subreport of Level 2 contracts which will have a subreport of Level 3 contracts. Of course, everything will look up and print people's names. You can even sort, group and fileter by people's names, and can make the common (higher level) name for a group be the group header(s). If I misunderstood something about your process, then I hope that parts of this might still be helpful. "Jeff @ CI" wrote: Thanks Fred, Let me do my best. The database tracks sales (contracts). It also tracks clients (people). Each person can have up to three levels of people under him/her. The database will assist in computing commissions to be paid. The downline is the mulit-tier of ClientA sells package to Client B (Tier 1). Client B then sells package to Client C (Tier 2) who then sells to Client D (Tier 3). The contracts are sold in one of three levels. Level 1, Level 2, Level 3. If Client A buys Level 3, any sales he makes is commissioned at a higher level than if he had bought at Level 2 or Level 1. Commissions vary according to which tier a client in his down line makes a sale. In the above example. Client A would earn commissions for the sale to B. A and B would make money on B selling C. A, B, and C would make money on C selling D a package. The database will need to not only compute commissions, track clients, and contracts, but also provide (amongst others) reports to the clients on how their downline is doing - growing, selling, etc. It is getting to this report that I am most worried about. My level of expertise is that of a self taught, mediocre Access designer with 3 other databases under my belt. Those nearly had me on a strict regimen of "Just for Men" so that my wife would recognize me. Hope this helps. Looking forward to your help with a promise of great appreciation. Jeff ----------------------- "Fred" wrote: Jeff, I noticed that nobody answered. Hopefully I can be helpful by being direct. For a structure question (which this) you need to describe the process that you are trying to database. and what types of information you are trying to sto Clients? Contracts? In your whole post there were only about three sentences on this, the rest was sidebar stuff (including on how you tried to do it) You also said that you need to display "downline" but didn't tell us what "downline" is, forcing us to make overly vague guesses. Also didn't define whether a "Client" is a person, or an organization (which can have many people) I think that this will probably be pretty simple once you tell us those things. In fact your job of describing this per the above may be the toughest part, and may even answer your own question! Hope that helps, and would be happy to help with that info. Fred "Jeff @ CI" wrote: Using Access 2000 - I need to design and implement a database to track sales and clients. The structure of the program I am supporting with this database is set up so that if we contract with person one and then person one sets up a contract with person two who contracts with person three - we can track and report on the "downline". One criteria is to set up a report that shows all top level people with their downline. I have created a table (ClientTable) which has basic contact information and has the key of ClientID. I also have a field named ReferralID in which I intend to store the ClientID of the person who signed him/her up. In creating this application, I will need to ensure that when I create my report for the downline, it shows the downline which is limited to three tiers. I am seeking advice on how to make this happen. I would also like some hints as to how to create the report for this feature. I am comfortable in setting up the remaining criteria for the database, but admit I am far from Access guru. Thanks for any help provided. Jeff |
Thread Tools | |
Display Modes | |
|
|