Access 2000 Table Relationship Question

I have a table consisting of people and their attributes. I would
like to create a relationship matrix table that records the
relationships between the people. So the two tables would look
something like this:

tblPeople

*ID, AutoNumber
LastName, Text
FirstName, Text
Height, Text
Weight, Text
etc...

tblRelationshipMatrix

*Person1, Lookup (tblPeople)
*Person2, Lookup (tblPeople)
Relationship, Memo

* - Primary Key

I can't figure out a way to make this table work correctly. I'd like
each combination of two people to be unique. Would anyone have
suggestions on how to do this cleanly?

Thanks,

MW

re: Access 2000 Table Relationship Question

Mason,

Can you give us a scenario that you are trying to manage with relationships?

Plug some data into the structure, and maybe that will help us and you help
yourself. Give us an example of the rows and columns actually representing
the data?
IE what does Person1 lookup. Person2.Lookup? Is the lookup the ID of
People table?

Data really tells the story!

Barry


"Mason" <mason@codemonkey.cc> wrote in message
news:eb2cafcc.0409141119.524274c3@posting.google.c om...[color=blue]
> I have a table consisting of people and their attributes. I would
> like to create a relationship matrix table that records the
> relationships between the people. So the two tables would look
> something like this:
>
> tblPeople
>
> *ID, AutoNumber
> LastName, Text
> FirstName, Text
> Height, Text
> Weight, Text
> etc...
>
> tblRelationshipMatrix
>
> *Person1, Lookup (tblPeople)
> *Person2, Lookup (tblPeople)
> Relationship, Memo
>
> * - Primary Key
>
> I can't figure out a way to make this table work correctly. I'd like
> each combination of two people to be unique. Would anyone have
> suggestions on how to do this cleanly?
>
> Thanks,
>
> MW[/color]

re: Access 2000 Table Relationship Question

mason@codemonkey.cc (Mason) wrote in message news:<eb2cafcc.0409141119.524274c3@posting.google. com>...[color=blue]
> I have a table consisting of people and their attributes. I would
> like to create a relationship matrix table that records the
> relationships between the people. So the two tables would look
> something like this:
>
> tblPeople
>
> *ID, AutoNumber
> LastName, Text
> FirstName, Text
> Height, Text
> Weight, Text
> etc...
>
> tblRelationshipMatrix
>
> *Person1, Lookup (tblPeople)
> *Person2, Lookup (tblPeople)
> Relationship, Memo
>
> * - Primary Key
>
> I can't figure out a way to make this table work correctly. I'd like
> each combination of two people to be unique. Would anyone have
> suggestions on how to do this cleanly?
>
> Thanks,
>
> MW[/color]


Join the tables in a query. Just add Person multiple times and alias it.

re: Access 2000 Table Relationship Question

Sure, Barry. I'll give an example of what's contained in tblPeople.

ID: 1
LastName: Doe
FirstName: John
Height: 5'10"
Weight: 175
DOB: 7/7/77

ID: 2
LastName: Smith
FirstName: Lisa
Height: 5'3"
Weight: 150
DOB: 5/5/75

Now the relationship matrix:

Person1: 1
Person2: 2
Relationship: John thinks Lisa is pretty cool, although she's been
weird lately. Everyone in the office makes fun of her weight,
although John only goes along with it half-heartedly.

Person1: 2
Person2: 1
Relationship: Lisa has overheard John call her fat while he was
laughing it up with other coworkers. Already self-consious about her
weight, she strikes back by saying negative things about his work
habits to anyone in a position of influence.

What I don't want:

Person1: 1
Person2: 1

I also want to enforce referential integrity.

I hope that helps,

MW

"Barry Young" <youngbar@insightbb.com> wrote in message news:<7JM1d.194271$mD.50318@attbi_s02>...[color=blue]
> Mason,
>
> Can you give us a scenario that you are trying to manage with relationships?
>
> Plug some data into the structure, and maybe that will help us and you help
> yourself. Give us an example of the rows and columns actually representing
> the data?
> IE what does Person1 lookup. Person2.Lookup? Is the lookup the ID of
> People table?
>
> Data really tells the story!
>
> Barry
>
>
> "Mason" <mason@codemonkey.cc> wrote in message
> news:eb2cafcc.0409141119.524274c3@posting.google.c om...[color=green]
> > I have a table consisting of people and their attributes. I would
> > like to create a relationship matrix table that records the
> > relationships between the people. So the two tables would look
> > something like this:
> >
> > tblPeople
> >
> > *ID, AutoNumber
> > LastName, Text
> > FirstName, Text
> > Height, Text
> > Weight, Text
> > etc...
> >
> > tblRelationshipMatrix
> >
> > *Person1, Lookup (tblPeople)
> > *Person2, Lookup (tblPeople)
> > Relationship, Memo
> >
> > * - Primary Key
> >
> > I can't figure out a way to make this table work correctly. I'd like
> > each combination of two people to be unique. Would anyone have
> > suggestions on how to do this cleanly?
> >
> > Thanks,
> >
> > MW[/color][/color]