Avatar of clchapman
Flag for United States of America asked on

Manipulate Data in Access 2007

I am working on an Access database that pulls two data sources from Excel. Due to HIPPA and confidentiality compliance I cannot provide real examples or actual data, but hopefully this will make some sense.

Data source one includes a column for a contract name; data source two supplies the information in two separate columns and in different formats. For instance:

Data Source 1
Mickey Mouse Basic Plan
Mickey Mouse Deluxe AB123 Plan
Minnie Mouse Basic+ XY987
Donald Duck Basic Plan
Donald Duck Deluxe+ Plan
Daisy Duck Premier AB987

Data Source 2 (in the same order)
MM Basic Pln
MM Deluxe Plan          AB123
MinM Basic+                XY987
DonDuc Basic Pln
DonDuc Deluxe+
DaDu Prem                  AB987
I need to match the columns from data source 2 to contract names in data source 1 in order to incorporate additional data from source 1. The only thing I can think of is creating a query of each table, using various functions (Replace, Left, Len, InStr) to manipulate the text, then joining the queries into a separate query that includes all needed data from source 1. The problem is because of the number of contract names and inconsistent formatting, it has taken 20 such functions--so far. The result is a slow running query. There has to be better way.

Although I am a novice with Visual Basic, incorporating code is an option; however I am not sure where to start (more specifically, I have no idea where to start).

Any thoughts, suggestions or ideas?
Thank you
Microsoft AccessVB ScriptDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you for responding. I had to move on to a different project, but will try your suggestion later this week. Thanks again
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.