troubleshooting Question

Manipulate Data in Access 2007

Avatar of clchapman
clchapmanFlag for United States of America asked on
DatabasesMicrosoft AccessVB Script
2 Comments1 Solution623 ViewsLast Modified:
Experts,
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
COLUMN A
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)
COLUMN A           COLUMN B
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros