Brent,
It concept, it is a hierarchical data problem. Others here have solutions
different from the three I know about. Each solution to this problem has
its benefits and drawbacks. Study each one to decide which works best for
you.
Solution 1:
Single table joined to itself and modeling a one-to-many relationship
between a row in the table and other related rows in the same table.
Solution 2:
Two tables, one is the detailed information about each thing in the
hierarchy (sales representative in your case) and the other is a two column
list of primary keys from the first table that lists which thing in the
first table is related to the other and modeling a many-to-many relationship
between a row in the first table and another row in the first table.
Solution 3:
Two tables like Solution 2 but with as many columns as are needed in Table 2
to list each level of the hierarchy. This can make navigating the hierarchy
easier in some cases.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"
"Brent Taylor via AccessMonster.com" <fo***@nospam.AccessMonster.com> wrote
in message news:7e******************************@AccessMonste r.com...
I am working on a MS Access database to track the sales of a 3 Tier Multi-
level Marketing system.
I have created a table to track a new RepID and a SponsorID. I have
created
a query that will show the Sponsor and all of their reps but I am not sure
how to show the reps that the second tier has sponsored.
Example:
RepID 0011 SponsorID 0010
RepID 0012 SponsorID 0010
RepID 0013 SponsorID 0010
The above is easy but I am not sure how to get:
RepID 0014 SponsorID 0013 and his SponsorID 0010
Does anyone have any idea of a good way to do this?
Thank you,
Brent