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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Joins & Cascading



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2010, 07:00 PM posted to microsoft.public.access.gettingstarted
Kelli
external usenet poster
 
Posts: 56
Default Joins & Cascading

I am relatively new to Access, and am currently creating a new Database, four
tables, definately obeying the first two normal forms. The main table (named
tblCO) has a field named Conc# that is designated as the primary key. I need
to join the remaining three tables to tblCO so that when a value is input
into the Conc# field, it cascades to the other three table.
I will ultimately build a form for users to work with in data input/editing,
but am using the tables myself during the developement stage. As I put in
data, I can not get the Conc# field values to propogate to the other tables.
I have joined them and have selected Referential Integrity and Cascade Update
and Cascade Delete.
What might I be doing incorrectly.
Thank you for you interest in my problem
Kelli
  #2  
Old April 27th, 2010, 07:31 PM posted to microsoft.public.access.gettingstarted
Golfinray
external usenet poster
 
Posts: 1,597
Default Joins & Cascading

I would first check my relationships. A one-to-one is where, say, you have
one salesman, one product to sell. In that case, you usually don't need but
one table unless it is huge and then you might split into two. One-to-many
is, say, one salesman, many products. In that case you want the primary key
in the one table to be the foreign (not primary) key in the ohter tables. If
you have many-to-many, say many salesmen, many products, you need tables in
between those tables to connect them together or you won't get good results.
Cascade updates and deletes will only update or delete what is in the
relationship.
--
Milton Purdy
ACCESS
State of Arkansas


"Kelli" wrote:

I am relatively new to Access, and am currently creating a new Database, four
tables, definately obeying the first two normal forms. The main table (named
tblCO) has a field named Conc# that is designated as the primary key. I need
to join the remaining three tables to tblCO so that when a value is input
into the Conc# field, it cascades to the other three table.
I will ultimately build a form for users to work with in data input/editing,
but am using the tables myself during the developement stage. As I put in
data, I can not get the Conc# field values to propogate to the other tables.
I have joined them and have selected Referential Integrity and Cascade Update
and Cascade Delete.
What might I be doing incorrectly.
Thank you for you interest in my problem
Kelli

  #3  
Old April 27th, 2010, 07:57 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Joins & Cascading

On Tue, 27 Apr 2010 11:00:01 -0700, Kelli
wrote:

I am relatively new to Access, and am currently creating a new Database, four
tables, definately obeying the first two normal forms. The main table (named
tblCO) has a field named Conc# that is designated as the primary key. I need
to join the remaining three tables to tblCO so that when a value is input
into the Conc# field, it cascades to the other three table.


That's not how relationships work! A relationship *prevents* adding invalid
records; it doesn't automagically create a new record.

I will ultimately build a form for users to work with in data input/editing,
but am using the tables myself during the developement stage.


Use a form with subforms from the start. If you have a child table displayed
in the subform using Conc# as the Master/Child Link Field it will fill in
automatically for you. Table datasheets are for data storage; they are *very
limited* when it comes to data entry.

As I put in
data, I can not get the Conc# field values to propogate to the other tables.


It won't, and you shouldn't expect it to do so. After all some parent records
might not HAVE any child records; some might have one; some might have
thousands. Create the child record when you have data to put in the child
record (using a Subform); there is no need to create an empty "placeholder"
record.


I have joined them and have selected Referential Integrity and Cascade Update
and Cascade Delete.


Cascade Update will update an existing linked child record when you change the
value of the primary key field in an existing record in the table (something
that should rarely or never be done, since primary keys should be stable).

What might I be doing incorrectly.


Just your expectation.
--

John W. Vinson [MVP]
  #4  
Old April 28th, 2010, 07:53 PM posted to microsoft.public.access.gettingstarted
Kelli
external usenet poster
 
Posts: 56
Default Joins & Cascading

Thank you, and I have lowered my expectations as you suggest, but not my
enthusiasm

"John W. Vinson" wrote:

On Tue, 27 Apr 2010 11:00:01 -0700, Kelli
wrote:

I am relatively new to Access, and am currently creating a new Database, four
tables, definately obeying the first two normal forms. The main table (named
tblCO) has a field named Conc# that is designated as the primary key. I need
to join the remaining three tables to tblCO so that when a value is input
into the Conc# field, it cascades to the other three table.


That's not how relationships work! A relationship *prevents* adding invalid
records; it doesn't automagically create a new record.

I will ultimately build a form for users to work with in data input/editing,
but am using the tables myself during the developement stage.


Use a form with subforms from the start. If you have a child table displayed
in the subform using Conc# as the Master/Child Link Field it will fill in
automatically for you. Table datasheets are for data storage; they are *very
limited* when it comes to data entry.

As I put in
data, I can not get the Conc# field values to propogate to the other tables.


It won't, and you shouldn't expect it to do so. After all some parent records
might not HAVE any child records; some might have one; some might have
thousands. Create the child record when you have data to put in the child
record (using a Subform); there is no need to create an empty "placeholder"
record.


I have joined them and have selected Referential Integrity and Cascade Update
and Cascade Delete.


Cascade Update will update an existing linked child record when you change the
value of the primary key field in an existing record in the table (something
that should rarely or never be done, since primary keys should be stable).

What might I be doing incorrectly.


Just your expectation.
--

John W. Vinson [MVP]
.

  #5  
Old April 29th, 2010, 05:55 PM posted to microsoft.public.access.gettingstarted
Kelli
external usenet poster
 
Posts: 56
Default Joins & Cascading

John,
That was sage advice.
I lowered my expectations, and got more cool things done than I thought I
could

Many thanks.

"John W. Vinson" wrote:

On Tue, 27 Apr 2010 11:00:01 -0700, Kelli
wrote:

I am relatively new to Access, and am currently creating a new Database, four
tables, definately obeying the first two normal forms. The main table (named
tblCO) has a field named Conc# that is designated as the primary key. I need
to join the remaining three tables to tblCO so that when a value is input
into the Conc# field, it cascades to the other three table.


That's not how relationships work! A relationship *prevents* adding invalid
records; it doesn't automagically create a new record.

I will ultimately build a form for users to work with in data input/editing,
but am using the tables myself during the developement stage.


Use a form with subforms from the start. If you have a child table displayed
in the subform using Conc# as the Master/Child Link Field it will fill in
automatically for you. Table datasheets are for data storage; they are *very
limited* when it comes to data entry.

As I put in
data, I can not get the Conc# field values to propogate to the other tables.


It won't, and you shouldn't expect it to do so. After all some parent records
might not HAVE any child records; some might have one; some might have
thousands. Create the child record when you have data to put in the child
record (using a Subform); there is no need to create an empty "placeholder"
record.


I have joined them and have selected Referential Integrity and Cascade Update
and Cascade Delete.


Cascade Update will update an existing linked child record when you change the
value of the primary key field in an existing record in the table (something
that should rarely or never be done, since primary keys should be stable).

What might I be doing incorrectly.


Just your expectation.
--

John W. Vinson [MVP]
.

 




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 06:15 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.