asked
SQL Binary Microsoft Access - Adding to existing records if specific field values are equal between two tables
I am new to Microsoft Access and SQL but have a decent programming background and I believe this problem should be relatively simple. I have two tables that I have imported into Access. I will give you a little context. One table is huge and contains generic, global data. The other table is still big but contains specific, regional data. There is only one common field (or column) between the two tables. Let’s call this common field CF. The other fields in both tables are different. I’ll take you through one iteration of what I need to do. I need to take each CF value in the regional, smaller table and find the common CF value in the larger, global table. After finding the match, I need to take the whole “record” or “row” from the global data and copy it over to the corresponding record in the smaller regional table (This should involve creating the new fields). I need to do this for all CF values in the regional, smaller table. I was recommended to use SQL and a binary search, but I am unfamiliar. Let me know if you have any questions. I appreciate the help!
Access still allows update from the join, but the syntax is different from T-SQL. Your first step should be to redesign the regional table such that it now includes all (except CF) columns from the global table and you have to make the columns nullable for now regardless of whether they are nullable or not in the global table. This is because your regional table already has records so you cannot really add not nullable columns to it. Lets say that the global table has the column named CF and also columns named ColA, ColB, ColC and ColD. After you alter the regional table to add ColA, ColB, ColC and ColD to it, you can issue an update statement which will work just fine if every CF value in the regional table has a single match in the global table (it is OK if there is no match, those rows will remain not updated): update RegionalTable inner join GlobalTable on
RegionalTable.CF =
GlobalTable.CF set RegionalTable.ColA = GlobalTable.ColA, RegionalTable.ColB = GlobalTable.ColB, RegionalTable.ColC = GlobalTable.ColC, RegionalTable.ColD = GlobalTable.ColD The statement above will at once update all those rows in the RegionalTable which have a match by the column named CF in the GlobalTable. Please note that the syntax of the query is different from T-SQL where it would probably look like this: -- T-SQL if your tables were in SQL Server database update RegionalTable set ColA = GlobalTable.ColA, ColB = GlobalTable.ColB, ColC = GlobalTable.ColC, ColD = GlobalTable.ColD from RegionalTable inner join GlobalTable on
RegionalTable.CF =
GlobalTable.CF; If there was a match in the global table for all existing rows in the regional table and the original columns in the global table were not nullable, you can at this point also make them not nullable in the regional table because all rows in these columns of the regional table now have not null values. Oleg
10 People are following this question.
