KenHigg
Registered User
- Local time
- Today, 11:47
- Joined
- Jun 9, 2004
- Messages
- 13,327
Using a table twice (1 Viewer)
- Thread starter Tyler08
- Start date
RuralGuy
AWF VIP
- Local time
- Today, 09:47
- Joined
- Jul 2, 2005
- Messages
- 13,826
I see no reason there could not be many entries in the documents table for each document, all having different version numbers. The document and version number make it unique.
RuralGuy
AWF VIP
- Local time
- Today, 09:47
- Joined
- Jul 2, 2005
- Messages
- 13,826
LOL Thanks Ken, I needed that this morning.About 3 zero's on their w2 -
boblarson
Smeghead
- Local time
- Today, 08:47
- Joined
- Jan 12, 2001
- Messages
- 32,059
If there is more information about the document that just the document name, and the data needs to be repeated for each document entry, if you use the method you describe, then normalization rules come into play around the storing of redundant data and I would use the Versions table to keep from having to store redundant data.
For example, you might be storing the document owner and the document's physical location. The location and the owner may be the same for the document, regardless of the versions and then you would be storing the same info over and over again.
RuralGuy
AWF VIP
- Local time
- Today, 09:47
- Joined
- Jul 2, 2005
- Messages
- 13,826
I think you just moved your issues out to the Version table but I could be wrong. I think the question is "Does more than one version exist at the same time?" If not then your version table makes sense. Maybe I'm just all wet here.
Each Employee can have many Documents and each Document can have many versions. And each Owner can have many Versions.
The form that displays this information shows the Employee name against the Document details and has a subform that lists each document's version details and the name of the Owner of each version.
I hope that makes sense!
Tyler
The form that displays this information shows the Employee name against the Document details and has a subform that lists each document's version details and the name of the Owner of each version.
I hope that makes sense!
Tyler
Attachments
-
Relationships01.JPG
KenHigg
Registered User
- Local time
- Today, 11:47
- Joined
- Jun 9, 2004
- Messages
- 13,327
True - If all you need is a place to put a version number and don't really care about the history then verison can be an attribute of the document entity.
This has evolved into a business requirement issue -
I have put the employee table in the relationships window in an attempt to use it twice but failed to work out how to enter the joins to make the thing work. My frustration at the ambiguous outer joins error led me to start this thread!
Could anyone point me to an example of how to do this?
Thanks
Tyler
Employees are users of the documents. The owners are the people who write the documents and the new revved up versions of the documents. They are employees too! Hence the duplication of information. The db works ok with two seperate tables but I recently had to update information on an employee who was also an owner, twice. Perhaps it would be easier to leave it as it is?
Thanks for your interest anyway.
Tyler
About 3 zero's on their w2 -
Ain't that the truth!!!
RuralGuy
AWF VIP
- Local time
- Today, 09:47
- Joined
- Jul 2, 2005
- Messages
- 13,826
I thought that was the case. The "User" of the document and the "Owner" of the document are simply two ForeignKeys in your Document table that point to the Employee table. The two FK's can be the same if that is the case.
Thanks for your continued interest!
The Owner is the owner of the version and needs to be an FK in the Version table. And that is exactly what is causing my dilemma. If I put the Employee table in the relationships twice and omit the Owner table, I then create a join from the second Employee table to the Version table and Access automatically creates another join from the first Employees table to the Version table. Thus causing an ambiguous outer join.
Tyler
RuralGuy
AWF VIP
- Local time
- Today, 09:47
- Joined
- Jul 2, 2005
- Messages
- 13,826
Agreed! I refer to Allen's site all of the time. I have it from a good source (Allen) then even he refers to the site often. Hope you get your issue sorted. Post back if you have more questions.
- Local time
- Today, 16:47
- Joined
- Sep 12, 2006
- Messages
- 15,640
not read everything, but if you have multiple versions of dcuments then surely you have
owners 1 ------- n documents 1 ------ n document version
surely if you are keeping a document history, then the document versions belong with the documents. why does each version of the document need a different owner.
having said that, you could probably still link the owners table to the document versions if necessary. or you could have another field in the document version called prepared by, to distinguish it from owner
theres probably some redundancy normalization issues
ie a owns b, b owns c, but a does not own c, but that might not be a serious issue for you. you can use the same table multiple times in relationships
eg i have a logistics system where a vehicle is linked to a driver, and a drivers mate, both taken from an employees table
owners 1 ------- n documents 1 ------ n document version
surely if you are keeping a document history, then the document versions belong with the documents. why does each version of the document need a different owner.
having said that, you could probably still link the owners table to the document versions if necessary. or you could have another field in the document version called prepared by, to distinguish it from owner
theres probably some redundancy normalization issues
ie a owns b, b owns c, but a does not own c, but that might not be a serious issue for you. you can use the same table multiple times in relationships
eg i have a logistics system where a vehicle is linked to a driver, and a drivers mate, both taken from an employees table
Because usually, although not always, when a document is revved up the author is a different person from the author of the previous rev. So an Owner is linked to each Version of each Document.
The Versions have to be in a seperate table since a document could be revved up as a result of just a small change in the document text. Without a seperate table all the Document Details fields (there are many more than in my example)would be repeated.
Thanks for your input gemma-the-husky
Tyler
