Self referencing

[karl.berger]

I have a table of items that are bilaterally connected to one or more
of each other. For example, Item A may connect with Item B and Item F.
Item B may connect with Item H in addition to the connection it
already has with Item A. So I have a table with fields: Item_Name and
Item_Description. How do I create a table that relates the records
within the Item table?

Later I will want to create a report that shows all the connections
among the Items. For the above example it should produce a report with
these lines:

1. Item A - Item B
2. Item A - Item F
3. Item B - Item A
4. Item B - Item H
5. Item F - Item A

I am using MS Access 2003.

Thanks

 

[vbnetman via AccessMonster.com]

Hi karl,
I'm not really sure I understand what you mean by bilaterally connected.
Could you show a table w/ some data to elaborate?

 

[John Pinback]

By bilateral I mean that if A connects to B then B is also connected to A.
Think of this like electrical wires. The Item table has a list of all
terminal points. The connection table has a list of connections between the
various points.

Items
A
B
C
D
E
F
G
H

Connections
A B
A F
B H

The report should show 6 connections: A-B, A-F, B-H, B-A, F-A, and H-B.

I think I have both tables working but now I don't know how to make the
report list the connections in both directions. I have three fields in the
Connections table: Sys_A, Sys_B, and Description. Maybe code can be writen
that would merge and sort the Sys_A and Sys_B lists and print each item in
the right place. (But I don't know how to do this.)

Thanks,
Karl

 

[John Vinson]

I have a table of items that are bilaterally connected to one or more
of each other. For example, Item A may connect with Item B and Item F.
Item B may connect with Item H in addition to the connection it
already has with Item A. So I have a table with fields: Item_Name and
Item_Description. How do I create a table that relates the records
within the Item table?

Later I will want to create a report that shows all the connections
among the Items. For the above example it should produce a report with
these lines:

1. Item A - Item B
2. Item A - Item F
3. Item B - Item A
4. Item B - Item H
5. Item F - Item A

I am using MS Access 2003.

Thanks

You can get all combinations by adding the Items table to the query
grid TWICE, and the Connections table once. Items.Item to
Connnections.ItemA, and Connections.ItemB to Items_1 - or alias the
table names. E.g.

SELECT ItemsA.Item, ItemsA.otherfield, ItemsB.Item, ItemsB.otherfield
FROM (Items AS ItemsA INNER JOIN Connections
ON ItemsA.Item = Connections.ItemA)
INNER JOIN Items AS ItemsB
ON ItemsB.Item = Connections.ItemB;


John W. Vinson[MVP]