Ok, I've made an empty database and experimented a bit with
relationships and integrity, and i think i just had my Eureka!
moment. :-)

I see why it worked before, and that is an acceptable way to make it
work again, to me.
I made a simple database, two tables with referential integrity
enforced.
Made a simple query, tag.* and tag_diverse.* and started entering
data. as soon as I started entering data in any of the tag_diverse
fields a record was created in that table, with the correct tagnumber.
So all I need to do is make sure the users enter all the relevant data
(in all tables) before saving the record.

This may be very basic stuff for you guys, and laugh if you want, but
I think I get it now. :-)

Thank you guys!


On 4 Mai, 08:46, atledreier wrote:
The Tag_diverse table is a table of data that is design specific, and
not meant for the client database at all. Like I stated before, the
structure of most of the tables is untouchable.

Any thoughts on how I can solve the other tables?

On 3 Mai, 18:31, John W. Vinson
wrote:



On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier
wrote:

Short explanation to the different tables and what I want:

Tag. This is the main table with all the tag numbers and some related
information like category and function code
Tag_Diverse: This is misc information about tags. all tags should have
at least some information in this table, hence the 1:1 relationship
Cable: All tags with [Tag_cat]="C" should have a record in this table.
Tag_format: All tags with [tag_cat]="BR", "IN" or "TE" should have a
record in this table.
The Doc_ref thing works fine.

I think you may be misunderstanding how one to one relationships work.

A relationship will PREVENT adding a record to Tag_Diverse unless its linking
field exists in Tag. The relationship will not *create* a record in
Tag_Diverse. The relationship is one way; the Tag table is still the "master"
table, and strictly speaking the relationship should be called a "One to (zero
or one)" relationship. It's a chicken or egg problem; before a Tag record has
been created and saved to disk, you *cannot* have a Tag_Diverse record because
referential integrity would prevent its existance. There will always be a
moment when you have *a "chicken which has not yet laid an egg"!

Another issue is the 1 to 1 relationship. Is it in fact the case that the
Tag_Diverse table will contain one, and only one, NEVER ANY MORE, records of
"misc information"? If so, why not just add the fields in Tag_Diverse into Tag
and enforce that at least some of them are non-null?

Similar questions about the Cable and Tag_Format tables, which may be more
legitimate one to one "subclassing" tables.

--

* * * * * * *John W. Vinson [MVP]