Sorry for the unhelpful title, what I need to do is this:
Compare 2 tables based on a match/join of 4 fields then write the value from the ID field to a field in the first table. The data must be written permanently to table 1, viewing a select query isn’t an acceptible option.
The ‘join’ fields are actually XY coordinates, stored as doubles.
a) Some records may find only 1 ‘match’ – Copy the ID value (a long number) from table 2 into a field in table 1
Some records may find >1 match – Create a comma separate string of the IDs and write them to a different field in table 1
c) As ‘a’ but with the coordinates round to whole numbers
d) As ‘b’ but with the coordinates round to whole numbers
Currently, I’m looping through table 1, passing the coordinates as variables (together with an empty ID variable)to a function. The function loops through table 2 until it find the first match, then I use the now set ID variable.
I’m a bit stuck on how to handle possible multiple matches ‘b’ and wondering whether to pass an array/collection to the function and then retrieve it’s contents in the main routine.
I’m a a bit worried about speed as effectively each time I move to another record in table 1 I have to search every record in table 2.
Any ideas?
