extracting data from text fields... complex?

[wiklendt]

The CharCount user defined function is in the only module and checks for the occurences of the 2nd parameter in the 1st paramenter. The FALSE just signifies not case sensitive.

aren't you a little genius thanks for that - i'll check it out when i'm back at work this morning (when i get paid to work with this mostrosity!)

You data really needs to be normalised but that is a bigger job.

yes, i know. i may yet have to do that for future posterity.

If you ever need help doing that just send me a msg.

much appreciated - sounds like you had fun, which reduces some of my guilt about you working on this while i was sleeping

Here is the beach: http://www.soton.ac.uk/~imw/chesil.htm that I'm off to now. 18 miles and 18000000000 pebbles. Small tiny ones at one end and big heavy ones at the other and still unexplained.

unexplained? aren't all british beachs just pebbles instead of sand? LOL no, i get you, different sizes... it's quite a long beach, though.

You owe me a big Ice Cream.

you're not wrong.

this is my closest beach (about 2 min walk form my front door)
http://www.centralcoastaustralia.com.au/visualGallery/slideshow.asp?ID=36

 

[wiklendt]

you are a genius highandwild - it works (in my "real" database).

i've added your credentials to the code, by the way - is there anything in particular you'd like to add (i've added your username and the url to this post)

You must spread some Reputation around before giving it to highandwild again.

 

[wiklendt]

You have missing data in some of your [notes].

i know it seems that way, but if you pair it with another of the fields (GrowthIndex) the ID=1 is actually "no growth", and often there was nothing to 'note' for these instances.

highandwild, the code you wrote works a dream. it's allowed me to extract practically ALL the little bits and pieces from that table, and it will DEFINITELY play a big part in normalisation (now that we've to the data extracted, we can normalise it). my supervisor is uber impressed (he knows i didn't do it on my own, but he's impressed anyway!)

thanks again for your brilliance.

 

[highandwild]

thanks again for your brilliance.[/quote]

Thanks Wiklendt

We all know things that others need to know and we all can do things that others need to do, the problem is pairing people off. That is what the forum is for.

 

[wiklendt]

We all know things that others need to know and we all can do things that others need to do, the problem is pairing people off. That is what the forum is for.

LOL, didn't we just snap together, then!

 

[namliam]

I would have done something like attached...
Check out tblDetailsNormed, which is created from Module2 / TestNorming

Now this module/code you can all kinds of logic too, but once you have this normalized data, getting going with reporting is easy.... Even just building a cross tab...
Or perhaps first cleaning it up a bit...

Hope you find it usefull

 

[highandwild]

That is a good start namliam.

A great deal of checking would need to take place before trusting the data, but there again, how much can one trust data that has been entered manually instead of comprised of data selected from reference tables.

I notice that there are some question marks in some of the records. What would these mean, if anything other than the user entering the data did not know what to enter or was not a hundred percent sure of what they did enter.

I spent years sorting health service data out and it is an all time consuming exercise and not something I want to do again.