table with two 1:1 relations to another table can't update/add new

[mitenko]

Hi All,

I have a Staff table with two 1:1 relationships to an Address table
(Staff.TempAddress and Staff.PermAddress). Referential
Integrity/Cascading Updates have been defined for both 1:1 relations.

Right now the form I have won't let me make new records. I would very
much like to add new records. I've read the "When can I update data
from a query?" help section and I can't see where I've gone wrong.

The query for the form has a TempAddress table, with only one 1:1
relation defined for it and the same for a PermAddress table. There are
no many:1:many relationships (as far as I can tell).

The nested INNER JOIN part of the query looks like this:

"FROM Address AS PermAddress INNER JOIN (Address AS TempAddress INNER
JOIN Staff ON TempAddress.AddressID = Staff.TempAddress) ON
PermAddress.AddressID = Staff.PermAddress;"

Am I missing something obvious?

 

[Guest]

You need to make sure that the same data is entered in the primary key field
in all three tables at once. This is even if they do not have a temporary
address for example.

You might need to rethink the need for a 1-1 relationship. While it makes
sense that a person would only have one temp and one perm address, they might
not have both at the same time. You might be better off enforcing the
business rule by having a 1-M relationship, but then creating a unique
constraint/unique index to the foreign key fields.

Also instead of using one form, consider using a form with two subforms as
Access will take care of the links.

Further have you considered just one address form with an AddressType field
with Temp and Perm as the values? That would be much cleaner.

 

[Guest]

Sorry. I just reread your post and see that it is in one table and this table
has 2 fields for the addresses. Still my advice still holds some truth
especially about the part of having a form for the Staff table and a subform
for the Address table.

Actually I'd rather see a 1-M relationship with a different record for each
address. That way if someone wants to add something like Vacation Condos, it
wouldn't be much trouble. Also formatting and searching of properly split
addresses into things like StreetAddress, City, State, and Zipcode would be
much better.