Referencing records in same table

[teddymines]

I want to create a collection that allows records to reference other records in the same table. This is similar to books in a library referencing other books. I am stumped.

I'd like to eventually be able to get a report that looks like this:
Book1 is related to Book2, Book3, Book4
Book2 is related to Book1, Book4
Book3 is not related
Book4 is related to Book1, Book2

Here is what I have so far:

tblProcesses:
index autonumber (primary key)
name text

tblReferences:
from number (points to the index)
to number (points to the index)

The relationships in tblReferences would look like this:

from to
----------
1 2
1 3
1 5
2 3
3 6
3 7

How do I set up the relationships? Also, what would be an efficient way to manage tblReferences? As records in tblProcesses are added, the selection list in tblReferences:to should also grow.

Any input would be appreciated. Thanks.

 

[Trainingjason]

One approach is use list boxes, with initially the form load event. Code similar to the following, where for example I have country and city in the same customer table.

Basically load up the list box using similar code
Form_Load()
Me.FirstListboxname.RowSource = _
"SELECT DISTINCT State FROM tblCustomers " & _
"WHERE strCountry IS NOT NULL"
End sub

Private Sub FirstListBoxName_AfterUpdate()
With Me.SecondListboxname
.RowSource = _
"SELECT DISTINCT strCity FROM tblCustomers " & _
"WHERE strCountry = " & Chr(34) & Me.FirstListboxname & Chr(34)
.Requery
End With
End Sub

I hope this gives you some ideas

Jason