Match and replace fields?

[Katskratches]

I've been teaching myself Access mostly by Googling what I need. I'm sure I have some gaps in my learning and I feel that this question has likely been answered before. However, I don't know what to search or even the category to search in.



I am trying to build a query that will match fields and correct errors before I try to load it into a different program. I have a linked table (Table A) with the records I need to load into a system. Note that the linked text file is from a third-party source. I also have a linked table (Table B) with basic information from the system itself. In our system, I receive errors in loading the raw data file (Table A) because of character issues on name (Smith-Jones will generate an error because the system has SmithJones). Table A does not include unique identifiers such as IDs. Table B does include unique identifiers.

I know I can do an unmatched query, but that simply tells me the potential errors. I want to somehow either able able to replace the names in Table A records with the matching name in Table B OR simply pull in the unique IDs from Table B. If I have an unmatched query, I will know which records need this, but I don't know how to fix them and then get them all back properly to the linked table A to export. I know I can't edit a linked table. Are there any additional steps I can take to try to simplify this process? Am I approaching the whole thing wrong?

Sorry this was long. Wanted to provide adequate context and details. Again, I'm sort of learning as needed, so I appreciate the help!

[June7]

There have certainly been many discussions on this. Often called pattern matching.

Correcting data like this can be a tedious, time-consuming process. Access can't read minds. How should it know that Smith-Jones equals SmithJones? This will likely require you to do case-by-case review of each record in unmatched query and make a decision on correction.

[Katskratches]

There have certainly been many discussions on this. Often called pattern matching.

Correcting data like this can be a tedious, time-consuming process. Access can't read minds. How should it know that Smith-Jones equals SmithJones? This will likely require you to do case-by-case review of each record in unmatched query and make a decision on correction.

Sure, yes. I totally understand that. That's why I veered toward an unmatched query. But from there, I guess my question is what is the most efficient next step once I work out the corrections? I can't make changes to the original linked table. How would you proceed?

[orange]

Make 2 backup copies of your database. Put 1 into a folder of your choice for safe keeping.
For the 2nd, put it into a zip file. Also put some representative data from the 3rd party file in that zip. Post that zip with the 3rd party file and your database to the forum. Add instructions to that post to help people understand the data and what you want to achieve --even give a few examples.

As others have mentioned, cleansing data has become a life-long task for many. You identify patterns; solve each pattern and test it vigorously with lots of "strange test data". Often easier if you identify what would make some names "difficult", create some test data with those attributes and test same. As they say, rinse and repeat.

Here are a few test names from one of my routines.
"Mrs Patricia Donna Geraldine Jones-Green"
"Ms Ali vant Goor"
"Oscar de la Hoya"
"Ms Patty O'Reilly"
"Miss Chloe de Riems-Cotret"

Good luck.

[June7]

What do you want to do? Are you importing data from the linked text file? Are you using INSERT action sql? How many variations in name structure are there? Is it just hyphen? Could a name have multiple variations?

Exactly what are the errors you get when 'loading raw data file'? Do they occur when linking or when running SQL?

As orange suggested, can provide file for analysis.

[Katskratches]

Hopefully, I can provide additional clarity here. I work in a school system. Each month, I receive a disc containing a text file from ACT that has scores for any students who sat the test. What I do is upload that txt file directly into our Student Information System (SIS). The text file from ACT tries to match by name and DOB only. If those match, it loads. Obviously if not, it doesn't and I receive the error report. I don't receive any error reports in Access. Sorry about that confusion.

What I am trying to do is stop the errors before they happen. I can almost pinpoint where I will have errors because of the name discrepancy. In Access, I've created a linked table to the ACT txt file (the file layout is fairly complex and tab delimited, so I need to keep it linked so that I can just update it monthly). I've also created a linked table to a text file containing information to the students in our system. With those two tables, I run an unmatched query to identify where I will have errors. My main question is what can I do, once I have the unmatched results, to "fix" the linked text file? OR will I have to just pull in the file and not link it so that I can actually edit the records? I want to mess with that file as little as possible so that when I export it again, the layout will be unchanged.

I'm been toying with learning VBA to write scripts in Excel, but am not yet savvy in SQL, although I was just introduced to it last week in Jasper, so I hope to learn little by little. So I don't think I'm running SQL? At least, not anything that I've written beyond what's already inside the programming.

I've been trying to find an Access training place, but I have the basics down (at least, I think I do) so at this point I'm not even sure what I should be learning. I think there are many processes that could be helped if I could increase my knowledge Access. If you have any resources that I should look at, please feel free to share. I don't think I'm a lost cause quite yet.

Orange, to your point, I can't share the database (it's only two linked tables and an unmatched query anyway, nothing fancy). However, at the end of the day, I need a way to change either the name or DOB in the ACT linked table so that I can export it to a txt file.

It may be that I have to avoid the link table and instead build the file each month, but I'm really hoping that isn't the case because there are well over 150 fields.

Thank you again! I'll ask a better question next time.

[orange]

Well it's your call, but when faced with such a predicament you could:

make a copy of your limited database,
adjust the names to things like Sheesa Student, Porky Pig etc
create a test text table to resemble the ACT file (same format but different data)
then zip and post,

we don't need or want real data. We want sometging sufficiently similar to design and test some routines to help solve your issue.