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:
- Create a new F14 field for the Main Table where the data from Table 1 will show up.
- 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.