IFFs and splitting in a query

[kupe]

I must split a number of sentences into solitary words.

I have a query and I need an IFF or a split function - or heaven knows what - to take each sentence, of up to about a dozen words, and put each word into a separate field.

Craftsmen, many hours have gone into this project. I know that an IFF would/should do it, but for the life of me I can't find the correct construct. Most of the weekend went this way, and failed miserably. (I almost kicked the cat when I left this morning.)

If anyone has something similar, I would be so relieved to see it. (So would the moggy, I think.)

 

[Mile-O]

Here's my attempt:

 

[Mile-O]

....but why?

 

[kupe]

Request of the User. The individual words will be counted and their relative popularity gauged, I gather. Off to lookat your form, and will report back. Many thanks, Mile-O-Phile.

 

[Rich]

Then you're better off having the words listed as separate records, not fields.

 

[kupe]

That really is very impressive. You must be worth a fortune.

What it needs to do is to put each of the words into a separate field. IDs don't matter so that should make it easier.

At the end of the project, I will count the number of words in each field, and report that to the User. He will link to the databases to manipulate the words in various ways in Excel.

But what you have here looks really marvellous. (What a pity I can't persuade the User to adapt his demands to your provision!) Cheers

 

[kupe]

Hi Rich

In a way, but won't they be handled better if all to do with each batch of sentences, or phrases, is kept in the same database?

All to do with, say, dairy farming herded into tblJerseyBreeds, for instance, and all to do with, say, scuba diving into tblTheDeep? Cheers

 

[Mile-O]

Like this then?

 

[kupe]

No, you were right first time, Mile-O-Phile. My mistake. You'd thought it through further. Very sorry.

Obviously this second way, there could be a billion fields, or as many fields as variations of words that occur in the sentences.

I'm really sorry for putting you to the trouble of the second db. But if you don't mind, I'll keep that code to learn from - or try to learn from. (From this present perspective, it's a bit like a village chemist trying to grasp the theory of splitting the atom.)

What I should do now is to group and count the words in a query. I'm guessing here until I try it.

Please know I'm really amazed by the look of it and your speed. Oh, if my father had given me an Amstrad (?) handbook when I was 4. I think I was having enough difficulty coping with Wind in the Willows then. Wasted infancy! I salute you, master-craftsman.

 

[Mile-O]

lol, no probs

 

[kupe]

And it seems to work out, Group and Count in a totals query. Brilliant, Mile-O-Phile.

And, when you have a moment later, could you say if you wouldn't have done the work in a query? Cheers

 

[Mile-O]

Could you say if you wouldn't have done the work in a query?

No idea. Head ain't feeling too hot today. Need beer!

 

[kupe]

Not close enough to buy a round, sorry. Thought north of the border the tipple was a bit harder. Thanks for all your help. Cheers