By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Access 2000 Table Relationship Question

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <ma***@codemonkey.cc> wrote in message
news:eb**************************@posting.google.c om...
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

Nov 13 '05 #2

P: n/a
ma***@codemonkey.cc (Mason) wrote in message news:<eb**************************@posting.google. com>...
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

Join the tables in a query. Just add Person multiple times and alias it.
Nov 13 '05 #3

P: n/a
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" <yo******@insightbb.com> wrote in message news:<7JM1d.194271$mD.50318@attbi_s02>...
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" <ma***@codemonkey.cc> wrote in message
news:eb**************************@posting.google.c om...
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

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.