reference document records to one another in the same table

[Judy]

I have a database with approx 300 records. These are process and procedural
documents and can be associated with others in the database. For example.
Procedure A could reference something in Procedure G; and that would mean
that I need to be sure that the Procedure G document would also have a
reference in it referencing back to Procedure A.

I currently have one table with all the document names and numbers in it.
I'm just not sure how to get them to reference one another within the same
able; or, do I need a separate table with all the document names in it?

I have fields:
DocID
DocName

Ultimately, I need to be able to produce a report showing the document names
and what other documents have been referenced in it.

Thanks for the assistance.
Judy

 

[John Spencer]

Since you could have a document reference multiple other documents you need an
additional table.

tblReferences
DocIDA
DocIDB

Then to get the references you would use a query with your Procedure database
in the query two times and join the one copy of that to the first field and
the other copy to the second field. You would only need one record to
describe the cross-reference since the directionality of the reference is not
important. If the directionality were important, then you would need two
records for each reference one for each direction A to B and B to A.

SELECT A.*, B.*
FROM tProcs as A INNER JOIN tblReferences
ON A.DocID = tblReferences.DocIDA
INNER JOIN tProcs as B ON
B.DocID = tblReferences.DocIDB


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Judy]

John, Thanks for your quick response. I've been trying to write the query
below in SQL as you wrote it. But I'm getting an error with it.

Here is how I interpreted your query and I also provided you with the error
message I'm getting. I'm sure I have probably interpreted yours incorrectly
and I truly appreciate your help. My 2 table names are Document and
References. The ID field in both tables is named ControlNo

SELECT Document.*,References.*
FROM Document as A INNER JOIN References
ON Document.ControlNo = References.ControlNo
INNER JOIN Document as References ON
References.ControlNo = References.ControlNo

Error Msg: Syntax error (missing operator) in query experssion
'Document.ControlNo = References.ControlNo
INNER JOIN Document as References ON
References.ControlNo - References.ControlN'.

 

[John Spencer]

Assumption: References has two fields that contain a ControlNo (two different
ones in any record) that specify Document A is related to Document C (and,
conversely, Document C is related to Document A).

Your SQL should look like the following.
SELECT A.*,B.*
FROM (Document as A INNER JOIN References
ON A.ControlNo = References.ControlNoA)
INNER JOIN Document as B ON
B.ControlNo = References.ControlNoB

If you work in the Query Design view
== Add the Document table two times
== Add the References table one time
== Drag from ControlNo in one Document table to the first ControlNo in References
== Drag from ControlNo in second Document table to the other ControlNo in
References
== Select the fields from each Document table you wish to display

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County