Hello guys,

I have a table called documents. each document has a code and a version.

I have a layout where I need to display the document evolution: basically some data for each previous version of the same document.

I have created a relationship between documents and documents (this instance is called documentsByCode) and I have tried different way to set the relationship up but no one works so far.

Any ideas about how I have to set the relationship up in order to display for each document its history?

Thanks and have a nice day

Why not have a DocHistory table? It would be a child of Documents. In it would be your version, code, date? and notes.

if it is easier I could make it but I don't like data redundancy so I prefer trying to solve it using a self-relationship. Is it possible?

Thanks

Data redundancy? How is it redundant?

Anyway, maybe the List( ) function might help.

I have created a relationship between documents and documents (this instance is called documentsByCode) and I have tried different way to set the relationship up but no one works so far.

What Barbara suggests is quite valid. If your Documents table has an auto-enter (serial) of DocumentCode then a self-join based upon that code will not work. If you can have two (or more) document records in Documents with same DocumentCode then it should work * to use a self-join on DocumentCode.

* you haven't told us how they are joined or what you have tried. We need that information.

Prefer this ... if the document is in the Document table with a unique DocumentCode and all versions are in a Version table which contains a DocumentCode and they are joined on this code, then you can place a portal of your self-join on your Documents layout and see all versions for the specific document you are viewing.

That is not redundancy because the only thing which would be in both tables is the DocumentCode and those are called keys and are necessary. You can even sort the versions by version number (or could probably leave them in natural sort order of creation). One document ... many versions 99.9% of time equals 1:n (one-to-many) and would indicate another table (for the many side).

@bcooney: I misunderstood what you suggest. With LaRetta explanation now it is clear.

Anyway since the database is already done (and I don't have too much time to upgrade it) I prefer to keep everything into a single table and, I guess, I just found the way to solve my issue (why I haven't thought about that earlier on?).

I have set a self-relationship on document table on

code = code

version < version

In this way for each document I get the previous versions.

Thanks again for your help.