Linking two tables by one-to-one

[ansar.abdulhai]

Hello All,

I am newbie to Access.

I have a manual form which is filled up by 2 different persons. 1st person fills out his/her related fields and takes that form to 2nd person to allow him/her to fill out his/her related fields. The manual form cannot be split into two forms.

My Approach:
I am creating separate tables for both persons related fields. Also, I will be including common fields in both the tables. Now, these tables are stand-alone. My problem is how do I establish a relationship between these tables? And the process is that after 1st person fills out his/her fields then only can the 2nd person fill his/her related fields and then all the info from these 2 persons go together to a third person to get entered into another software.

Any help would be greatly appreciated.

 

[DevastatioN]

I don't believe a one-to-one relationship is what you are looking for, these setups are very rare, and usually only used to decrease the amount of null values in a record, or for security.

You should have a single table with all of the required fields.

You can split up the information into separate forms. User1 has a form that has only the fields they require to fill out. The second user has a form that has only his required fields.

User3 has a form that has all of the information visible.

A common rule of databases is that common fields should never be kept in seperate tables, because they won't be easiuly joinable later, and it's data duplication.

 

[The_Doc_Man]

This question opens up whole cans of worms.

First, I agree with DevastatioN that generally you don't really want split tables. You can have split access by having multiple forms. This reduces confusion later - which you would have had when you joined the corresponding entries from your two tables.

Second, there are really rare cases for which split tables make sense, usually having to do with incredibly strict security requirements. We are talking here about privacy laws or national defense databases or such as that.

Third, this statement has serious potential to drag you through the dirt:

Also, I will be including common fields in both the tables.

No, NEVER should you do that. EVER. To do this right, you must have a SINGLE field to use to join the split tables. Nothing else should ever be kept in common between the two tables. You can search this forum for discussions of surrogate keys, meaningless keys, and autonumber keys.

To understand why you should do it this way, you need to study up on the topic of database normalization. Search this forum, Access help, and Wikipedia.ORG to see some introductory articles on the subject. Splitting tables with a 1/1 join when there is no security reason for the split will ALWAYS lead you to confusion unless you are incredibly careful. The duplication of data will guarantee you more headaches than you could possibly want to handle in terms of data maintenance.

 

[ansar.abdulhai]

Thanks Grand Pa..... I got it. I will be keeping only one table and have the common fields appear only once.

I also want to know if I have a general form which is used every time, do I need to link the corresponding table behind scenes with the main table of the database. (Or) Can a database have a stand alone table and having this can we retrieve data from it even though it is not linked to the database? I am sounding funny here...... Say I have all the tables in the database linked some way or the other to the main table. But I also have one more table which I don't want to link it with the main table and keep it for general use. Can I retrieve data from it in logical way from that table by writing a query or so..... Please advise.