?
Solved

Complex query made easy?!

Posted on 2007-12-04
5
Medium Priority
?
776 Views
Last Modified: 2013-11-27
Access 2002 or 2003.
I am developing a mailing list system for an organisation, written in VB.
The  client requires to be able to do complex queries to set criteria for a mailing system. This includes and/or/not, hierarchy of precedence of criteria, etc ... Is there  a VB framework available anywhere (preferably free!) to make this as easy as possible for a non-computer-savvy person to specify these criteria? They do not know SQL and don't understand the subtleties of query-by-example, as Access supplies. They want to specify their criteria simply and intutively.
Many thanks.
0
Comment
Question by:Peborgh
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20525089
>The  client requires to be able to do complex queries
>They do not know SQL
>They want to specify their criteria simply and intuitively.

criteria #1 is contradictory with #2 and #3

if the customer (person) wants to be able to do complex stuff, he has to understand the complex stuff.
no way around that.



0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 1500 total points
ID: 20536288
I've created solutions like the one you're suggesting but it is difficult to give you specific instructions without knowing what the details are.  Even so, it is possible to avail techniques to do what you're trying to do.

While it is not necessary for your user to be adept at writing complex queries it will help a great deal if you can.  Remember, if they don't create the queries, you will have to do it for them and these queries will probably have to be built on the fly using code.

Providing the user AND, OR and NOT....

AND is pretty easy.  Bear in mind that all use of the word easy presumes that you already have the skills needed for the task.  If you create temporary tables you can draft queries that compile lists of matching values and use them with the IN Clause (Hopefully the key values are numeric as that will make things simpler) or if you prefer you an join the table in question using an INNER JOIN.

OR is just another concatenation of criteria that would render the results you're after.  You can bring this together using the OR clause or if that proves to be too messy a UNION query can sometimes work well, (ultimately it depends entirely on what you're doing).  If you use a UNION QUERY never forget the significant difference between using UNION and UNION ALL.  The former will avail all unique rows of data and UNION ALL will avail all rows whether duplicated or not.

NOT is about as simple as AND but if you're using the IN clause then the clause would read something like Not In(1, 3, 5, 15, 32) for example or you could join the table to a temporary/hash table using an OUTER JOIN and stipulate that you want the outer join's joining field to be null.

At the end of the day the question is, how adept are you at drafting queries because what you're proposing to do is build queries on the fly via CODE.  Very doable but if you find you rely heavily on the QBE grid to create you're queries and are not yet prepared to draft them from scratch (freehand) you may find you're bighting off something more than you can chew.  An experienced developer adept at the task of creating queries without the need to rely on the QBE grid to write their queries who is also good at concatenating strings shouldn't have much trouble but they need to be skilled at both tasks.

My main concern is that anyone who would ask the question may find these general guidelines to be inadequate to pull off what your're trying to do.  Whether this is due to a lack of experience in drafting code or drafting queries is somewhat academic, my main concern is that one or both are sticking points less the question would not have been asked.  Even so, the above tricks will get you there and I sincerly hope it proves to be the little extra needed to get you where you want to go.
0
 

Author Comment

by:Peborgh
ID: 20547421
This looks really helpful, at first sight, RR. I am v busy at present but will get back to you when this issue comes back to the top of the stack!

peter
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20549477
Very good.  Just let me know if I can be of any further help.

Rick
0
 

Author Comment

by:Peborgh
ID: 20665746
This has now gone away but th help was good, Rick...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question