Query Challenge

[vgersghost]

I have several Databases with similar tables with similar information, but entered differently. (Different persons made separate databases before my time) I would like to make new tables, write a query to combine the information into these tables, but I need to get the data similar before I can combine.

Example of information:

7L 24 3A719 AD one table might have it stored this way
7l243a719 AD another this way
7L24-3A719-AD or 7L24 3A719 AD both ways in this table
and so on. There are alot of combinations

I have been in attempting Mid$, Right$, Left$, Like, Not Like and others to no avail.

Looking to make all the information the same layout 7L243A719AD, then I will be able to combine information.

Any ideas / help would be appreciated.

 

[KeithG]

I would make a VBA procedure to step through the string and remove all spaces and - so that all the data is in the same format

 

[erickat]

Best Bet May Be to Dump The Data

Not knowing how much data you have, you might want to export the data to a more easily manipulated format - like Excel & use a macro or vba - or drop it to text & use VB to manipulate the data & get it all standard - then put it in Excel the way you want it & import it to the new tables.

It's quite easy to get data in & out by exporting - I've been doing alot of this with our research database - don't want 200 people accessing a single Access 2003 database - so I have them enter data on spreadsheets that I design to match the tables & then I import the data & analyze it.

 

[raskew]

Hi -

Try playing around with this function.

Function fFixString(pstr As String) As String
'*******************************************
'purpose:   Remove spaces (chr(32)) and
'           hyphens (chr(45)) from a string
'           and returns result in vbUpper
'           format
'coded by:  raskew
'Inputs:    from debug (immediate) window
'           ? fFixString("7L24-3a719-AD ")
'Output:    7L243A719AD
'*******************************************

Dim strHold As String
Dim strDump As String
Dim i       As Integer
Dim n       As Integer

   strHold = Trim(pstr)
   
   '1) Make strHold upper case
   strHold = StrConv(strHold, vbUpperCase)
   
   '2) Eliminate spaces and hyphens.
   'Note if other characters must be removed, increase
   'n and add the character to the Choose() statement
   For n = 1 To 2
      strDump = Choose(n, Chr(45), Chr(32))
      Do While InStr(strHold, strDump) > 0
         i = InStr(strHold, strDump)
         strHold = Left(strHold, i - 1) & Mid(strHold, i + 1)
      Loop
   Next n
   fFixString = strHold
   
End Function

I'd first create a query that includes the field to be updated, and add a calculated expression like: Expr1: fFixString([myField])

Once your satisfied with the output, and after creating a backup of your table, create an update query with the Update To for myField - fFixString([myField]).

HTH - Bob

 

[vgersghost]

Thanks

Thanks for the response, I'll give them a try and I know they will work, because as far as I can tell when reviewing the forum, you answer every question / concern with intelligent and accurate solutions.

 

[raskew]

Thanks for the response, I'll give them a try and I know they will work, because as far as I can tell when reviewing the forum, you answer every question / concern with intelligent and accurate solutions.

Think it should work for you. Please post back with your results.

Best Wishes - Bob

 

[vgersghost]

Thanks raskew

The code worked great. Had a few left over that were placed in the tables like 7L24 - 3A719,AG and 7L24, 3a, 719AG. I just edited them manually. The code did not seem to work with combinations or I was using it worng.
Thanks again
dmh

 

[raskew]

The example was set to remove only hyphens and spaces:

'Note if other characters must be removed, increase
'n and add the character to the Choose() statement

This example increase the count from 2 to 3 and adds Chr(44) (comma) to the list of characters to be removed

For n = 1 To 3
strDump = Choose(n, Chr(45), Chr(32), Chr(44))

Bob