Search for string in string in Table (1 Viewer)

[nobby]

Hi
I currently downolad my bank statement via excel and add a code number to each entry in order for me to see where I am spending most momey
IE

Code...........Shop.........Date...........Amount
1................Amazon.....1/1/18........£100
2................McDonalds..1/1/2018....£9.99
1................Ebay...........1/1/18.......£54.00

So I would have code 1 as electrical items and code 2 as eating out etc

I currently have to manually add each code which is not time efficient especially when I may have visited McDonalds 15 times in the month etc
So, using access I guess I need a master table with
Code.......Shop
1............Amazon
2...........McDonalds
and then something like
if the first item on my Statement is Amazon then put code 1 in my Statement Table if not go on until the shop in my Master table matches the entry in my statement
once this is done it needs to look at the second item on my statement etc

Any help would be great

 

[isladogs]

Create a code type table with an ID field & code field.
Populate this with all codes you will use in your statements

In your data entry/edit form, add a combobox with the new table as row source containing the different code types

P.S. Suggest you visit McDonalds less often!

 

[plog]

If every shop goes to a distinct code and that's the only way you determine the code, no need for you to add any data to your Statement table, nor use a form.

Set up your table as you described:

ShopCodes
SC_Code, SC_Shop
1, Amazon
2, McDonalds
1, Ebay

Then all you need is a query:

SELECT SC_Code, Shop, Date, Amount
FROM Statement
LEFT JOIN ShopCodes ON SC_Shop = Shop

That pulls in everything you need. Further, if the SC_Code field is blank for a record then it means you need to add that Shop to the ShopCodes table.

 

[nobby]

Thanks for the quick return

HAve also decided on a lifestyle change in view of your comments

Now going to KFC

 

[isladogs]

Thanks for the quick return

HAve also decided on a lifestyle change in view of your comments

Now going to KFC

LOL - Glad to had an impact on your lifestyle
Of course that's one more code type for your db

 

[Mark_]

@ Colin,

McDonalds is one of the few places you get get decent oatmeal. One that I was going to even added some vanilla creamer along with the fruit. Nutritionist said it was the most healthy item the served! Pretty tasty also.

 

[isladogs]

Hey ...I was making a joke in response to this...

especially when I may have visited McDonalds 15 times in the month etc

though if I'm honest I've probably eaten a McDonalds less often that that in my life

 

[nobby]

I was also joking about the McDonalds trips

 

[nobby]

Have reached this stage, wonder if you could take a look
I have a table that has the first x characters of my Supplier (TblSuppliers) IE Card payment to McDonalds ( 26 characters in this case)
I than have my statement with Card payment to McDonald's gillingham 123456

I guess I compare the first 26 characters of my Suppliers and if it matches the first 26 characters of my statement I add a code and iff not move on

But cant quite work out how to do this
I attach a zip of the tables
Thanks

Rob

 

[isladogs]

I can't see any simple way of populating the spend code field using an update query.
You can't use character count as you have different entries with the same count.
Similarly you can't use e.g. Card payment as an indicator as that is included in more than one spend code.

You might be able to devise a function that covers every possible outcome but I still think it would be both easier and more reliable to enter the code manually using a continuous form for your statement records with a combo box listing the different spend codes

 

[nobby]

OK Will rethink it

 

[plog]

Every single value in TblStatements.Shop contains ' PAYMENT TO ' and then the payee. I would use that to extract the payee from your data.

So you use Instr (https://www.techonthenet.com/access/functions/string/instr.php) to find that phrase, Mid(https://www.techonthenet.com/access/functions/string/mid.php) to extract what comes after it. You could even just grab all the characters up until the next space so that:

"Card payment to McDonald's gillingham 123456"
becomes simply
"McDonald's"

I don't know how that effects the full dataset but would work on the limited one you provided. It also means you have to rework your TblSuppliers, but that just means you have less records.

 

[nobby]

Hi And thanks for the reply
the temp dbase I sent did only have payments to ... but I have refund from... transfer to .... transfer from..... Direct debit to.. thats why my supplier list would have transfer to fred smith 22 characters so I can compare this with the first 22 characters on my statement

I've looked at using a form with a combo box from my suppliers table and trying to compare this with a subform of my statement to try to filter by my combo box. I could at least add a code in bulk but having probs with looking up x characters in the list

 

[Mark_]

Are you trying to do this as a simple "Who do I pay most" or is this "What do I spend my money on the most" project?

As is you are able to say who receives money from you, but it does not include the reason for payment, at least as has been explained.

As an example, if you spend £100 with Amazon, what type of expense is this really? Is it for entertainment? Is it for a present? A book required for something else? A replacement part for a system you need?

Often when you start by asking "Where do I spend my money" you find you are really asking "How do I spend my money". This means you will want to track payments at McDonalds based on "Was it just lunch for me" or "Was I taking someone out".

Were I you, I'd think about this for a bit before getting deeper into how you read in your statement. Assigning to a reason is normally manually done as you will know why you did something.

 

[nobby]

Yes, good point, I notice that I spend money via PayPal which could be for a variety of things