cross referencing uniqueID's

[MaxEd]

I know how to create relationships in Access. But here's my delima.

Let's say I have the following ID's

1
2
3
4
5
6

They are linked by

1 - 2
2 - 3
3 - 4
4 - 5
5 - 6

I haven't figured out how to put this in the table yet either but what I want is when I pull up a list box and the bounded ID is 1, I would see 2 ,3 ,4, 5, 6. Same with ID 2, I would see 1, 3, 4, 5, 6.

First problem is getting them entered into the table which has a PrimaryKey, ID1, ID2.

One solution I have come up with to solving this part of is to use a B-tree design, by having 2 fields in a table, a Head and a Tail. Then write code to search through the table for the referenced values and if there isn't a match to find either the Head or the Tail and fill in the field that is currently empty.


Second problem, like the first one is trying to display this in a listbox in a form. I would think that this would invovle a lot of coding.

Any help is greatly appreciated. Thank you.




-Laughter works miracles.

 

[dhookom]

Is this a real world situation? I can't image what you are attempting to model with this. Can you be a little more descriptive? Is this simply a matter of displaying in the list box all the values that aren't the bound value?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[MaxEd]

I described the earlier part of my question incorrectly. I have a list of RecUID's that I would like to be linked so I have a seperate linking table that stores the PK and FK. Also I want to have one RecUID related to multiple RecUID's so that is what my linking table is used for.

So like if I had a RecUID of 1 and related to 3. Then I have another link between 3 and 4. I append the linking table using the DoCmd.RunSQL macro each time I want to link then RecUIDs. When I view the RecUID in my listbox say RecUID=1, I query the linking table, so I want RecUIDs 3 and 4 show up in my listbox because the structure looks like:

1 - 3
3 - 4

 

[dhookom]

Set the Row Source of the listbox to:
SELECT PKFld
FROM tblLinkingTable
WHERE FKFld = 1
UNION
SELECT FKFld
FROM tblLinkingTable
WHERE PKFld = 1;

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[MaxEd]

It doesn't do what I want though because I want to be able to do the following.

Say

1 - 2
2 - 3
3 - 4


When I list the PKID of 1 I want to see 2,3,4 show up in the listbox. I think the only way to do it is add the links individually.

-Laughter works miracles.

 

[dhookom]

This looks a bit like a tree structure query. You might want to search for articles by Joe Celko on the web.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PWise]

are these id's in the same table
you might try inner join .... on table1.id<>table2.id

 

[MaxEd]

Dhookom,

I think it's a tree structure query too. After some clarification with my team and manager we decided to simplify it by just assigning each UID to only one particular group. So we just added one extra column to the table to handle it. I am interested in learning about the tree structure query though. Thanks for all your help.

-Laughter works miracles.

 

[PHV]

Do a google search for trees in sql

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244