Null values in one-to-many relationship

[Guest]

I have a persons table with an "AddressID" field which is a foreign key into
the "address" table. The default addressid is Null. When I want to list all
people + their addresses, an outer join works nicely. However, even though
this outer join is an updateable recordset, I can only change an already
exisiting addressid, or add a new record including the address info. I
canNOT change a NULL addressid.

I tried using a form that was a view over the persons table linked to a
subform being a view over the address table and still no luck (I get an error
when trying to edit addresses that are null in the persons table).

What type of query (or forms/subforms) can I do that will allow me to add an
address to a person with a null address? I could do it in VB, but I'm trying
to avoid that situation if at all possible.

Thanks in advance,
Robbie

 

[Guest]

Instead of a Null can you use a dash? It should be updateable then as it
will not be null.

 

[Guest]

Thanks for the quick reply. Unfortunately, the field is a long integer and I
am enforcing foreign key constraints in my database (hence I can't just use 0
or -1). In fact, let's say I create a "dummy" address with ID 1 and use
that ID instead of Null. When I change one person's address that has ID 1,
that would change everybody's address that was pointing to ID 1. Of course,
this could be solved with some programming, but that is what I am
purposefully avoiding.

Any other ideas?

Robbie

 

[John Smith]

The main/subform is the way to go on this, but the way that you describe you
are dealing with people belonging to addresses rather than addresses belonging
to people. You need to make address the main form and person the sub form for
it to work. You could use a tab control if you want to hide this fact from
the users. Alternatively you would need to link to the address using a person
ID foreign key in the address table.

HTH
John

 

[Guest]

That makes perfect sense. Thanks for pointing that out. Our data model is
such that we are assuming a Person has a single address, but that an address
might be shared by multiple people in our table. We will consider allowing
duplicate addresses so that we can reverse the relationship, even though it
isn't the best fit to our model.

Regards,
Robbie

 

[John Smith]

Well potentially you have a many-to-many relationship between people and
addresses but making that into a nice UI is likely to be tricky! I'd be
inclined to keep the model to the simplest that meets the users needs.

HTH
John