Solved

Creating a hierachical database using MS Access 2000

Posted on 2001-07-02
4
230 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

679 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