Solved

Creating a hierachical database using MS Access 2000

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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