Solved

Complex query made easy?!

Posted on 2007-12-04
5
766 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 142

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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now