Solved

Creating a hierachical database using MS Access 2000

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now