one-to-one relationships

[bonecone]

I'm a bit rusty with Access and can't figure out what I'm doing wrong.

I have three tables

Staff Data
Salary Data
Vacation Data

The ID field of Staff data has a one to one relationship with the ID field of Salary data. The ID field of Salary Data has a one-to-one relationship with the ID field of Vacation Data.

When I enter a record directly into the Staff Data table I am given a plus sign that I can click on to enter a record into the Salary Data table for this staff member. Presumably once I fill out these fields I should be given another plus sign I can click on to fill out the fields in the Vacation Data table.

However, when I key in the Salary Data fields and try tabbing to the next cell I get an error message saying



"You cannot add or change a record because a related record is required in table 'Staff Data'"

What am I missing here?

[June7]

If these are all 1-to-1 why separate tables? How many fields? Will all staff always have data in Salary and Vacation?

Are you the only user of this project? Forms would be better interface for data entry than directly with tables.

[Jacqueline]

Your thoughts on design are correct. A good rule of thumb is if you change subjects you create a new table. This keeps the need to enter redudant data out of your database.

Having said that, it sounds like you are doing data entry directly into the table, you can only have one sub (the plus sign) table connected at a time.

Also, I think you are going to run into problems with the Vacation table. You will have one Employee table but the Vacation would logically be a Many table. The Employee will take many vacation days.

Change that to a many table by using the Auto number to create a primary key where the Employee ID is the Foreign Key in the Vacation table.

Hope this helps
Jacqueline

[June7]

Good points Jacqueline.

Whether or not these tables should be separate depends on what they are for.

OP said relationships are 1-to-1. If Salary is just a single record for the staff current salary, then that is info about the staff. Every staff has a salary so just put field in Staff table for the salary amount. If Vacation is a single record for the staff vacation category, again, info about staff, put in Staff table.

If Salary and Vacation are intended to document history of staff salary and vacation earned/used, then they are 1-to-many relationships and separate tables are needed.

Really need more info.