Creating a hierachical database using MS Access 2000

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.
Who is Participating?
NosterdamusConnect With a Mentor Commented:
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,


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


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

community support moderator
Points to Nost'.

community support moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.