Use of comma in query

[William Tavolga]

We have recently started using Alpha 5 in our archive of music recordings and sheet music. It has been a successful change from our previous database, and certainly easier to use and develop than most others.

I do have a question however. Now that several of our volunteers are actually using the new forms and applications, generally speaking they seem to be satisfied. There is one almost universal complaint: "Why can't I use the comma in my search?"
Unfortunately, the comma is coded as a logical OR, and searching for:
Sinatra, Frank
returns not only all the Sinatras, but all the Franks. The comma is such a common punctuation mark to use, and reserving it for the OR function is very inconvenient. We can live with the Asterisk, Dollar sign, Two dots, etc. Is there anything that can be done about this? Maybe the designers of version 5 can change to some less frequently used character.

I informed all our people to use an asterisk instead of a comma or avoid it as much as possible.

Any suggestions?

--Bill

[email protected]

[Peter.Wayne]

RE: Use of comma in query

try querying for "Sinatra,Frank" (use quotes around the whole name)>
or else try querying for sinatra*
either one should get you what you want.

[William Tavolga]

RE: Use of comma in query

Peter,

I tried using quotes, but the find-by-form does not recognize quotes. The sinatra* will work, of course, but we use commas widely, not only in composer names, but titles, as well. My example was badly chosen, but we have:
Bach, Carl Philip Emmanuel
Bach, Johann Sebastian
Bach, Johann Christian

If you write 'Bach, Johann' in the FBF, it returns all the Johann's and none of the Bach's

My point is that although the results can be achieved with use of the asterisk and other things, it is inconvenient not to be able to use the comma in a search.

Frankly, I think it was a mistake to assign the OR function to such a common punctuation mark as a comma.

--Bill

[forskare]

RE: Use of comma in query

William,

How about considering this idea?

Split the name field into three fields; Last, First and Middle. This will allow you to use FBF even more extensively as you could use any or all three fields.

Click the Operations tab and then right click anywhere in the white space and select New. Choose Update and then the table which contains the name field; then leaved the selection for the Genie and click OK. Choose, Break a Field into its parts and follow the genie.

You must add the three feilds to the table befoe doing the operation.

If you want to have a form, browse or report which shows the full name, create a calculated field and your expression could be:

Trim(Lastname)+", "+Trim(Firstname)+" "+Trim(Middlename)

It would then show the name as Bach, Johann Sebastian. I put 2 spaces between the " marks.

kenn

[Tom Cone Jr]

RE: Use of comma in query

I like Ken's idea of splitting the name into its component parts. would make searching and querying much easier.

Alpha Five update operation genie will offer considerable help in splitting names like these.

-- tom

[Tom Cone Jr]

RE: Use of comma in query

Bill,

I tested Dr. Wayne's idea (enclosing the QBF search string in a pair of quotation marks) and it works fine. The comma is not treated as a delimiter separating alternative search terms. If you did not get a match then it's because there are other differences in the string you used to search with and the string as it exists in your table. Perhaps there are more blank spaces embedded in the name than in your search string.

Searching and querying text fields can be a pain in the neck because of the misspellings. If running queries against a single field is critical, you might want to consider putting the field values in a separate table, one entry per item, and then have your data entry people pick them off a lookup list, rather than type them in from scratch each time. Will improve your accuracy tremendously. I regard this as essential when dealing with key fields that must be queried frequently. My personal goal is to avoid having the user actually type new data any more than is absolutely necessary. For text fields containing names of places, universities, artists, or the like, I'd force them to pick the name of a list, and I'd keep a watchful eye on that list!

-- tom

[Mike Konoff]

RE: Use of comma in query

I agree with the above suggestion of splitting the names into parts. You can probably even write a simple update expression to break "Sinatra, Frank" into its parts.

Mike

[Tom Cone Jr]

RE: Use of comma in query

Mike,

the update operation genie will offer to do it for you...

-- tom

[William Tavolga]

RE: Use of comma in query

Thanks much for all the replies. The quotes idea does not work, but splitting the text makes sense and is really easy for the computer to do. However, I am faced with the real world of TRADITION. The total database began with handwritten 3x5 card entries, then put into old DOS-based files by people who knew little or nothing about computers. Our volunteers range all over the map in computer literacy.

I can think of many ways of getting around the problem of the comma, but I think my point has been missed. Why the comma ? Who decided to program the comma as a symbol for the logical OR ? Why not the tilde ? or even the # sign ? Curiously, the period and other punctuation marks are treated as mere characters. Maybe the designers of version 5 could take this into consideration.

Thanks again for all the suggestions, and perhaps, in self-defense, we may have to reorganize and redefine our fields. But in the meantime, my simple solution is to tell all our users to substitute the asterisk for the comma in all our searches.

With out old database, the semi-colon was used as a separator, and all new data entries were to avoid the use of the semicolon.

--Bill

[forskare]

RE: Use of comma in query

Hi Bill,

No, your point wasn't missed. I doub't any of us know why the coma was chosen for other duty. I have a database at work that is being converted to A5 and it has one field for the name, thus the use of the coma. However, I can tell you from experience that entering names into three fields is easier than entering names into one field. AND, there is less room for error, such as; did the coma get put in the right place?

There is a difference in what Tom did vs the way you're doing it. You're applying the "" in FBF whereas Tom used Query By Form (QBF).

Perhaps you might suggest to your users that everything in life changes, especially when it comes to a database. One could conclude, "Well, I've got this little database, it serves me well so we'll just use it forever, just the way it is". The problem is, life changes and that will eventually force changes in the 'little database'. I think you're experiencing that now, are you not? Additionally, there will come a point when the database software itself will no longer be supported by the its creator, even the upcoming release of v5. That surely will force change.

Many people resist change especially when it comes to having done something one way for a long time. I heard this 'lil quote at a training conference one time;

"If you do what'you've always done, you'll get what you've always got." While that's good for some specifics, it does not bode wll for keeping up with life's changes.

Example 1: Let's say you want to search for the name "Carpenter". How many Carpenter's are in the database? With a single name field, you'll have to use the * to fine all persons with the name Carpenter. With a single last name field, you don't have to type the *.

Example 2: Let's say you know a first name and can't remember a last name. Now you have a bit of a problem for searching using a one name field, do you not?

While a new highway is smooth and fun to drive on, it will become obsolete. I guess that's my point, the use of one field for the entire name is obsolete. Without change, the road we're on is a dead end. It has nothing to do with the coma. It has everything to do with queries and searching and some day you'll have a need to search differently than you're doing now. When that occurs, with three fields, you're ready for it. Ya won't have to redesign the database.

So much for may soapbox. Good Luck
kenn

[William Tavolga]

RE: Use of comma in query

Ken,

Thanks for your thoughtful comments. I cannot agree more, but in my case I have to approach the personnel problem slowly. First of all, our entire operation is staffed by volunteers, and you can't treat volunteers the way you do employees. Eventually, I expect to redo our databases, but I have to do it in steps.

One of my main steps now was to get the people to use the mouse. That alone has taken much time and patience, but we are getting there. To make the transition easier, I made the search forms keyboard accessible - at least partially.

The reason I use find-by-form, rather than query-by-form, is that the former is easier to use. For example, if we enter just "Sinatra", the query returns only strings that consist entirely of the one word. The FBF, however, returns all that BEGIN with the word. Adding an asterisk at the beginning, i.e. "*Sinatra", then lets the FBF return any string that CONTAINS the word. I agree that we should use two fields, but how about a co-authorship such as:
Rogers, Richard, and Hammerstein, Oscar, III

Also how can we deal with a song title such as:
Lo, How the Bird Sings
Now I would tell my people to enter:
Lo*How
and that will find the title. It will also take care of situations where the space after the comma was missing.

Thanks again - but I still wish the progammers had not used a comma for the OR function.

--Bill

[forskare]

RE: Use of comma in query

Bill,

Yes, you do have your work cutr out for you. I'll give your questions some thought and respond a bit later. My grandkids want to go for a boat ride and the time of reckoning is here.

I shall return

kenn

[forskare]

RE: Use of comma in query

Bill,

Have you given any thought to using Query by Genie to search for multiple names and song titles? It is quite versatile and is easy to use, especially for volunteer users who don't spend much time on the computer. I believe it will alow you to find not only a name of song title but both at the same time.

QBG can be accessed either from the menu bar or by creating a button on the form. If you create the button, I would suggest you use a custom button and then with action scripting, choose the show all records first and the QBG.

You should still add a button on the form to show all records. I really like QBG as you can create some quite complex queries rather easily. You could approach your volunteers by telling them this is a very easy way to find data, especially when they might want to find a song title and a composer at the same time.

kenn

[Pat Bremkamp]

RE: Use of comma in query

Bill,

Thi sounds like a job for the "help" kind of search where you enter letters into a search box and the list below the box changes. That is you type "s" and the list starts with "s". Then type "Si" and it jumps to "Si". With this, you don't have to type the entire name to find it.
The key to training is to make it easier to use than not use, then everyone will want to change.
I think there is an article on Peter Wayne's "learn alpha.com" about this.
More work for you, but maybe easier to implement.

[William Tavolga]

RE: Use of comma in query

Pat,

The 'help' list sounds good, and I have seen it elsewhere. I didn't know how to implement it in Alpha, but I'll look at Wayne's article.

Of course it does not solve the basic problem of the 'misused' comma.

As for QBF vs. FBF: for our purposes, there seems to be a slight advantage to the FBF. Mainly the simpler use of the asterisk. For our users, I blanked out the tool bars - too small and more there than they will need. - and placed all functions on large, colorful, buttons. Buttons that are not necessary are temporarily hidden.

--Bill