Compare field names, adding/deleting fields

[thh]

Problem:
Field names in my table A is built by a character followed by a number,
e.g. A1, where 'A' signies the variable and '1' an ID of a person.

My second table B have the same field name structure, like B1, B3 etc. This table is constructed as a result of a complex Pivot query, and I have not control of the fields (persons) it ends up with.

I would like to restructure table B in the following way:
1. If table B contains fields of persons that is not in table A, delete that field.
2. If table B misses some of the fields in table A, add that field.

The order of fields in B should be the same as in A.

Example:
Table A:

Key A1 A2
1 11 12
2 21 22

Table B:

Key B1 B3
1 .11 .12
2 .21 .22

So: Delete B3 since person 3 is not in A, and add B2 since person 2 is present in A but not in B. If successful, I shold end up with a table B_adjusted like


Key B1 B2
1 .11 null
2 .21 null

Comments:
In reality the number of tables and fields are large, and I would like to program this in vba code. Tables will be exported to a matrix oriented language like Matlab for statistical analysis, and it is convenient to have tables with equal matrix dimensions, so that matrix index [i,j] refer to the same person and record, thus the need for introducing null fields.

The main problem is thus to compare the integer part of the string names, and based on the result, execute delete or add fields. I could rename field names by removing the character part if that makes the problem easier. Any hints are welcome.

Best regards thh

Version: Access 2002 SP3

 

[boblarson]

You are running into these problems because your database is not normalized. In fact, you should NOT have columns based on multiple people. You need to revisit your design (check out the posts on normalization, especially those by Pat Hartman).

I know it's going to be a headache to redesign your database, but it will only get worse if you don't do it now. I've experienced this firsthand. I did exactly the same thing you did and it became a total nightmare.

Now that I design my databases properly, it is so easy to add things it is scary to think what things were like 10 years ago when I started.

The issue is trying to stop thinking horizontally and start thinking in a vertical sense.

 

[thh]

Thanks boblarson

Yes, a properly normalized database will probably ensure that I do not end up with such "ugly" tables like my Table B (having to rely on a Pivot query) that needs restructuring. As things are in my field (economic research), we are often given badly structured databases from external sources, and we have to try to pull out a sensible data set from them as they are.

Wrt not having people in columns: In a final data set for my purpose, they do have to be in columns as each person's action represents a different variable to be included in e.g. regression analysis. Actually, I'm studying bidder behaviour in an auction market, and a column represents the bids submitted by a given bidder and the rows represent the different auctioned objects.

My tables are the end result of some make table queries. They do not represent the structure of the basic tables in the database.

Cheers, thh