Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > Courses > Access > Beginner > B5 > < B4 | B6 >
Back to Access Beginner 5    Comments List
Legacy DB Bad Data Upload Images   Link   Email  
Charles Milledge 
I have inherited the maintenance of a legacy Access database.  This database gets its data from our company's Unix based operations software via a nightly update.  The Unix based system has significant field size limitations with regards to customer name (which is a company name).  The customer's company name field is limited to 23 characters.  It stores two fields worth of data, Address Line 1 and Address Line 2 as well.  Both also are limited to 23 characters each.  The current data has about 950 customer records.

As a result of this field size limitation, a significant amount of the customer's company names will not fit into the company name field.  The person who enters that information for our company insists on the whole name being shown in the system so they have repeatedly entered the "balance" of the customer's company name into the address line 1 field and then put the data that would go into address line 1 into the address line 2 field.  This has created a big issue when trying to develop new upgrades to the existing legacy database such as utilizing mapping integration for example.

Obviously, I'm not restricted to 23 characters in the Access legacy database but there is no way to expand the field size in the Unix based system.  My work around to this point has consisted of creating a separate manually updated table in the back end called "tblCustomersStatic".  This table consists of the customer's company name, a separate company "DBA" field in case that is different than the company's legal name, corrected address line 1 field, and a corrected address line 2 field. The two tables are related via the company's ID field which is the customer's account number.

Since we don't often add many new customers, maybe a dozen a year on average, my work around has been to run a "missing records" query which picks up any new customers that I need to manually add to the tblCustomersStatic table once per week.  I then go to the tblCustomersStatic table and add the missing data to those fields.  I then run an update via VBA to refresh the true tblCustomers table and the problem is resolved.  That's worked fine for the last several years.

I'd like to automate the process as much as possible so that, God forbid, I'm not there someday, the updating can continue on.  Over the years, I've tried various techniques of using query criteria, in string searches but the data can be so random as to length, characters, numbers, etc. that I can never seem to account for all possible outcomes to get the data correctly broken into the corrected fields. That's also why I'm posting the question here at it seems to fit in with this lesson.

My question is...

Are there better ways to address this issue?  Has anyone else had a similar problem and how did you approach it? What degree of success were you able to achieve?

Any guidance would be greatly appreciated.

P.S. telling the person who's entering the data in the wrong fields is also not an option.  I already have.  She refuses to change because it's her firm belief that the information must be in there completely so that if we ever take a customer to court over an outstanding debt, if the customer's name isn't in there in its entirety that we would automatically lose the case. The funny part is, I've worked at the company for 27 years and in that entire time we have never, not once, taken a customer to court over an unsettled debt.  Even when it was possible to do so and even when it was a significant amount (five figures). They have written it off every time.  So, it's pretty much like trying to explain calculus to a donkey.  She also owns the company and is my boss, so it just ain't happenin'. :)

P.P.S.  Richard, I've taken classes on Access from nearly every major platform over the past four years - Linkedin Learning, Udemy, etc.  I think your lessons are better than any I've taken.  The only other instructor I've found that's close is Bruce Myron who has his classes on Udemy.  However, your structure and progression is much more thorough than anybody, bar none.  Thank you for sharing your knowledge and experience.
Kevin Yip
Hi Charles, you probably need to show us what the source data look like.

For instance, if an address in its proper form looks like this:

The Joseph L. Morse Geriatric Center
4847 Fred Gladstone Drive
West Palm Beach, FL 33417-4847

How would it be entered in the legacy database?  With only 3 lines of 23 characters each, there may not be enough space.

Are words cut off at the end of a line?
Are abbreviations used?
Are commas used as separation?
Is some info omitted, such as city (since zip code usually suffices without the city)?

For instance, below is what I would think the above address could be entered into three 23-character lines:

Joseph L Morse Geriatr
ic Ctr,4847 Fred Glads
tone Dr, FL 33417

If there are comma separations, you can concatenate all the lines together and separate the fields based on where the commas are, using the VBA function Split() to split them into separate fields.  Sample VBA code:

Dim s As String, a As Variant
s = "Joseph L Morse Geriatric Ctr,4847 Fred Gladstone Dr, FL 33417"
a = Split(s, ",")

If you can't rely on comma separations, then you have to find a way to "parse" the address yourself: browse through every address with your own eyeballs, manually put a "delimiter" between fields you want to separate, then use the VBA function Split() as shown above.  With only 950 addresses, that would be quicker and cheaper than looking for an "address parser" to do the job, or developing or hiring someone to develop it.  Yes, 950 is still a lot, but by my estimate it would only take about 4 days, with 2 to 4 hours per day, if you took 30-60 seconds to add delimiters to each address (30-60s times 950 = 8 to 16 hours).

The above method is for transferring the data to a new, improved customer table in Access that has more space in each field and more fields.  Obviously, you need your users to start entering data into the new tables when the transfer is finished.
Charles Milledge
Thanks Kevin!  I appreciate your thorough response.  Unfortunately, I'm not able to share the data as this is a real working database with real businesses and people's info.  I wouldn't want to post someone's business information without their permission.  But I do follow your answer and will consider using your approach.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Beginner 5.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/4/2024 8:11:01 AM. PLT: 0s