I have a few tables in my database, all 1 to 1 with the master table.

I have a query that gather much of the data into one large
datasheet.The problem is I cannot add records through this query.
I get a message that I need a related record in the child table for it
to work.

Am I right in assuming I need to add the record in the master database
first, then the inegrity check will create the record inthe child
databases, and THEN I can add data through my datasheet?

On Thu, 29 Apr 2010 01:51:32 -0700 (PDT), atledreier <(E-Mail Removed)>
wrote:

>I have a few tables in my database, all 1 to 1 with the master table.

That's an *extremely* unusual design. What are these tables? Why do you need
one to one relationships? Are you perhaps trying to get around the 255 field
limit? If so, you are on the wrong track!

>I have a query that gather much of the data into one large
>datasheet.The problem is I cannot add records through this query.
>I get a message that I need a related record in the child table for it
>to work.

I'm guessing that the messages is that you need a related record in the PARENT
table, not the child?

>Am I right in assuming I need to add the record in the master database
>first, then the inegrity check will create the record inthe child
>databases, and THEN I can add data through my datasheet?

The integrity check will *prevent* the addition of an invalid record but no,
it will not automagically create a new child record.

I think you're really on the wrong track, and probably are "committing
spreadsheet". Please post a description of your tables; I'm sure there's a
better way to accomplish what you want done.
--

John W. Vinson [MVP]

Thank you guys. I'll give you more details.

I am in the design phase of a project, and my client wants me to
import all my data when all is as-built. My client has a database for
all his needed info, so I just used that database structure as a basis
for my design. I also added some new tables for design specific data,
and some other stuff that won't go to my client.

There is the master tag table. This holds the main list of records.
Then supporting tables to give more information about different kinds
of tags.

First, the tables: I've not listed all the fields, but the most
relevant ones.

Tag:
*Tag
Description
Tag_Cat (category)
Function_code
....

Tag_misc:
*tag
manufacturer
misc fields....

Tag_Failure_Mode:
*tag
Fail_code
Fail_mode
.....

Tag_Format_in/_br_/te
*tag
cal_low
cal_high
IP-grade
.....


The tag_misc table is 1 to 1. All tags should have information in the
tag_misc table. I know I then should have all that information in one
table, but since the original client structure needs to be maintained
I chose that design. And this has worked for a while, so I thought
it'd still work.

The tag_failure_mode table should contain data for most tags, but not
all. I chose to have a record in there for all tags regardless, as i
thought that may be easier to maintain. This is based on the tag!
function_code field.

The tag_format table should have data for tags of certain tag!tag_cat
values (br, in and te type tags). Once again, most of my tags are in
this category, so I chose to have all tags in this table too.

So I have used a query to gather up all the relevant fields for my
users (they are conservative and like their big excel-like datasheets)
in one big list.
My client made a change in his underlying database recently, so I
thought I'd take the opportunity to re-work my database as well.
trying to get rid of the big queries and relying more on forms, and
also finally linking the database (we've all been working on the same
file up until now).
So after these changes I get the message that a relevant record needs
to exist in the CHILD database, the Tag_failure_mode table in my
instance.
If it is like you say then I find it strange that this has worked
before, really. I see how it would work like you say, but then how did
it work this long? It was after I linked the database and added the
Tag_failure_mode table it stopped working. I also made many other
changes, so I can't tell what I did to break it.

Any tips on how to restructure or set stuff up to make this as smooth
as possible?

- Atle

On 29 apr, 18:43, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Thu, 29 Apr 2010 01:51:32 -0700 (PDT), atledreier <atledre...@gmail.com>
> wrote:
>
> >I have a few tables in my database, all 1 to 1 with the master table.
>
> That's an *extremely* unusual design. What are these tables? Why do you need
> one to one relationships? Are you perhaps trying to get around the 255 field
> limit? If so, you are on the wrong track!
>
> >I have a query that gather much of the data into one large
> >datasheet.The problem is I cannot add records through this query.
> >I get a message that I need a related record in the child table for it
> >to work.
>
> I'm guessing that the messages is that you need a related record in the PARENT
> table, not the child?
>
> >Am I right in assuming I need to add the record in the master database
> >first, then the inegrity check will create the record inthe child
> >databases, and THEN I can add data through my datasheet?
>
> The integrity check will *prevent* the addition of an invalid record but no,
> it will not automagically create a new child record.
>
> I think you're really on the wrong track, and probably are "committing
> spreadsheet". Please post a description of your tables; I'm sure there's a
> better way to accomplish what you want done.
> --
>
> * * * * * * *John W. Vinson [MVP]

Due to limitations regarding my client I can't change the structure of
the tables.

One tag_misc for every Tag is exactly what I want, really, but I guess
I can enforce this on form level and just tell my users this is what
you get.
But after reading this, I have no idea how this has worked
before! :-)

I guess I'll shuffle over to the Forms guys and see if they can help
me out with something.

Thanks for your help!

- Atle



On 30 apr, 13:35, "BruceM via AccessMonster.com" <u54429@uwe> wrote:
> If there could be more than one record for Tag_Misc, Tag_Failure_Mode, and
> Tag_Format, those tables need to one-to-many. *If they are one-to-one there
> can be only one Tag_Misc record for each Tag. *In the Relationships window,
> one-to-many will appear (in Access 2003 and earlier, anyhow) with a 0 on one
> end of the relationship line, and an infinity symbol on the other.
>
> The problem I see is that it seems you are attempting to link the primary
> keys of your tables. *Using Tag_Misc as an example, you should have:
>
> *tblTag:
> * * TagID
> * * Description
> * * Tag_Cat (category)
> * * Function_code
>
> tblTag_misc
> * *Tag_miscID
> * *TagID (linking field to tblTag)
> * *manufacturer
> * *misc fields....
>
> It may be possible under some specific circumstances to link the PK of one
> table to the PK of another for a one-to-one. *If so, it would work onlyif
> the PK field of the linked table is not autonumber. *I'm not sure if itwill
> work even in that case, as on the rare occasions I have used one-to-one I
> have designed the table as if for one-to-many, but with a unique index onthe
> linking field.
>
> You can set the Required property of the linking field to Yes, but that only
> means (unless I am missing something) that if there is a record it must have
> a value in that field. *If you want to require Tag_misc records for each Tag
> record I think you will need to enforce that at the form level, or at least
> in some way other than requiring a value in that field.
>
> Form/subform is the best way to set this up. *It would be possible to append
> values in other ways, but for day-to-day data entry it is unlikely it would
> make much sense to take that approach. *A form based on a query including
> several table may not be updatable. *For more:http://allenbrowne.com/ser-61.html
>
> On another note, I would not use Tag as a table or field name, as it is a
> property of forms, reports, and controls. *If you use it you would haveto
> enclose it in square brackets, or you could get some unexpected results. *I
> have suggested tblTag as the table name, and TagID as the field name, butyou
> can choose what you like. *For more on Reserved words:http://www.accessmvp.com/JConrad/acc....html#Reserved...
> I have found Allen Browne's Problem names and reserved words in Access tobe
> especially helpful.
>
>
>
>
>
> atledreier wrote:
> >Thank you guys. I'll give you more details.
>
> >I am in the design phase of a project, and my client wants me to
> >import all my data when all is as-built. My client has a database for
> >all his needed info, so I just used that database structure as a basis
> >for my design. I also added some new tables for design specific data,
> >and some other stuff that won't go to my client.
>
> >There is the master tag table. This holds the main list of records.
> >Then supporting tables to give more information about different kinds
> >of tags.
>
> >First, the tables: I've not listed all the fields, but the most
> >relevant ones.
>
> >Tag:
> >*Tag
> >Description
> >Tag_Cat (category)
> >Function_code
> >...
>
> >Tag_misc:
> >*tag
> >manufacturer
> >misc fields....
>
> >Tag_Failure_Mode:
> >*tag
> >Fail_code
> >Fail_mode
> >....
>
> >Tag_Format_in/_br_/te
> >*tag
> >cal_low
> >cal_high
> >IP-grade
> >....
>
> >The tag_misc table is 1 to 1. All tags should have information in the
> >tag_misc table. I know I then should have all that information in one
> >table, but since the original client structure needs to be maintained
> >I chose that design. And this has worked for a while, so I thought
> >it'd still work.
>
> >The tag_failure_mode table should contain data for most tags, but not
> >all. I chose to have a record in there for all tags regardless, as i
> >thought that may be easier to maintain. This is based on the tag!
> >function_code field.
>
> >The tag_format table should have data for tags of certain tag!tag_cat
> >values (br, in and te type tags). Once again, most of my tags are in
> >this category, so I chose to have all tags in this table too.
>
> >So I have used a query to gather up all the relevant fields for my
> >users (they are conservative and like their big excel-like datasheets)
> >in one big list.
> >My client made a change in his underlying database recently, so I
> >thought I'd take the opportunity to re-work my database as well.
> >trying to get rid of the big queries and relying more on forms, and
> >also finally linking the database (we've all been working on the same
> >file up until now).
> >So after these changes I get the message that a relevant record needs
> >to exist in the CHILD database, the Tag_failure_mode table in my
> >instance.
> >If it is like you say then I find it strange that this has worked
> >before, really. I see how it would work like you say, but then how did
> >it work this long? It was after I linked the database and added the
> >Tag_failure_mode table it stopped working. I also made many other
> >changes, so I can't tell what I did to break it.
>
> >Any tips on how to restructure or set stuff up to make this as smooth
> >as possible?
>
> >- Atle
>
> >On 29 apr, 18:43, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
> >wrote:
>
> >> >I have a few tables in my database, all 1 to 1 with the master table.
>
> >[quoted text clipped - 23 lines]
>
> >> * * * * * * *John W. Vinson [MVP]
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

Thank you for the good luck! :-)

I'm on a little thin ice here.
My client's system import tables one by one, so no links are provided
or taken into account. I need to make sure of the integrity of the
data in the tables, hence I use referential integity. So the actual
links is pretty much up to me. So how can I make sure the data makes
sense and keep the data integrity while keeping the table structure? I
should think the structure as is should work, since the tables are
structured the way they are. Basic logic don't change from system to
system.

So, here's a link to a screenshot of the main tables in the database.
If anyone could suggest how to best structure this database while
keeping the table structure I'd be thrilled.

http://picasaweb.google.no/111389954...51736013107746

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.

Hope someone can make sense of this, I really haven't fully wrapped my
head around these basic issues yet.

- Atle


On 30 apr, 16:06, "BruceM via AccessMonster.com" <u54429@uwe> wrote:
> Don't try to link the PK fields! *If you have just one Tag_Misc for every
> client, is there a reason it cannot appear in the main client record?
>
> The "forms guys" cannot overcome an unworkable design. *It's hard to know how
> it worked before, but you are **severely** limited by the design if you have
> no freedom to change the table structure. *Good luck.
>
>
>
>
>
> atledreier wrote:
> >Due to limitations regarding my client I can't change the structure of
> >the tables.
>
> >One tag_misc for every Tag is exactly what I want, really, but I guess
> >I can enforce this on form level and just tell my users this is what
> >you get.
> >But after reading this, I have no idea how this has worked
> >before! *:-)
>
> >I guess I'll shuffle over to the Forms guys and see if they can help
> >me out with something.
>
> >Thanks for your help!
>
> >- Atle
>
> >> If there could be more than one record for Tag_Misc, Tag_Failure_Mode,and
> >> Tag_Format, those tables need to one-to-many. *If they are one-to-one there
> >[quoted text clipped - 131 lines]
> >> --
> >> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...
>
> --
> Message posted viahttp://www.accessmonster.com

On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier <(E-Mail Removed)>
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]

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 <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier <atledre...@gmail.com>
> 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 recordhas
> 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, recordsof
> "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]

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 <atledre...@gmail.com> 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 <jvinson@STOP_SPAM.WysardOfInfo.com>
> wrote:
>
>
>
> > On Mon, 3 May 2010 01:12:56 -0700 (PDT), atledreier <atledre...@gmail.com>
> > 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 bea
> > 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]