Lookup Column Auto-fill?

[cmet]

I have two tables of aircraft serial numbers I am trying to link. Both tables have Serial Numbers as their primary key (and are linked accordingly), and each table includes an Operator field. The Operator fields, while they *usually* refer to the same operator (for each table) are phrased differently (for example, serial number 3056 refers to Operator "Algerian Air Force" in Table1 and Operator "Flying Algeria" in Table2.

Most of the records match serial numbers in both tables, but each table has "extra" records that aren't included in the other table. Here's my dilemma: trying to match up the inconsistent serial numbers with an Operator from the opposite table, so when I do a full query, all the aggregate results by operator show.

I've tried to do Lookup tables, but it's trying to make me manually input the Operator for each record - though I know it knows it's linked, because the Serial numbers are the same.

How can I link the two tables?

[gnawoncents]

A few questions:

First, are you trying to merge these two tables into one new table which you will use from now on, or do you need to keep both tables and just want a query based on the results?

Second, can you please provide a brief example (a few records from each table) and what you need the finished product to look like.

Thanks.

[cmet]

I'd like one full table, preferably. The key point is finding a way to select an Operator from Table1 for serial numbers that exist only in Table2.

Here's the example:

Table1:
3126 Algerian Airlines Field1 Field2 Field3 etc
3127 Chad Air Force Field1 Field2 Field3 etc

Table2:
3126 Flying Algeria Field5 Field 6 Field7
3128 Flying Chad Field 5 Field 5 Field 5

Final Output:
Algeria Airlines
3126 Field1 Field5 Field6
Chad Air Force
3127 Field1 Field5 Field6
3128 Field1 Field5 Field6

Thoughts?

[gnawoncents]

If there isn't any field with common data to link between the two tables, this is going to be difficult.

How many different operators are we talking about here? If it's not that many, a one-time manual update or find/replace might be the best solution. Otherwise there isn't going to be any real way to automate the process.