Advice for a begineer

[SteveFlash]

Hi! I am working on a database hobby project and I am very much a beginner. I really need some advice because I feel like I am spinning my wheels.




I have two large sources of data. One is about 750,000 rows of user inputted data, the other is about 2.5 million rows of standardized error free data with a 10 digit ID code.


Every of the 750,000 rows of data that is user inputted can be find within the other sheet but it often has human errors so I need to match a lot of inexact data. I need to link them all up the best I can and tag all of the user inputted data with the 10 digit code. That way I do not have to relay on the user inputted data with errors and everything is standardized. The two sheets share the following columns Fname, Lname, Address and City.


Up until this point I have been trying to match all of the data through Excel but the biggest problem is how large the data is. You can't have sheets any bigger then a million rows so it becomes near impossible at one point. My plan was always to clean the data in Excel and then load it into Access where it could become user friendly.


Can anyone tell me if I am headed down the right path? Can I use Access to match all of the data easier then Excel? Am I on the right path?


Please any advice at all would be so helpful this thing is driving me crazy.

EDIT: Ha speaking of human errors, I spelled beginner wrong.

[stmoong]

You could import the raw data into Access. Then, do the cleanup and updates from there.

[June7]

A query in Access can probably help with this. Be aware Access has a 2gb size limit.

[SteveFlash]

Ok. Can you just send me in the direction of what to Google?

I know how to import data in to Access and work around a but but does Access really have a Fuzzy Logic of sorts like Excel?

[stmoong]

A couple of ways. One way is to make your own using regular expression (Regex) which allows you to do pattern matching. This can be done from Access or Excel.

The other way I could think of using Excel, is to write some VBA code which will read in the raw data files, create new workbook for processing, split the million of rows into many "system input" worksheets, and do the matching from the "human input" worksheet(s) with the "system input" worksheets. The "human input" worksheet(s) could also have more than one worksheet. Perhaps, this may not be the cleanest approach.

Then try this add-in for Excel from Microsoft.
http://blogs.msdn.com/b/business_int...for-excel.aspx

More discussion about this subject over here: http://social.msdn.microsoft.com/For...b-fb0d45713e1d

Another way is using SoundEx described here:
http://allenbrowne.com/vba-Soundex.html

You could probably test it out using a small subset of data to see which way offer the best way to match.

[June7]

Start with Find Unmatched query. Access has wildcards but won't help you in running a simple Find Unmatched query. VBA code would be required to do anything more complicated than Find Unmatched query could perform.

[SteveFlash]

Whoa thanks for the tips. See I would be able to match all of it in Excel but there is just WAY to much data. I mean for me to match the Names I needs to sort like 20 sheets in two ABC order by Lname and then re-sort everything by City to search via address and so on and so forth.

If anyone is curious this is kinda what some of the cleaned data looks like: http://i.imgur.com/pezUn.jpg

That first outlined block on the far left is the user inputted data with errors. The next four outlined blocks of data are matches which were copied from the clean table with no errors. Right now I am having a tough time deciding when a match is "good". I need to create some kinda formula to run down the first outlined block of data and decide which one of the four match cells are the best.

[June7]

Google: Access VBA fuzzy matching

Review http://allenbrowne.com/vba-Soundex.html

[SteveFlash]

Thank you June7!!! I think that is just what I need.

[SteveFlash]

When I try to use the SoundEx VBA it doesn't seem to work. When I try to compile it I get an error window that says "sub or function not defined". Any thoughts?

[June7]

Would have to know more about how you implemented the function code. If you strictly followed Allen's instructions, should work. His stuff usually does. However, I have never used this one.

If you want to provide your effort for analysis, follow instructions at bottom of my post.