Compare and update entire recordset or reassign key field?

[Marty2of5]

I'm not sure where to begin. I need to synchronize two data tables. Table1 is the one I work with for queries, reports, automation. Table2 is the official data source which is updated weekly. The two are nearly identical except I added a primary key to Table1. I can't modify Table2 and it identifies records by an ID field that changes, but is always unique. (It's a 6 character field. The sixth character changes, but several records have the same first five digits. No two records have the same 6 characters.)

Do I loop through each field and update Table1 with the changed data from Table2 (matching records by the closest matched ID fields), or do I add my primary key to Table2 (matching records by the closest matched ID fields again) and rename the tables so all my procedures now refer to the updated table?

Can someone give me suggestions in code?

Thanks
Marty

 

[kathryn]

OK, how is Table1 created? Is it created weekly, after Table2 is updated? Kathryn

 

[Marty2of5]

Yes.

Table1 needs to be repopulated each week from Table2. OR Table2 can replace Table1 if I can find a way to assign the key field from Table1 to the corresponding record in Table2.

(Right now, I have Table2 as a linked table. When the table is updated each week, I need to run a series of queries to find out which records contain new data and make the corresponding changes in Table1.)

Marty

 

[kathryn]

I guess I am not clear on how you can update table1 from Table2 when there is no identifiable ID in Table2 to link the two.

You say that you "run a series of queries to find out which records contain new data and make the corresponding changes in Table1" How do you know what is "corresponding"?

Maybe if you post an example of what the data in the two tables look like before and after an update that would help. You would only need to show the fields you use to link the two tables and a field that might be updated.





Kathryn

 

[raskew]

I'm a little confused about the six-character ID field in table 2. When you say it changes, do you mean that the ID field for a specific record does not remain constant?

If a record's ID field does remain constant, it would seem that this would be a candidate for your primary key since, as you say, it's always unique.

 

[Marty2of5]

I wish it were that simple raskew. OK, kathryn, thanks for hanging in there with me. Here's the whole gruesome tale.

Table2 is a weekly data dump from an old Unix mainframe that was written 30 years ago. Each record represents a project. The project ID changes to reflect the progress of the project as follows:
Before it is official, it has a 4 digit alphanumeric "ficticious" ID: like A470
Once the project is official, it is given a 6 digit ID that has nothing to do with the temporary 4 digit ID: 12340K
The last digit represents the phase. K=preplanning, 0=study, 1=design, and so on up to phase 9.
The fifth digit is used when projects are split into segments or combined. For example, our project 123401 could be split into three projects: 123411, 123421, and 123431.
Or it could be combined with the smaller project 987601 and become 1234U1.
For many projects, it will retain the first four digits (1234). However, if 123401 was smaller than 987601 and they were combined, they would become 9876U1.
I really have very little rigorous identification to go on.

Here's what I do now.
1. Run a simple to query to find all the one to one matches between ID fields. This takes care of around 85% of the records.
2. Find records that match the first 5 digits fo the ID field. Determine if there has been a one step phase change. Make the corresponding change in Table1, retaining the same record and primary key.
3. Take the remaining records from Table2 and find all the temporary IDs that have been given an official 6 digit ID by comparing other fields such as [ProjMgr] and [Description]. Update the ID in Table1, retaining the primary key.
4. There will still be splits and combines. For these, I make the original project inactive (by updating status fields), and import the new split or combine IDs as new records with their own primary keys.

I want to automate this procedure perhaps with a nested If..Then. But I wanted to check first to see if there was something really basic that I'm missing altogether.
Any thoughts?

Marty

 

[kathryn]

Marty,

I think that the way you are going about it is the best way. It is tedious, but with the insane numbering system you have to deal with, you don't really have any choice.

I would agree with going with a nested if or a Select... Case statement.

Good luck and post if you need help with the nuts and bolts. Kathryn