Solved

Complex query made easy?!

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

17 Experts available now in Live!

Get 1:1 Help Now