Easy newbie question

[pontuse]

Sorry for being such a newbie to Access, but I would really like to know if and how I can solve my problem:

I need to set up a list with names and then connect them in pairs, and be able to make a query on all fields that lists the names and their mate. It is for a project with schools.

My list of names could look like this in a table:

Steve, New York, US
Martin, Los Angeles, US
Adam, San Diego, US

I would then like to connect each person with one other person, and when I search for “New York” I will get all people in New York listed together with the people with whom they are connected. Like this:

Steve, New York, US - Carl, Miami, US


Paul, New York, US - Matt, Maine, US
etc.

Please let me know how to do. Thanks.

[NTC]

now way to answer

Steve, New York, US - Carl, Miami, US
Paul, New York, US - Matt, Maine, US

where is the linked named stored and how is it linked?

i.e.
- Carl, Miami, US
- Matt, Maine, US

how does the db know that Carl links to Steve??

[pontuse]

Thanks, exactly, that is my problem. Is there a way in Access to create a relation between different fields? It would be enough just to have a column that said "Connect to ID", so each ID could connect with another. Or would it help to put them in different tables...?

[Nijal]

Here is one solution to what I think you're asking -

Names in one table, links in another:

tblNames:
EID(autonumber)
EName (text)
ELocation (text)

tblLinks:
EID (long integer)
EIDlink (long integer)

For each person you want to link, put first ID in tblLinks.EID and the second ID in tblLinks.EIDlink. You may want to put a reversed matching entry to reciprocate the friendship/relationship.

Sample Data:

tblNames:
1, John, NY
2, Dave, WA
3, Michael, MA
4, Richard, MA

tblLinks:
1, 2
2, 1
1, 3
3, 1
3, 4
4, 3

You will see from this that John is friends with Dave and Michael
Then run a query to show People from New York on the left (more than once if more than one friend) and friends on the right:

"SELECT tblENames.EName, tblENames.ELocation, tblENames_1.EName, tblENames_1.ELocation
FROM tblENames AS tblENames_1 INNER JOIN (tblENames INNER JOIN tblLinks ON tblENames.EID = tblLinks.EID) ON tblENames_1.EID = tblLinks.EIDlink
WHERE (((tblENames.ELocation)='NY'));"
Hope this helps
Nijal

[pontuse]

Thanks so much Nijal, it works perfectly! Just like I wanted! I appreciate it very much.