I like the BOTH for AND. I think that would be very clear. I'm not so sure about the ONLY for OR.

Jim Booths solution is the one we are currently using (not the details, but custom coding queries with a query specific interface). The problem is that we do have to now custom code every query. I think the idea of writing our own query builder rather than using a third party is a good one.

From these responses, I gather that there are no third party tools our there which avoid these problems. (Our users currently query Oracle, SQL Server, and VFP so we were limited to tools that query all of these.)

The engine for query building is actually pretty simple to code. If a particular interface can determine what the user wants, it is not hard to translate it into a SQL query. The hard part is figuring out what kind of general purpose (not hard coded to a data structure) inteface can determine what the user wants. I agree that it is unreasonable to expect the end user to know boolean logic. I'll look at ENGLISH.

I wonder if Jim's approach could be generalized.

Step 1 -- user enter criteria in a grid -- one criteria to a row .

Step 2 -- Have list box or option group similar to that in Internet searches engines. "Return only rows meeting ALL of these criteria at the same time"?
"Return rows meeting ANY ONE of these criteria"?


Engine would also recoginize when same field was used twice and not allow the choice of "ALL" in that case?

Sums, Avg , Counts are common enough that we would need some user friendly approach to this.

Combinations of AND and OR would be in advanced search screen -- making that user friendly would be a real challenge -- then again this is uncommon enough that we might simply make a little less friendly -- perhaps trying the ONLY, BOTH approach.

Question: We have no problem building something like this if there is really nothing out there, but hate reinventing the wheel. Is everybody sure there is no good third party tool that takes a super user friendly approach (not neccesarily the one I outlined) to generalized query building?