update query

[NM]

My database has many parent/child records of part
components and ratios that I need to string together for
reports and queries. This concatenation process is proving
to be a performance issue. I've been thinking about adding
fields to my parent table to store the concatenated data.
Could I trigger an update/append query to write the data
to the parent table? Is this not a good idea?

 

[Armen Stein]

My database has many parent/child records of part
components and ratios that I need to string together for
reports and queries. This concatenation process is proving
to be a performance issue. I've been thinking about adding
fields to my parent table to store the concatenated data.
Could I trigger an update/append query to write the data
to the parent table? Is this not a good idea?

This is called denormalizing for performance, and there are times when
it makes sense.

Access doesn't support triggers to automatically recalculate the value
in the parent table. However, in your Access application you can find
all the places that the child records can be added/deleted/changed. In
those places, run a subroutine that recalculates the field and updates
the parent record. You probably want to use some VBA with recordset
code that just updates the one affected parent record; you probably
don't want to update all the parent records on a regular basis.

By the way, SQL Server has triggers that can automatically run code
whenever a record is changed.

Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[NM]

Thanks for the response. I am not much of a programmer yet
so might you know of sample databases or such that I might
take a look at?

 

[Armen Stein]

Thanks for the response. I am not much of a programmer yet
so might you know of sample databases or such that I might
take a look at?

I don't have any ready-to-go examples. Perhaps someone else does?

To get you started though, you can add code to the After Update event of
all forms that modify child records. This code would perform the same
concatenation that you are already doing, but for only the current
parent record. Using this new calculated value, you can update the
parent record using either a control on the form (if the parent record
is already being editing on the current master form) or a recordset (if
it isn't).

By the way, it isn't a bad idea to name the calculated field something
special to indicate that it isn't a real data field to be updated by
humans. In our shop, we put a _CALC suffix on the field name, like
OrderTotal_CALC.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com