Append-and/or-Update Query SQL/VBA

I have a "common" table with about 60 fields which are common to one or more of 5 different tables. Initially a record in the common table is created by filling in a form. The "common" table is then APPENDed to each of the 5 tables thereby creating a new record in each and filling in the fields common to each respective table. This works fine (manually) although I still have to use a WHERE condition to ensure that I am only APPENDing new records.

The problem arises when I edit/modify one of the already APPENDed records in the "common" table (eg. change an address or a telephone number) and then try to reflect the change/changes in the same corresponding records in the respective tables previously APPENDed to. These other 5 tables contain fields which are NOT common and therefore may be edited individually through their own forms. I don't want to lose these edits.

I have been trying to accomplish this using an UPDATE query which seems to me to be the logical approach but I'm coming undone in the practical implementation or development of the required SQL. From there I want to create a VBA procedure (which presents its own challenges )

I hope the above is understandable and any assistance will be greatly appreciated. Thanx.

Your procedure is highly unusual. Data related from different tables would be assembled using a query, the query would be bound to a form and show the data on screen, data would be typed/modified directly, and that would be that. The "common" table would not exist, but be the output of a query reassembling the data as desired at the time.

Is there any specific reason for your "inverted" handling of data?

Yes Sikepl, there is. Please prove me wrong! I know my approach is somewhat unusual but seems to be required by the application: a 'forms generator' for a chartered accounting firm where they have to complete a series of registration forms for their clients. Doing this manually is a lenghty and time consuming job and most forms require the same information in many of their fields, which means a lot of duplication (and room for error).

The easiest way to do this would be, I believe, to have one table for all forms from which the various reports could be generated. In theory this would work but then I would end up with a table containing well over 255 fields! (there's my 'limitation') Hence the 'common table' approach.


I secretly hope, however, that there is a simpler way to achieve the desired result and would appreciate any suggestions...

In relational database one does not have large numbers of common fields in multiple tables. Only the key fields are common. As Spike indicated, the common data is taken from the one table and assembled with a query.

You need to study normalization.

Post a sample of your tables and we can show you how it aught to be structured.

Mist said:

Yes Sikepl, there is. Please prove me wrong! I know my approach is somewhat unusual but seems to be required by the application: a 'forms generator' for a chartered accounting firm where they have to complete a series of registration forms for their clients. Doing this manually is a lenghty and time consuming job and most forms require the same information in many of their fields, which means a lot of duplication (and room for error).

The easiest way to do this would be, I believe, to have one table for all forms from which the various reports could be generated. In theory this would work but then I would end up with a table containing well over 255 fields! (there's my 'limitation') Hence the 'common table' approach.


I secretly hope, however, that there is a simpler way to achieve the desired result and would appreciate any suggestions...

Click to expand...

As Galaxiom said, "You need to study learn Normalization." The way you are going about this may seem logical in the abstract, but it runs counter to the way relational database applications work. Therefore, you're going to spend the rest of the time you work with this design compensating for one problem or another as they arise.

I want to make one point as clear as possible. I think sometimes people hear "Normalization" and think, "My way works just as good for my unique situation." That's seldom true.

This is not the sort of consideration one might make in deciding between alternate, but fairly equivalent and viable, approaches to a task. For example, the question might be:

"Should I drive from Los Angeles to New York, or should I take a flight?"

Each comes with advantages and disadvantages, but the choice is between two equally viable choices. Give up something (time) to take the scenic route, or give up the freedom to take small side trips to get there faster.

Instead, the description of the existing design, and your attempts to resolve it in either SQL or VBA, is more like asking "How can I drive from Los Angeles to New York non-stop?" Sorry, it's hard to picture a viable solution to that. Of course, one could hire a tanker truck and fill it with gasoline and attach that to the car's fuel system. After days or weeks of extra preparation and a lot of additional -- highly customized modifications -- you could be ready to make the drive "non-stop". But that seems like a rather cumbersome, expensive and hard to maintain approach best left to the reality shows on network TV, and not a very good solution to the problem of traveling from Point A to Point B.

Normalize works because "it just works". Most other approaches require that the developer "has to make it work."

@GPGeorge Did you spot that this was an 11-year-old thread?

Minty said:

@GPGeorge Did you spot that this was an 11-year-old thread?

Click to expand...

Yup. I was asked specifically to comment on it, though, so I figured what the heck.