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
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS"
"Brent Taylor via AccessMonster.com" <forum@nospam.AccessMonster.com> wrote
in message news:7efacfe252de4bc5a2c1e47dd1c14ecf@AccessMonste r.com...[color=blue]
>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[/color]
