Retrieve names of fields common to multiple tables

[Atomic Shrimp]

I still don't get why you need to do it dynamically though, unless there are going to be more and more new, non-normalised tables appearing in the future.

I also don't really know what you mean by 'modifier' (I mean, I understand the dictionary definition of the word, just not what it means to you in this context).

What is the desired outcome for all of this process? Knowing that would really help us to help you, I think.

 

[cj-1289]

Yes: at least 9 more tables to come and the program is to dynamically compare the fields of the new tables with the old tables upon request.

'Modifier' in my context is one record in the table 'Modifiers'. Each of these records contain a unique name, the names of several tables, and the fields that they share in common. For every table there is a letter (or 'Parameter') which references a multiplier inputted on another form. This multiplier determines how many times a new record should be added to the relevant table. Data to fill the 'Modifier's' shared fields is user-inputted. It is copied into every table contained in the 'Modifier', in the amount of records specified by the parameter.

Thus, every table is filled with generic information in as many records of that table as that table requires the generic information to be listed, saving lots of repetitive typing for the technicians.

More repetitive typing will be saved by the shared fields being automatically determined, as opposed to manually entered into the 'Modifier'.

(It's called a 'Modifier', therefore, because it determines how all the tables are modified. Was previously called an 'Updater' but that was a term already in use in the company.)

(But I'll see how I get along with the code you've already given me.)

 

[Atomic Shrimp]

Best of luck with it. It sounds horrible, and I don't envy you at all.

My concern, if I were in your position, would be that the solution you're trying to build here may permit the problem of fragmented and denormalized data to continue getting worse - perhaps building to some future point where it catastrophically fails.

 

[The_Doc_Man]

My greater concern isn't the mechanics of what you are doing but the meaning underneath it. To do this requires that all sources of data treat those fields uniformly. If not, you will have a massive-squared problem. First, finding similar fields. Second, deciding whether the interpretations are compatible.

If there is ever a case where the interpretations are NOT compatible, you cannot merge the data without technically violating the underlying assumption that drives a normalization process. That is that the records of the table all mean the same thing and are interpreted the same way. This is a VERY basic set-theory assumption.

I might also suggest that a UNION query is still useful for you to prepare the holding tables that will eventually become the final repositories of your normalized data. Start by building the structures. You need to then somehow establish the base tables, perhaps only partly populated, so that you can then go through a data refinement process. For instance, does one source table use abbreviations and another not? Then one of them has to be updated to be compatible with the other.

 

[cj-1289]

Interpretations of the same-named fields are all exactly the same at all times. Otherwise they'd be called something different. The system that I'm working with is not as chaotic as that! You're correct about this 'data refinement process' though, for example there are identical fields with different names such as 'Study Well'/'Well' and 'Client'/'Client Ref'/'Client Name' across the tables. I'm currently seeing such refinement as a manual process though: but if I decide to attempt to automate that too, watch this space.

You may be right about me being able to use union queries in that way, Doc_Man, and I'll take that idea into account when I try and bring all of this together tomorrow. I was getting most of the rest of the code tied up today.

And Mike: the fragmentation isn't entirely without purpose. Certain people need to use certain tables for different things. Also, most of the tables are at the limit for fields and thus need to be split arbitrarily in order to get everything in. Alternatives considered, this is the best way to simulate normalization. It runs largely as it should, I've already written most of what you don't envy me for.

 

[Atomic Shrimp]

And Mike: the fragmentation isn't entirely without purpose. Certain people need to use certain tables for different things.

Sure, but in a normalized data environment, you just do that with queries and forms.

Also, most of the tables are at the limit for fields and thus need to be split arbitrarily in order to get everything in.

That also makes the hairs on my neck stand up. Not a good sign at all.

Alternatives considered, this is the best way to simulate normalization. It runs largely as it should, I've already written most of what you don't envy me for.

Fair enough - all that remains now is for it to spiral into complex self-destruction

Perhaps that's too harsh - you know the data better than we do, obviously - you've gotta do what you feel is most workable. All I can say is that this is the part of the Lucasfilm script where I'd be saying "I've got a bad feeling about this..."

 

[SOS]

All I can say is that this is the part of the Lucasfilm script where I'd be saying "I've got a bad feeling about this..."

Or "There is a great disturbance in the Force..."

 

[gemma-the-husky]

my code is tested and will work

uses dao, so that needs to be your default setting

-------------
i still think it will be hard to do much even after you identify the common fields, but it wasnt too hard to build the table checker, and i hope it helps

 

[cj-1289]

GTH: Thank you very much - I modified your code to compare more tables than just 2 and the program's now complete. You have my gratitude!

Mike: Queries are still used depending on the purpose, but they're less effective in some situations, like where there's an arbitrary split. The whole system was a mess that was becoming unworkable, it's why I was called in to think of a solution.

SOS: The force is with me