Match partial text string and update record

[lscheer]

Thanks to some complete MORON who obviously doesn't know the basic concepts of database development and implementation, I am in the unfortunate position of having to work around their stupid constructs to try to get useful data into our own internal database. And now for my convoluted description of what I am after...

I am provided (from the MORON-database) a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process. What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).

After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.

Type Descr PreEnc Enc Exp
VOU Jsmith-Instructor, 12/16/13 $0.00 $0.00 $45.00
PO JSmith-Instructor, 12/16/13, Course1, Parking ($45.00) $0.00 $0.00
REQ JSmith-Instructor, 12/16/13, Course1, Parking $0.00 $45.00 $0.00
VOU Jsmith-Instructor, 12/16/13 $0.00 $0.00 $221.13
PO JSmith-Instructor, 12/16/13, Course1, Lodging ($221.13) $0.00 $0.00
REQ JSmith-Instructor, 12/16/13, Course1, Lodging $0.00 $221.13 $0.00


I know this is a messy situation, so I don't expect a simple solution, if one even exists; my coding skills are not that advanced, however I can typically troubleshoot my way through most situations if I at least know where to start. Any suggestions are much appreciated.

 

[CJ_London]

I can see your example - from this can you provide what it is you want to end up with.

You haven't made clear (to me at any rate) which of the 3 transaction types you want to include or what is in the other table that you want to pull through - the full decription - or how it partially matches - beginning, end, somewhere in the middle?

 

[lscheer]

Apologies!

I am interested in utilizing the VOU transaction record only, but pulling the Descr string from the PO or the REQ record (either is fine) that matches the first 30 characters of the Descr string in the VOU record. However, since multiple (but not related) records might match the truncated Descr string, I hoped to differentiate them by finding matching values in the ENC and EXP fields for the different records.

Unfortunately, these are all in the same table!

 

[CJ_London]

Sorry more questions and to paraphrase to make sure I understand

is the desc string the bits highlighted in red below?

VOU Jsmith-Instructor, 12/16/13 $0.00 $0.00 $45.00
PO JSmith-Instructor, 12/16/13, Course1, Parking ($45.00) $0.00 $0.00
REQ JSmith-Instructor, 12/16/13, Course1, Parking $0.00 $45.00 $0.00

In which case the only way to differentiate from

PO JSmith-Instructor, 12/16/13, Course1, Lodging ($221.13) $0.00 $0.00
REQ JSmith-Instructor, 12/16/13, Course1, Lodging $0.00 $221.13 $0.00

is matching exp to enc - in green?

Matching to the same table is not a problem, so if you can confrim the above I can propose a sql solution

 

[lscheer]

CJ_London,
Your assumptions are correct!

 

[CJ_London]

Since I don't have the data and don't have time to create a sample set, this sql is untested - try it and correct for table/field names.

UPDATE myTable 
INNER JOIN myTable AS myTable_1 ON myTable.Exp = myTable_1.Enc 
SET myTable.Descr = myTable_1.Descr 
WHERE myTable.[Type]='VOU' AND myTable_1.[Type]='REQ' AND InStr(myTable_1.Descr,myTable.Descr)=1

Note Type is a reserved word and can cause problems, recommend you change the name in the table to something else.