Query to change values to lowercase (conditionally) 2

[Carenne]

Hi everyone -- I'm going to be importing a HUGE (single field/single words) list (20,000+) words into an Access table for manipulating and exporting into another application. Initially, the list will look something like this, with many repeats of each word:

preliminary
preliminary
Preliminary
preliminary
congratulations
Congratulations
congratulations
Congratulations
America
America
America

As you can see, there will be a mix of words that appear in lower case and initial caps, and some will need to stay that way, but others will only be that way because they appeared at the beginning of a sentence or heading in the original document(s) and will need to be converted to lowercase.

I'm wondering if it's possible to do an update query on the entire list that (1) checks for multiple occurrences of a word and then (2) if *any* occurrences of the word are in all lowercase, then changing the initial caps version(s) to all lowercase, while leaving words such as "America" (which presumably would always appear in the list initial capped) alone?

Thanks in advance for your help!

Carenne

 

[BH]

Don't know if this will help but:

Have you tried using the replace function on the required table. For example put in Find what A and replace with a, If you then want America starting with a capital put in Find what america replace with America.

A little time consuming but if you can't find another method, this will work

 

[KenReay]

Hi

You could have an upodate query to change everything to lower case using LCase() Function, but that would not help you with words such as America Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[SgtJarrow]

You are probably going to have to write your own function for this one. Create a recordset of the imported data. Walk through it one by one, checking the ascii values of the first character. If all your "like" words are together, this should be fairly easy. Simply have avariable that holds the current word to compare against, and when you change words, change the variable. If they are spaced out, gonna be a bit tougher. Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Carenne]

Hi, Ken and Robert -- Good morning and thanks so much for your speedy responses! As BH and Ken suspect, it would be quite tedious to go through the whole list and manipulate each group of words, and as Ken said, trying to automate it could end up with lowercase "america," etc.

However, I have been thinking of something along the line of what Robert proposed, only completely automated so that the results list that the user sees is already cleaned up. It's absolutely imperative that the user not have to do any manipulation of the list whatsoever, as it would totally defeat the purposes of the list. However, sort of on the line of what Robert is saying, I'm wondering if this is feasible, or if I am making it more complicated than necessary --

Fist, a little more color on the function of the application. After the initial list of words is imported, the first thing the user will do is run a (predefined) query on the list to remove the extraneous "noise words" and produce an output list into a new table (also done with a predefined query) that consists of one occurrence of each word plus an occurrence count, sorted in descending order by occurrence #.

When it is producing the output list for the new table of a single occurrence of all repeat words, I'm not sure how and when Access decides which of the series of repeat words to actually use for display in the resultant output.

If my theory is correct, that it simply uses the first (or maybe the last?) occurrence of a word, then I'm thinking that maybe I could add some additional steps *before* that process takes place - to create an additional field in the query representing the ascii equivalent of the first letter and have it subsort each word by that field, forcing the lowercase version of the word (if one exists) to be first (or last, if necessary) in the list, and *then* let it pull the requisite single occurrence of each word for the output list.

I'm thinking out loud here as I read your posts and digest what you've said. Does this sound (1) doable and (2) like the most efficient way of doing it? I won't have time to figure out how that's done and/or experiment with it till after lunch, but I'm crossing my fingers that it will work, or that you gurus will throw a boulder in the road if I'm on the wrong path. [g]

Thanks again for all your responses!

Carenne

 

[SgtJarrow]

Carenne,

So if I get you correct, you havee an import with thousands of duplicated words. You want only to end up with a list of unique occurances, and a count. If any of the occurance's first letter were lowercase, the output should be lowercase. If ALL the occurance's first letter were upercase, the output is uppercase.

Sounds doable to me.....can you send me a sample file? I can probably put it together faster than trying to explain it to you....I will put together a db sample and send it back. Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Carenne]

I'm back (so much for that other fire <g>).

I just had to tinker for a few minutes to see if I could get anything going based on my last post. In doing so, I realized that, to test that theory, I don't actually need to deal with the ascii value of the first letter, but only whether it's upper or lowercase.

So I was thinking that I would create a new column in the query that checks whether the first letter of a word is uppercase or lowercase, and puts, i.e., a 0 for lowercase and a 1 for uppercase. And then the words are subsorted by that column, followed by the final update query which outputs a single occurrence of each word.

But now I'm having trouble formulating the new column. I can't seem to figure out the syntax for the command to ultimately output the 0 or the 1 in that column, and I can't find any examples in the help file or my manual(s) for what I need.

In plain English, the command I think I'm looking for would look something like:

[If the first character of the word is lowercase] [then this field =0] else [if the first character is uppercase] then this field = 1]

Can you help me put this in its proper form?

Thanks again!

Carenne

 

[Carenne]

Hi Robert - I saw your latest post after I submitted my last question. I'll be happy to send you a sample. It will be forthcoming shortly. Thanks! -- Carenne

 

[SgtJarrow]

Kewl....in the interim, to answer your last post...

If Asc(Left(strWord,1)) = 97 Then ' is lowercase
Me![field] = 0
ElseIf Asc(Left(strWord,1)) = 65 Then ' is uppercase
Me![field] = 1
End If

But once I get the sample file, give me a few minutes and I should have it for you.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[allanon]

Here is what you can try:

IIF((ASC(Mid([MyField],1,1)) = ASC(UCASE(Mid([MyField],1,1)))), 1, 0)

 

[Carenne]

Thanks so much for the code, Robert and allanon -- that was so helpful!

I've had a bit of luck since getting the proper syntax to get it to work, but only numerous failed attempts to get it to work in a single step. When I tried to do it all in one query, it was tricky to figure out how to order the columns and the sort requests. What I usually ended up with, no matter what I tried to do, was *two* occurrences of each word - one uppercase and one lowercase - in the final output, or it would just ignore my efforts altogether and give me one occurrence, but use the uppercase selection in some cases where there was a mixed list.

What I finally ended up doing was a two-query process, which also ends up with three tables instead of the two (initial import and final list) that I had originally envisioned:

1. Run a &quot;make table&quot; query to subsort the original list according to case. Result is a second (tblSubsort) table which lists the words in ascending sort order - lowercase version first.

2. Run a second &quot;make table&quot; query against the newly made table, grouped by count, which (presumably) selects only the first occurrence of repeat words (which now happens to be lowercase when applicable), and output to a new (final word list) table. I haven't created the step yet, but ultimately I suppose I'll have to delete the second table to reclaim the wasted space.

Robert, I'd still like to email you the list so you can look at it if you get a chance and see if you (or anyone else) can spot a way that I could do it in fewer steps, if possible. But don't waste too much time on it - I can live with this method if I have to.

THANK YOU ALL AGAIN for the wonderful help - something you can ALWAYS count on in this community!

Carenne

 

[allanon]

email me a sample file as well @

jdyck@wcb.mb.ca

 

[Carenne]

Back with a final FYI update that after Robert looked at the sample database I sent, he confirmed that the routine I had set up was probably the best solution for the job at hand. I could not have accomplished that if I hadn't talked it out here first - I was &quot;stuck in the mud&quot; before submitting the initial post this morning! Thanks again to everyone for your help. I don't post here very often, but when I do, I always know I can count on getting the results I'm hoping for. Keep up the great work, and have a great evening!

Gratefully yours,
Carenne