Solved

Creating a hierachical database using MS Access 2000

Posted on 2001-07-02
4
231 Views
Last Modified: 2008-03-17
I am trying to design a database to store information based on a hierachical structure.  The database needs to record which sales rep introduce who and how much comission is due to them.  Example:  3 reps A, B, C.  If A intoduced B, then A gets 15% of B's sales and if B introduces C then B gets 15% of C's sale and A gets 10% of C's sale. I have create a table to record all the details of each rep with a column for [Referred By] to establish a link. I then exacted all the reps whose [Referred by] column was blank into a seperate table. The problem I am having is how to structure the various tables in order to create this relationship between the various levels and identify a path between reps. I was wondering if anyone would be able to assist me on the best table structure in order to achieve this hierachical database.
0
Comment
Question by:mdrake
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 100 total points
ID: 6244859
Hi mdrake,

I'd go with the following method:

Table Reresentatives will hold the information for individual Reresentative.

Table Reresentatives:
RepID as AutoNumber   'Primary Key
FirstName As String
MidName As String
LastName As String
.
.
More info on Reps...
.
.

Table RepsRelations will hold the relationship between the representatives. This table will be used to construct the hierachy.

Table RepsRelations:
RelID As AutoNumber   'PK
SeniorRep As Number   'Linked to RepID in table Reresentatives
JuniorRep As Number   'Linked to RepID in table Reresentatives

Table Bonuses will hold the information regarding each level's bonus (15%, 10% and so on).

Table Bonuses:
BonusID As AutoNumber  'PK
Level As Number        '1 for level 1, 2 for level 2 etc.
BonusFactor As Number  '0.15, 0.10 etc., according to Level (see remark)

Remark: Note that BonusFactor has to be treated backwords. A, B and C are Reps. Relationship between B and C is Level 1 (15%), relationship between A and B is level 2 (10%) and so on.

To test the hierachy for each transaction, you'll have to query for each SeniorRep to find a match, per transaction:

Say that Rep C made a transaction. To get the commision for each rep, you have to loop throuth table RepsRelations.

For each Rep, use a query like the following one:
SELECT Transactions.*, RepsRelations.*, Reresentatives,* FROM Transactions
(INNER JOIN RepsRelations ON Transactions.[Referred By] = RepsRelations.JuniorRep
(INNER JOIN Reresentatives ON RepsRelations.JuniorRep =  Reresentatives.RepID))
WHERE Transactions.[Referred By] = [Me]![RepRef]

Hope this helps,

Nosterdamus
0
 
LVL 11

Expert Comment

by:joekendall
ID: 6511051
mdrake:

Did Nosterdamus' answer help? If not, please post again.

Thanks!

Joe
0
 

Expert Comment

by:amp072397
ID: 6743392
mdrake has not returned to EE since he posted the question. Points will be awarded to Nost' unless mdrake returns with a viable objections.

thanks!
amp
community support moderator
0
 

Expert Comment

by:amp072397
ID: 6783194
Points to Nost'.

amp
community support moderator
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question