• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

Complex query made easy?!

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
Peborgh
Asked:
Peborgh
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Rick_RickardsCommented:
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
 
PeborghAuthor Commented:
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
 
Rick_RickardsCommented:
Very good.  Just let me know if I can be of any further help.

Rick
0
 
PeborghAuthor Commented:
This has now gone away but th help was good, Rick...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now