Difficult to explain yet simple problem...!

[richo2]

Hi all

I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example:
I want this: AUTO ONE CLARKSON \CLARKSON WA \AU006495
to be recognised as: car parts

or with this tranaction:
CLARKSON MINI MART CLARKSON553908
look for "mini mart" and return supermarket

This step will make classifying transactions a lot simpler and user friendly.
thanks

 

[gromit]

Hi - welcome!

If you can ennumerate all the potential lookups, then you can use the InStr function. You can also use the LIKE clause of SQL for matching.

My concern would be whether or not you can uniquely search the payer/payee without hitting duplicates. E.g. what about "Auto Ease Mini Mart"? Would that register as a car parts AND mini mart...

My approach might be to create some sort of common look up basis and then have the user confirm.

hth,

-g

 

[richo2]

I tried using the instr function but this does not work.

Expr1: InStr([Andrew].[Payer/payee],[categorys].[raw category])

With this expression it will only work if the raw category entirely matches the payer/payee, which is no good - I would have to make infinite combinations of spaces and numbers for this to work every time!! I don't know why it is not working with table values, but this technique is the closest I can get to without using the like operator and typing in unique keywords in the query code.

Maybe I can convert the table to code and refer to it that way?

 

[gromit]

You can use wildcard characters in SQL queries which might offer some additional flexibility.

Again, I think that the best you can hope to achieve is a reasonable guess with a confirmation by the user.