Cascade Update/Delete Lookup in Table

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

Joshua

If you'll scan this newsgroup for related postings, you'll see that there is
considerable opinion against using "lookup" datatypes in tables. While well
intentioned, the lookup datatype is actually storing one thing (the key
value), while displaying something else.

You'll also find considerable opinion against working directly in the
tables. Access tables store data, but have little in the way of tools for
display. Access forms display data and have a very rich "event" environment
you can use to make the data much more user-friendly.

I urge you to change that lookup datatype to whatever the underlying key
field's datatype might be, and to start using forms instead of the tables to
display data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Joshua" wrote in message
...
Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in
the
master table. If I change "graphic" to "graphics" and there are already
rows
with "graphic," is there a way to make it update all those rows?

Thanks!

Joshua:

You really shouldn't be storing text data from a lookup. You should store
the ID number for the appropriate record as then you only need to make a
single change to an item in the lookup table if the text needs changing.
Storing the ID number of the lookup is the way you keep normalization and
also then don't need to update multiple rows for a change in text.

As for Cascading Updates - if we talk about that from a relationship
standpoint, it has nothing to do with updating the text of the item in the
table. It has to do with the fact that if you were to change the primary key
of an item in the table where it is the primary key, the foreign keys would
cascade update to match the new key. But, if you use autonumbers for keys
(which is actually a pretty good idea), you would not ever likely need a
cascade update to occur. A cascade delete is something that, if you set it,
will delete any child records for a parent record if the parent record is
deleted. That way you do not have "orphan records" that hang around
afterwards.

I hope that makes sense.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

If you have proper relationships between your tables you would only need to
make a change in the lookup table and they would instantaneously be 'pushed'
to your data table. The key is your relationships.

Another alternative would be to use an update query.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

Daniel:

Actually, that is not completely true, as noted by my earlier post. Updates
to text values in a lookup table are NOT propagated to tables that have those
values stored. And, those values should NOT be stored actually. In reality
numeric ID's should be used as then you can update the text descriptions in
one place and, if you've used queries properly in your row sources, etc., it
will then display the updated text descriptions.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"Daniel Pineault" wrote:

If you have proper relationships between your tables you would only need to
make a change in the lookup table and they would instantaneously be 'pushed'
to your data table. The key is your relationships.

Another alternative would be to use an update query.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

I just read Jeff Boyce's response and it had not occurred to me that you
might be talking about using lookups at the table level instead of at form
level (where I was talking about). If you are talking about lookups at table
level, I would suggest reading this:
http://www.mvps.org/access/lookupfields.htm

--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"boblarson" wrote:

Joshua:

You really shouldn't be storing text data from a lookup. You should store
the ID number for the appropriate record as then you only need to make a
single change to an item in the lookup table if the text needs changing.
Storing the ID number of the lookup is the way you keep normalization and
also then don't need to update multiple rows for a change in text.

As for Cascading Updates - if we talk about that from a relationship
standpoint, it has nothing to do with updating the text of the item in the
table. It has to do with the fact that if you were to change the primary key
of an item in the table where it is the primary key, the foreign keys would
cascade update to match the new key. But, if you use autonumbers for keys
(which is actually a pretty good idea), you would not ever likely need a
cascade update to occur. A cascade delete is something that, if you set it,
will delete any child records for a parent record if the parent record is
deleted. That way you do not have "orphan records" that hang around
afterwards.

I hope that makes sense.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

Daniel:

Actually, after reading Jeff's post I realize that you may have been on that
same track so forgive the other post.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"boblarson" wrote:

Daniel:

Actually, that is not completely true, as noted by my earlier post. Updates
to text values in a lookup table are NOT propagated to tables that have those
values stored. And, those values should NOT be stored actually. In reality
numeric ID's should be used as then you can update the text descriptions in
one place and, if you've used queries properly in your row sources, etc., it
will then display the updated text descriptions.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"Daniel Pineault" wrote:

If you have proper relationships between your tables you would only need to
make a change in the lookup table and they would instantaneously be 'pushed'
to your data table. The key is your relationships.

Another alternative would be to use an update query.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

First off, thanks for all the replies. You guys in this group help me more
than you all know.

I personally love forms. But my boss likes working in tables. Unfortunately,
for this situation we need to store the value that's being looked up. It's a
simplistic database though with 2 tables and one split form (07) so I won't
stress out about it. I'll just create a button that they can update that
category table and have it do an update query to match the parent table. More
trouble than I hoped for, but, it'll work.

Thanks again for all the wisdom.

"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

Nothing to worry about, we are all here to help and learn!
--
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"boblarson" wrote:

Daniel:

Actually, after reading Jeff's post I realize that you may have been on that
same track so forgive the other post.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"boblarson" wrote:

Daniel:

Actually, that is not completely true, as noted by my earlier post. Updates
to text values in a lookup table are NOT propagated to tables that have those
values stored. And, those values should NOT be stored actually. In reality
numeric ID's should be used as then you can update the text descriptions in
one place and, if you've used queries properly in your row sources, etc., it
will then display the updated text descriptions.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"Daniel Pineault" wrote:

If you have proper relationships between your tables you would only need to
make a change in the lookup table and they would instantaneously be 'pushed'
to your data table. The key is your relationships.

Another alternative would be to use an update query.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked up in the
master table. If I change "graphic" to "graphics" and there are already rows
with "graphic," is there a way to make it update all those rows?

Thanks!

AMEN!

Jeff B.

"Daniel Pineault" wrote in
message ...
Nothing to worry about, we are all here to help and learn!
--
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"boblarson" wrote:

Daniel:

Actually, after reading Jeff's post I realize that you may have been on
that
same track so forgive the other post.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"boblarson" wrote:

Daniel:

Actually, that is not completely true, as noted by my earlier post.
Updates
to text values in a lookup table are NOT propagated to tables that have
those
values stored. And, those values should NOT be stored actually. In
reality
numeric ID's should be used as then you can update the text
descriptions in
one place and, if you've used queries properly in your row sources,
etc., it
will then display the updated text descriptions.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


"Daniel Pineault" wrote:

If you have proper relationships between your tables you would only
need to
make a change in the lookup table and they would instantaneously be
'pushed'
to your data table. The key is your relationships.

Another alternative would be to use an update query.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joshua" wrote:

Hello,

I'm trying to see if it is possible to have a table with a look up
field
that updates all records when the linked table data is changed.

For example, we have categories in a separate table that is looked
up in the
master table. If I change "graphic" to "graphics" and there are
already rows
with "graphic," is there a way to make it update all those rows?

Thanks!