I have data from three sources that is not similar. Each is an Excel spreadsheet that I have imported into Access. Each has a field called LOCATION. The tables are Site1, Site2 and MasterList. The MasterList is the correct format. Here is an example of the data I have in the LOCATION field.
Site1 - New York City - CS3 - North Side
Site2 - Con1, NYC-NY - CS003 - In work
MasterList (Two Fields)
Location - NY NY
LocationCode - CS3
As you can see, the sites report the location combined with the location code and sometimes some other junk data. The only thing that is important for this process is the location code (CS3). In addition, site2, for some stupid reason, converts all the location codes into a 5 digit code by filling in zeros.
Using individual update queries, I can update a field I added to each Site table (LocationCode) to the correct code with the criteria of Like "*CS3*" and update the LocationCode field to CS3 . The problem is there are nearly 247 locations and codes, and since one site adds zeros, that means double the number. I really think there is a better way than making almost 500 update queries.
I though of a single SQL located in the On_Click event procedures of a button, but it doesn't seem to work.
1 - Is there a way to match the Site tables to the Master Table or do I have to manually input each locationcode?
2 - If I have to manually input each code, would someone please be kind enough to provide an example of what it would look like in the event procedure.
3- Thanks!
BTW - One end goal of this task is to show just how out of whack the data is to force the sites into submitting data in a standardized format.
Note: Text to column in excel before importing is not a good option either as the delimiter may be used multiple times in the field and I would have to manually clean up the data anyways.
Again, thanks!
Site1 - New York City - CS3 - North Side
Site2 - Con1, NYC-NY - CS003 - In work
MasterList (Two Fields)
Location - NY NY
LocationCode - CS3
As you can see, the sites report the location combined with the location code and sometimes some other junk data. The only thing that is important for this process is the location code (CS3). In addition, site2, for some stupid reason, converts all the location codes into a 5 digit code by filling in zeros.
Using individual update queries, I can update a field I added to each Site table (LocationCode) to the correct code with the criteria of Like "*CS3*" and update the LocationCode field to CS3 . The problem is there are nearly 247 locations and codes, and since one site adds zeros, that means double the number. I really think there is a better way than making almost 500 update queries.
I though of a single SQL located in the On_Click event procedures of a button, but it doesn't seem to work.
1 - Is there a way to match the Site tables to the Master Table or do I have to manually input each locationcode?
2 - If I have to manually input each code, would someone please be kind enough to provide an example of what it would look like in the event procedure.
3- Thanks!
BTW - One end goal of this task is to show just how out of whack the data is to force the sites into submitting data in a standardized format.
Note: Text to column in excel before importing is not a good option either as the delimiter may be used multiple times in the field and I would have to manually clean up the data anyways.
Again, thanks!
