Split Fields and Look for Updated Match??

[Hey Lucy]

I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:

Street Number
Street Name
Street Type
Street Direction

And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.

I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.

But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.

There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.

This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.

Anybody have any viable ideas that doesn't include any VB programming?

Thanks!

 

[jdraw]

I'm sure most of us have done that or a reasonable facsimile over time. There have been careers spent trying to do what you are asking. As a matter of fact most Post Offices and 3rd party orgs doing data cleansing have commercial software to do this.

It is highly unlikely that a query or macro will suffice.

You need to get some answers to some basic questions and then lay out a strategy.

What makes a duplicate a duplicate?
How many records are you dealing with?
It seems that need data enters the system unvalidated at this time, correct?
How important to your organization/company is absolutely no duplicates?
For instance, Banks will often send you multiple "info emails/flyers" based on the number of accounts you have, NOT limited to your 1 address.
Can you identify potential duplicates using - phone number, zip and First/Lastname?

If you are going to parse every address then
-get a guide from your Post Office re proper street type names and abbreviations.
-get a table(s) of the accepted names and abbreviations
- set up modules (VBA) to do various parsing,- functions that can be reused
- you will need to be familiar with Inst, Mid, Left, Right and vba generally
- set up validation on the way in, no exceptions

You can get some info from suppliers on Address validation/verification programs/software but these tend to be more expensive than management "ever thought of" and it's likely you'll be working in the innards of vba more than you thought.

In any event, the forum is here to assist where possible.

 

[Hey Lucy]

Thanks for your response and insight. I have not found a viable way to do this in Access, although I can do it in Excel, then import, so that's the route I'm taking.

This database is for a real estate company to keep up with all their listed properties. Therefore, Address and City are the identifying factors. Presuming there could be a 123 Main Street in Georgetown and a 123 Main Street in Aubrey, I have to use the Address and the City to identify duplicates. But, as I said, if Record 1 is 123 Main Street in Georgetown, and Record 10 is 123 Main St in Georgetown, it is the same property and one should be removed.

I have spoken with my client and he has opted to go with the spreadsheet conversion that i've created, as he downloads two files every day to be imported into the database. One file downloads as an Excel file and is easily transferred over to the Excel file where I have parsed these addresses, then imported into the Main Table in the database. The other downloads as a .txt file, but I have also set up a spreadsheet for that one to be imported into.

I have created both spreadsheets with macros to automate, so all the client has to do is download his two files, open the two spreadsheets and run the macros to import, set field names to match, add field names not present, and identify and change abbreviated Street Types and Cities (some of the cities download abbreviated).

This works great and is the best option at this time.

I thank you for your thoughts into what I may need to do.