AnsweredAssumed Answered

Complicated Lookup or merging of data

Question asked by afubeca on Aug 12, 2009
Latest reply on Aug 13, 2009 by TSGal


Complicated Lookup or merging of data


I have a Main Table with 15,000 records that I would like to add some select data to.


I am pulling the data I want to add from 10 "Large Tables" with about 10,000,000 records in each table, so I am trying to figure out the most efficient way to do this. I only need 5 records form each table of 10,000,000 for each of the records on the Report Table of 15,000.


My Main Table (15,000 records) looks like this:


F1         F2            F3          F4         F5       F13

G6000     Data1     Data2     5609     6609 etc 7806

G7000     Data1     Data2     6795     3948 etc 8560


F1 = Unique Record number

F2 = Data for the record

F3 = Data for the record

F4 = ID number that appears in F1 of Large Table 1

F5 = ID number that appears in F1 of Large Table 2

etc. so F13 is the ID number that appears in F1 of Large Table10


Large Tables (10,000,000 records each) are all set up the same w/ four columns and look something like this.


F1         F2         F3         F4         F5

5609       A         B           C         100

5609       A         A           A         200

5609       B         C           A         5000

5609       B         A           A         567

6795       A         B           C         200

6795       A         A           A         201

6795       B         C           A         5500

6795       B         A           A         600


F1 - ID number that appears in corresponding field in the Main Table (this id number repeats itself multiples times for every each data point in F5

F2 - Code that helps identify F5

F3 - Code that helps identify F5

F4 - Code that helps identify F5

F5 - The data I am after.


I know the first step is to establish relationships between the fields with ID numbers in the Main Table and the Large Tables. For example I established a relationship between F4 in the Main Table with F1 in Large Table 1 and another between F5 in the main table


Here is a simplified example of what I am trying to do:


Assuming I want to add the values from the Large table that come after F1=(Desired ID) F2=B F3=C and F4=A into the Main Table. I assume I need to do the following:


  1. Create a new F14 field for the Main Table where the data from Table 1 will show up.
  2. Create some sort of script or look up that puts the F5 data in the F14 field when F1, F2, F3 and F4 equal specified values.

I guess another way to do this would be to constrain the Large Tables so that they only show a certain combination of F2, F3 and F4 fields and so there is only one instance of the ID number in each table, and then do a look up. The only problem with doing that is that the data in the Tables will be updated form time to time and would require the tables to be revised with each update. However if there is a way to do a lookup on constrained data (or a found set) it may not be a problem.


Any ideas or direction would be helpful.