Relationships decision to be made...

[MLH]

I have two tables with a 1 to many relationship between the
parent table and the child table. The parent table is tblOwners
and contains vehicle owner records. The child table is tblVehicleJobs
and contains records of towed vehicles that belong to the owners
in tblOwners.

Trouble is, towing company does not always know the ownership
info when towing a vehicle into the shop. So, its impossible to enter
the owner until after-the-fact when the ownership info is obtained
from the state DMV. I really do wish I could make the settings in
relationships window to enforce referential integrity. Not only is it
good policy, it gives me the advantage of being able to implement
cascading updates/deletes.

Any way to get around this. The vehicle info is ALWAYS known -
make, model, year, etc... But the owner info doesn't come until
later. Hoping someone has worked around this in the past. I'm not
too keen on the 'bogus owner record' deal, as REAL owner info
will eventually be known.

[pietlinden]

Don't know any way around it but creating a dummy "unknown owner"
record in the parent table. Then once you know the real owner, you can
"transfer" ownership to the new record. Since you're just adding a new
owner record and then transferring the vehicle from "unknown" it should
work fine.

[MGFoster]

MLH wrote:

I have two tables with a 1 to many relationship between the
parent table and the child table. The parent table is tblOwners
and contains vehicle owner records. The child table is tblVehicleJobs
and contains records of towed vehicles that belong to the owners
in tblOwners.

Trouble is, towing company does not always know the ownership
info when towing a vehicle into the shop. So, its impossible to enter
the owner until after-the-fact when the ownership info is obtained
from the state DMV. I really do wish I could make the settings in
relationships window to enforce referential integrity. Not only is it
good policy, it gives me the advantage of being able to implement
cascading updates/deletes.

Any way to get around this. The vehicle info is ALWAYS known -
make, model, year, etc... But the owner info doesn't come until
later. Hoping someone has worked around this in the past. I'm not
too keen on the 'bogus owner record' deal, as REAL owner info
will eventually be known.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

"Unknown" place holder is the standard method to use when the data is
unknown. Is this Owner identification the Primary Key (PK) and that's
why you don't want to use it - 'cuz of possible duplicates?

What I do in this situation, multiple unknowns, not duplicates, is
create a report that lists all UNKNOWNs that need to be known & run that
in a timely manner. You could also just run the report's query
everytime the application is opened & have a user notification
indication of either the number of Unknown owners or the vehicle IDs
that have missing owner info.

If it comes to many vehicles having the same owner you'll have to have a
"merge" utility that will merge all the unknown owners you created for
the vehicles, into the known owner record. Not that hard just, change
the OwnerID in the vehicles table to the now-known-owner's OwnerID and
delete the old unknown owner records.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzYmSoechKqOuFEgEQJbnQCffp+EZbm+aUSWGubYiIqfVb TyLjgAoMRx
T6kwSneZ1zDg5DGUYv8knuWp
=dlVO
-----END PGP SIGNATURE-----

[MacDermott]

This seems to be a little-known feature of referential integrity.
It is perfectly acceptable for the Foreign Key in a child table to contain a
null value.
This indicates that it is not (yet) associated with any record in the master
table.

One way to achieve this is to make sure there is no default value assigned
for that field in its definition in the child table.

Of course, other constraints may keep you from saving this record, but you
can deal with those outside of the question of referential integrity.

HTH
- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:eo********************************@4ax.com...

I have two tables with a 1 to many relationship between the
parent table and the child table. The parent table is tblOwners
and contains vehicle owner records. The child table is tblVehicleJobs
and contains records of towed vehicles that belong to the owners
in tblOwners.

Trouble is, towing company does not always know the ownership
info when towing a vehicle into the shop. So, its impossible to enter
the owner until after-the-fact when the ownership info is obtained
from the state DMV. I really do wish I could make the settings in
relationships window to enforce referential integrity. Not only is it
good policy, it gives me the advantage of being able to implement
cascading updates/deletes.

Any way to get around this. The vehicle info is ALWAYS known -
make, model, year, etc... But the owner info doesn't come until
later. Hoping someone has worked around this in the past. I'm not
too keen on the 'bogus owner record' deal, as REAL owner info
will eventually be known.

[MLH]

<censored>
Well, Turtle, you hit it dead on the head. When I created the link
field in the child table, it was set up to automatically set a value
of zero in that field. After changing that, Access allowed records
to be added - no problem. I would like to circumvent Access using
zero as default value for long integer field types added to tables.
Dunno how to do that.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx

This seems to be a little-known feature of referential integrity.
It is perfectly acceptable for the Foreign Key in a child table to contain a
null value.
This indicates that it is not (yet) associated with any record in the master
table.

One way to achieve this is to make sure there is no default value assigned
for that field in its definition in the child table.

Of course, other constraints may keep you from saving this record, but you
can deal with those outside of the question of referential integrity.

HTH
- Turtle

<snip>

[MacDermott]

I don't know any way to do that automatically, either.
But you only need to set the DefaultValue for each field once, when you
create it.

Just another confirmation of my contention that it's well to allow extra
time at the beginning for table design and implementation. It will pay off
many times over later.

- Turtle
"MLH" <CR**@NorthState.net> wrote in message
news:65********************************@4ax.com...

<censored>
Well, Turtle, you hit it dead on the head. When I created the link
field in the child table, it was set up to automatically set a value
of zero in that field. After changing that, Access allowed records
to be added - no problem. I would like to circumvent Access using
zero as default value for long integer field types added to tables.
Dunno how to do that.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx

This seems to be a little-known feature of referential integrity.
It is perfectly acceptable for the Foreign Key in a child table to contain anull value.
This indicates that it is not (yet) associated with any record in the mastertable.

One way to achieve this is to make sure there is no default value assignedfor that field in its definition in the child table.

Of course, other constraints may keep you from saving this record, but youcan deal with those outside of the question of referential integrity.

HTH
- Turtle

<snip>