Need help normalizing a table(s) in MS Access 2007

[AshAccess]

I thought I posted this, but I can't find it anywhere, so I'm going to post it, again. I hope there's not some kind of delay, and they both show up!

I am fairly new to MS Access 2007, and, as I get older, I am starting to have problems with my logical thinking. That being said, I would appreciate help with a budget I am trying to build. I have several Wells Fargo bank accounts, and I am able to download my transaction histories for each account as ".csv" files. I would like to be able to view and analyze this data in MS Access, but I am having difficulty at the most basic level (at the point that I understand to be "1NF").

When opened in MS Excel, the data is presented in this way (I apologize if there is a better, or more preferred method of presenting this):

A1 = Date (with format 1/1/2023)
B1 = Number (this is a financial transaction, but doesn't have any formatting other than a minus sign for payments, withdrawals, etc).
C1 = * (all cells in this column only have an Asterisk, not sure of why)
D1 = (all cells in this column are blank)
E1 = (this is the problem cell... This column contains the majority of the information that I would use to populate fields in a table, or more than likely, two tables. Problems I have with it is that it doesn't have delimiters between the data that I would separate, and doesn't have a consistent format, that is, it doesn't always contain the same type of information, or in the same order).

Examples of data found in this column are:

E1 = GWM LLC UTILITYPMT 181220 XXXXX1136 JOHN SMITH
E2 = PURCHASE AUTHORIZED ON 12/18 CARL'S JR #1100133 FRESNO CA S42231180056323 0 CARD 1234
E4 = AAA INSURANCE PAYMENT 221218 990141116932674 SMITH

And, so on...

If I go to the Wells Fargo website and copy and paste from the online transaction history, into excel, I can bring some formatting with it, and what I feel is some helpful separation. This method looks like this:

A1 = Date (with format 1/1/2023)
B1 = same as E1 is the previous example
C1 = $1,000 (formatted as Currency, this column is for deposits, credits, transfers in, or other money coming in, and is empty unless the transaction is one of those types).
D1 = $1,000 (formatted as Currency, this column is for payments, withdrawals, transfers out, or other money going out, and is empty unless the transaction is one of those types).
E1 = $1,000 (formatted as Currency, this column is the balance amount, post-transaction).

I am not concerned about the currency transactions, at this time, because I think however I choose to handle those, it will be fairly straight forward. My issue is breaking down the trouble cell (E1 in the former example, B1 in the latter) into useful fields in Access. Because the information doesn't use consistent formatting, my first (Excel-based) thought is to use an if, then, type statement to populate another field in an existing table (Like if the field contains "GWM LLC", populate the field "FriendlyNa me" with "Water & Trash Bill" or, if it finds six numbers with a space on each end, assume that's a date, and format it accordingly (yes, I can see issues with that one, but just visually skimming the information I have, it seems like it could work...). Again, these are just my first thoughts, but seem viable. And, being fairly new, I hate to jump into the VBA code pool, too early. Is there another way? I figured your experience would help me in avoiding any pitfalls, and in recommending a preferred method. Another thing that I'd like to keep in mind, is that this won't be a one-time thing. I will continue to get data in one of these ways (copy & paste, or via .csv files), and it will always be in the non-preferred format, so this will be an ongoing process, and therefore, as much automation as possible would be preferred. I apologize for the post length, but I wanted to make sure I explained to the best of my ability.
Thank you, in advance,
Ash

[NeoPa]

Hi Ash.

Welcome to Bytes.com.

New threads always go into a moderation queue before they are available publicly. It is only frustrating that this is not made clearer to new members when they first post a new thread. In this case, as you'll see, your post is perfectly fine and should continue to be visible going forward.

As for your question, it seems it boils down to the fact you have some data that even you don't know the reliable format of, or logic to. That will always be a problem. If you don't understand the format how can anyone expect to interpret the data automatically - as in how can you program for a format you don't know?

As such, if I'm to be of any help at all, I must limit myself to some very general observations. A shame really as you seem to have done a pretty decent job of explaining the situation. I know at least that the major issue is dealing with column E of the first type of file and/or column B of the second.

So, the data is pretty much in the same format regardless of which one we are dealing with (I understand). Nevertheless it seems to vary between records. What I would look to do is to write a Function procedure in VBA in your database that has the understanding of the data within it. I understand this will start off as very limited, as your understanding of the data starts as very limited, but that you can add to it as you proceed. The Function would be provided with the full data from column E (or B, depending) and an extra parameter that tells the code what type of data is required out the other end (Return Value).

The Function would have to be coded - once you understand the data well enough to do so (and this could be added to over time as your understanding evolves) - in order to extract different data from the string depending on which element has been requested and what it recognises to be the format of the data as presented - as we know the format can change based on other factors we know not of - as yet.

I'm afraid, based on the little we know at this point, that's as much as I can say.

[AshAccess]

NeoPa, Thank you for your prompt attention to my problem! As I suspected... I guess the next step is to start brushing up on VBA code. Without knowing too much about it, I'm guessing I'll be able to utilize some sort of "like", and "if, than, else" type queries to cut down on some of the grunt work (there are a lot of repeating entries, as I eat at certain restaurants close to my work, on a semi-regular basis, and of course, the monthly bills), but a lot of it will probably be down and dirty manual data entry, for those less often, and one time, purchases. Until I get more comfortable with the necessary code I was thinking it might even be easier for me to do some manipulation in Excel, before importing it to access. I feel guilty even saying that (typing it) out loud, because I fully know there's nothing Excel can do, that access can't, but there's that learning curve... Yes, I was hoping for the magic bullet, but I'm still a firm believer that anything is possible with Access, so I'll keep plugging away, learning, and becoming a better person, because of it!
Thanks, again,
Ash

[NeoPa]

Hi Ash.

In a way it's true to say that you will probably require some VBA skills in order to create even the framework within which this can work. However, it's also true to say that a major ingredient, and one that will need to be added in early, is an understanding of the logic you're trying to implement. The framework can never be enough on its own - but you're right to think it is a necessary part of the process.