A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Adding records with referential integrity



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2010, 09:51 AM posted to microsoft.public.access.tablesdbdesign
atledreier[_2_]
external usenet poster
 
Posts: 22
Default Adding records with referential integrity

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?
  #2  
Old April 29th, 2010, 02:19 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Adding records with referential integrity

A 1 to 1 relationship is unusual, as there can be only one child record for
each parent record. A search for subclass or subclassing should produce some
more information Without knowing anything about how your database is put
together or what it is used for it is almost impossible to offer specific
suggestions. In general you do not assemble several tables into a single
query for data entry purposes. For one thing, such queries are often read-
only. In other cases (such as yours) there may be data integrity issues that
cannot be resolved.

As a point of terminology, "master database" is not especially meaningful. I
assume you mean the main table. If so, you are correct that you need a
parent record before you can have a child (related) record. However, it is
incorrect that "the inegrity check will create the record in the child
{tables}". The integrity check is just that: a check. It does not by itself
perform actions. For adding child records, a form/subform is the typical
route.

A better understanding of databases in general and Access in particular may
be of value:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

atledreier wrote:
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?


--
Message posted via http://www.accessmonster.com

  #3  
Old April 29th, 2010, 05:43 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Adding records with referential integrity

On Thu, 29 Apr 2010 01:51:32 -0700 (PDT), atledreier
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]
  #4  
Old April 30th, 2010, 07:52 AM posted to microsoft.public.access.tablesdbdesign
atledreier[_2_]
external usenet poster
 
Posts: 22
Default Adding records with referential integrity

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
wrote:
On Thu, 29 Apr 2010 01:51:32 -0700 (PDT), atledreier
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]


  #5  
Old April 30th, 2010, 12:35 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Adding records with referential integrity

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 only if
the PK field of the linked table is not autonumber. I'm not sure if it will
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 on the
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 mo
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 have to
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, but you
can choose what you like. For more on Reserved words:
http://www.accessmvp.com/JConrad/acc...#ReservedWords
I have found Allen Browne's Problem names and reserved words in Access to be
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
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.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

  #6  
Old April 30th, 2010, 02:07 PM posted to microsoft.public.access.tablesdbdesign
atledreier[_2_]
external usenet poster
 
Posts: 22
Default Adding records with referential integrity

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 only if
the PK field of the linked table is not autonumber. *I'm not sure if it will
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 on the
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 mohttp://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 have to
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, but you
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 to be
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
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...


  #7  
Old April 30th, 2010, 03:06 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Adding records with referential integrity

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 via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.