February 12th, 2013, 02:01 PM
Comparing parts of fields or a string
Hi. I have 2 databases each with a similar structure. Both databases have a vehicles table, a parts table and a vehiclepart table which essentially links parts to vehicles.
The access database runs off an id - say code1
The SQL database runs off its own id, but also has a link in a seperate table cross referencing to the code1.
What I want to do is replace the vehiclepart table in the SQL database with that of the access database BUT there are some annomolies with the codes and how they link.
We have a vehicle in the access database that is code 111 - This is one vehicle listing and has engine codes listed as AAA,BBB.CCC
However in the SQL database this exists as 3 seperate vehicles, one as AAA, another BBB and finally CCC.
In the notes section of the access database it says that the part fits AAA,CCC - so if I link it only based on the code1 cross reference it will be incorrect as the vehicle will link to all three codes.
What I need to do is compare the engine code string in the notes section of the access database with the vehicles Engine codes in the SQL database then if there is a match update the SQL vehiclepart table.
Both databases may display multiple engine codes on the same vehicle with commas to seperate them - How can i split these up and match only the engine codes that are relevant ???