Many to Many record links within 1 table

[khoegen]

I've created a Table and wish to create links between the records wtihin the table.

Background: The table consists of a list of Documents. These documents contain references to other documents...which are also listed within this same table. I would like to create a form which will allow me to call up any one 'parent' document, and have it list all the associated child documents.

Issues:
1. The parent and child records are located within the same table
2. Any one record within the table can have zero, 1, or many links to other records within the same table, which in turn can have links to other records...and so on.
3. Any one record can be both a parent and/or a child

Any assistance is appreciated

[comteck]

The best way to do it is to create a separate table for your child documents. You then create a link between the 2 tables using an "ID" field. The field should have the same name on both tables, and be the primary key on the main table. Also, this field's datatype should be "autonumber" on the main table, but "number" on the child table.

For example, if you called this field, "RecordID", this would be the primary key on the main table, and its datatype would be autonumber. On the child table, you would create a field with the same name, "RecordID", and its datatype would be number.

On the child table, you can still make this "RecordID" a primary key, but you will need a 2nd primary key for that table if you've got more than one document with the same ID because a primary key cannot be duplicated. A second primary key might be something like "ChildID".

Then finally, on your form, you create a subform. The main part of the form takes information from your main table, and the subform takes info from your child table. Of course, rthe child form will also have to be linked to the main form.

Hope this helped.
comteck