non-hierarchical database

[chriso]

Hi, I'm trying to build a database for a document management system at work,
each document can potentially be related to any other document in the DB.
Also, if you are looking at Doc 'A' details and add a relationship to Doc
'B', when you call up Doc 'B' details it should already show the relationship
to Doc 'A' without it having to be entered again.

the main table covers the document data (title etc.) with an auto number
index, a second table stores the relationships using the auto index number of
the documents and a relationship index as well (index, parent doc, daughter
doc). I've built forms that display the doc deatils and the relationships but
have hit a brick wall when it comes to maintaining the relationships. I can't
seem to engineer a way of reviewing a particular relationship (from the
document details form) and changing the related docs. Even a meaningful way
of calling a particular entry and editing it would be good.

any suggestions would be appreciated! Cheers

 

[KARL DEWEY]

You can relate a table to itself.
Doc_Table --
DocID - autonumber - primary key
DocNum - text
DocTitle - text
Category - text
Rev - text
Reference - number - integer - FK related to DocID

In the relationship window put the table twice (Access adds a sufix to the
second instance of the table like Table1_1).

Use a form/subform to show relationship.

 

[Amy Blankenship]

You can relate a table to itself.
Doc_Table --
DocID - autonumber - primary key
DocNum - text
DocTitle - text
Category - text
Rev - text
Reference - number - integer - FK related to DocID

In the relationship window put the table twice (Access adds a sufix to the
second instance of the table like Table1_1).

Use a form/subform to show relationship.

I think that will only work for a one-way relationship. Might I suggest
something like this:

Documents
DocID
DocTitle
DocFileName
etc...

DocumentReferences
FromDocID
ToDocID

Then, in a subform, you can have a query something like this:
SELECT FromDocID AS Source, DocID, DocTitle, 'from' AS LinkType FROM
Documents INNER JOIN
DocumentReferences ON DocID=FromDocID
UNION
SELECT ToDocID AS Source, DocID, DocTitle, 'to' AS LinkType FROM Documents
INNER JOIN
DocumentReferences ON DocID=ToDocID;

Use DocID in the main form as the LinkMaster and Source in the child form as
LinkChild. Note that this will only work for display purposes. You'll
probably need to add at least one popup to add/edit/delete values.

HTH;

Amy

 

[chriso]

Thanks to the both of you for the help, managed to get the thing working in
the end!

I ended up doing a bit of a mix of both - used a relationships table to
relate the doc list to itself but also dropped the doc index number - had a
bit of a "revelation" (of the d'oh! variety ;oD) when I realised that the doc
number was unique anyway, using this in the relationship table meant that
editing the relationships was easy as the entries in the table had a direct
relationship to the docs that the user could understand. By making the field
a combo list based on the main doc table made maintenance a breeze!

Thanks again, you both sent in the right direction.

Cheers

Chris