Link to home
Start Free TrialLog in
Avatar of clchapman
clchapmanFlag 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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of clchapman


Thank you for responding. I had to move on to a different project, but will try your suggestion later this week. Thanks again