One-to-one relationships

[novoiceleft]

Hello All

I think (hope) this is a simple question.

I have split the main data in my database into two tables to avoid having a single, very large table. (Also, I would eventually like the two tables represented on two separate forms)

So I have t_Main (the primary key is EmployeeID) with some data in it, and and t_Details (the primary key is also EmployeeID) with additional data in it.

The two tables are linked by a one-to-one relationship with referential integrity.

Now it won't let me add a new employee to t_main, is it says I need a record in t_details as well.

Fair enough, but what i would like to achieve is this:

- To be able to add a new employee to t_main
- A record with the same EmployeeId to be automatically created in t_details, with all the other fields in t-details to be blank

What is the best way round the error message?

Many thanks

NoVoice

 

[Keith Nichols]

Hi,

If every item in your tables only appears once, then they should be a single table. I don't know if there is a limit on the number of fields a table can have, but I assume it is a lot.

The only normal reason people have one to one relationships is to protect sensitive data such as having a tblEmployees 1-to-1 with tblSalary where the salary table is only accessible to the certain individuals or groups of users.

The sample database shows a quick 1 to 1. To enter data, use the form which has the second form details filled in via a subform.

There is no reason why you cannot have separate forms to show different parts of your table if that is what you want. For instance, you could have one form for employee addresses and another for employee pay, all from the same table, if you wished.

Hope this is of some help. If you want a more knowledgable response than this, or more general information on one to one relationships, search the tables forum for 'one to one'.

Cheers,

 

[Moniker]

Remember the link between t_Employees and t_Employee_Scores I made for you a bit ago? That's the same thing. With a one-to-one, you can't have a field exist in one table without it existing in the other. You need t_Employees (the "driver" table) to be on the left side of that join.

If you're determined to have a one-to-one relationship and you're allowing new employees to be added through a textbox on a form, then add this to that textbox's AfterUpdate event.

CurrentDb.Execute "INSERT INTO t_Detail (EmployeeID) SELECT t_Main.EmployeeID FROM t_Main LEFT JOIN t_Detail ON t_Main.EmployeeID = t_Detail.EmployeeID WHERE t_Detail.EmployeeID Is Null;"

You'll see that's very similar to "q_NewEmployee_Append" in the first example I sent your direction.

~Moniker

 

[novoiceleft]

Thanks for the replies.

On consideration - I took it all back to the one table in the end - and split the data entry into separate forms.

NoVoice