Using a table twice

[Tyler08]

My database has a table listing employee details which links to a table of documents. Another table lists document versions which links to a table of document version owners.

Since most of the people in the employees table are also in the document owners table any alterations to their details need to be updated twice.

Can anyone tell me how to use a table twice in one database? All attempts to date have resulted in “ambiguous outer joins”.

Regards

Tyler

 

[KenHigg]

Can you post a diagram of the current relationships window?

 

[RuralGuy]

If all document owners are employees then you do not need a document owners table. If not then you still only need one "people" table with an "IsEmployee" field.

 

[Tyler08]

Thanks for the reply. Will the forum let me post a jpg of the Relationships window? If not I'll email it home and post a link to a Photobucket url tonight. My company firewall will not let me access photo sharing web sites.

Or, the structure is like this:

TblEmployee
TblDocument
TblVersion
TblOwner

EmployeeID is the primary key in TblEmployee and the foreign key in TblDocument
DocumentID is the primary key in TblDocument and the foreign key in TblVersion
OwnerID is the primary key in TblOwner and the foreign key in TblVersion


Tyler

 

[boblarson]

You can post a jpg.

 

[Mile-O]

You need three tables.

• One for documents;
• One for employees; and
• One to act as a junction between the two.

tblDocuments
DocumentID (Autonumber) - Primary Key
document property fields

tblEmployees
EmployeeID (Autonumber) - Primary Key
Forename (Text)
Surname (Text)

tblEmployeesToDocuments
EmployeeID (Number) - Foreign Key
DocumentID (Number) - Foreign Key

These two fields together then comprise your composite primary key.

​

 

[KenHigg]

You need three tables.

• One for documents;
• One for employees; and
• One to act as a junction between the two.

tblDocuments
DocumentID (Autonumber) - Primary Key
document property fields

tblEmployees
EmployeeID (Autonumber) - Primary Key
Forename (Text)
Surname (Text)

tblEmployeesToDocuments
EmployeeID (Number) - Foreign Key
DocumentID (Number) - Foreign Key

These two fields together then comprise your composite primary key.

​

You left out the document version / owner parts - ?

 

[Tyler08]

Just found the paperclip icon!

Relationships jpg attached

Tyler

 

[RuralGuy]

IMHO, if documents always have one and only one "owner" then I see no reason not to put that ForeignKey right in the Documents table and eliminate the junction table.

 

[RuralGuy]

Just found the paperclip icon!

Relationships jpg attached

Tyler

When you use the Post Reply button or go to Advanced in the Quick Reply area, scroll down to "Manage Attachments" area to post an attachment.

 

[Tyler08]

TblVersion is there because each document (TblDocument) has many versions (TblVersion) and each version has it's own owner (TblOwner), not always the same person as the Employee.

Tyler

 

[Tyler08]

When you use the Post Reply button or go to Advanced in the Quick Reply area, scroll down to "Manage Attachments" area to post an attachment.

Ok thanks

Tyler

 

[RuralGuy]

TblVersion is there because each document (TblDocument) has many versions (TblVersion) and each version has it's own owner (TblOwner)

Tyler

I think of Version as just another attribute (field) of the document.

 

[boblarson]

Something's not right as the relationship should show a ONE-To-Many relationship between documents and versions. Did you make sure your document ID in the versions table isn't set to Indexed (No Duplicates) and should be Duplicates OK instead.

The same thing goes between Employees and Documents. As it looks right now it looks as if an employee can only have one document, and that shouldn't be the case.

 

[Tyler08]

Ok, I'll look at that. But I still need to display two different names one one form. One name from the Employee table and one from the Owner table.

Tyler

 

[boblarson]

I think of Version as just another attribute (field) of the document.

I would have to disagree on this if you want to track multiple versions of a document, not just have one version at any one time. There are reasons why you would do so. One example is the production illustration documents produced by Boeing. We needed to know which version of the P.I. was in what department at any one time, not just the most up to date version.

 

[boblarson]

Ok, I'll look at that. But I still need to display two different names one one form. One name from the Employee table and one from the Owner table.

Tyler

You can put the table in the relationships TWICE - once to show the employee and once for the owner.

 

[KenHigg]

I think of Version as just another attribute (field) of the document.

I was thinking document has multiple versions, hence a versions tables.

 

[Mile-O]

You left out the document version / owner parts - ?

Did I? Oh well. It was more the IsEmployee checkbox that caught my eye.

Well, there can be a tblVersions with all version infor, linked to a VersionID in the tblDocuments.

 

[RuralGuy]

Ok, I'll look at that. But I still need to display two different names one one form. One name from the Employee table and one from the Owner table.

Tyler

Hi Tyler,
What is the difference between Owner and Employee?