Actually Joe has a book out specifically on this:
http://www.amazon.com/exec/obidos/AS...356133-3944959
"MGFoster" <me@privacy.com> wrote in message
news:K_HDd.868$Ii4.153@newsread3.news.pas.earthlin k.net...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> You've described it well. It sounds like an adjacency list model or
> nested set model would be the way to set up your, relevant, tables.
> This is also called a Bill of Materials (BOM) model. The most famous
> book on adjacency list & nested sets is Joe Celko's. See his book _Joe
> Celko's SQL For Smarties: Advanced SQL Programming_. There are also
> many discussions of all three models in SQL forums and this newsgroup.
> Try reading the Google group archives in these newsgroups:
>
> comp.databases.ms-access
> microsoft.public.sqlserver.programming
>
> To understand these models it would be a good idea to read any
> programming books that include discussions of data structures: linked
> lists, trees, especially.
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQd9MHoechKqOuFEgEQLWDQCeJKdb+wICgcL1+h0IMVszka 1m04UAniFK
> FAO0QB2bV8/KCnreknuJEBpg
> =mj35
> -----END PGP SIGNATURE-----
>
>
> Elliot wrote:[color=green]
>> I'm having trouble structuring the data in an investment database. The
>> basics are:
>>
>> * Investors invest in Deals
>> * Investors can be either Individuals or Entities
>> * Entities may or may not contain individual Members
>> * Entity Members can be either Individuals or Entities
>> * An Investor in one Deal may be a Member of an Entity that is an
>> Investor in another (or even the same) Deal.
>> * There is no limit to the depth of this "tree"
>>
>> My issue is how to structure the data to support these relationships
>> while enabling flexibility in the depth of display for Investors - i.e.
>> show just the highest-level Investor or drill down N levels in the
>> "tree".
>>
>> I'm a novice with Access, so I apologize if I'm not explaining the
>> problem clearly. Any advice would be greatly appreciated.[/color][/color]
