How to implement a form to retrieve variables to customize a query

[DamePique]

Hello everybody,

I would like my Database to be able to perform a Poblational Design. That is, I have a population stored in a table, which is characterized by a number of variables (Age, Gender, Height, Heart Rate...) (at all it makes more than 30 variables).

So I would like to allow the user to select a subpopulation according to the desired criteria.

Let's say, the user want to select a subpopulation with:

Gender = Female AND Heart Rate > 80

My idea, at least to beggin with, was to let the user select a maximal of 2 variables. So, I have implemented a form with 7 Drop-Down List Boxes (LB) as it follows:

LB1: A list with the 30 variable names [corresponding to the files in the Table MedicalData of the population] for choosing Variable1

LB2: [=, >, <]

LB3: Text Box Allowing the user to select a quantity/string

LB4: [AND, OR]

LB5-6: Same as LB1-3 but corresponding to Variable2.

So my question is, is there a way of passing the information retrieved by the form, to a customized query?

PD. My first idea was to program an individual query for each of the variables and then combine them in a form, but I would need to make a new query each time a new medical variable is added, and I was wondering if there is a more direct and pretty way if doing that.

Thank you very much in advance for your comments and suggestions

[nico5038]

I would start with proposing to teach the users the use of the right-click pop-up menu.
This will allow in a table (or form) to filter directly on the data and by adding filters Access will use an AND.

There's also a filter by form allowing an OR relation, but I think that's complexer as teaching a user to write a SELECT query in the Graphical query editor.

Nic;o)

[NeoPa]

Ambitious, but possible.

If you consider that queries are, fundamentally, just SQL commands passed as a string, then you can use VBA in your form to respond to the selections in the ComboBoxes (for such is how the drop-downs controls are named) on your form and create the relevant string which corresponds to all the selections.

You could, alternatively, create a form with such controls in the header section which simply applies a filter to itself assuming that is adequate to your requirements.

[Mihail]

Hello !
Just for subscription to this thread and some comments:

From your example: Gender = Female
What means if you replace the "=" sign with the other one from your list (< , >) ?! . Think about using a combo box for that type of data (in this case to make a choice between ALL, MALE,FEMALE)

What about if you wish to know who is in trouble with heart ?
Something like: Heart_Rate < 60 OR Heart_Rate > 80
You need to use the same variable two times.

And so on, a lot of things to think about.

More than, the boolean logic is not for any one as clear as it is for a coder (even experimented coders make mistakes when us it). And I think that you design this database for a medical team (their math is almost null).

[NeoPa]

Some good points there in Mihail's post, but if you're considering presenting such a page to users I would assume you already know the users are capable of dealing with it intelligently and logically. Unfortunately, there's no facility within Access to check SQL as valid before executing it as there is in many other flavours of SQL. That would be an appropriate check if it were available. I do suggest you handle the SQL failing with a friendly error sequence though, as it's clearly possible for the SQL created to be invalid.

Whether you extend the comparison options to include the non-arithmetic operators such as Like; In(); Between ... And; etc is up to you, but doing so will add power to the page as well as complicating your design a fair bit.

NB. Remember to parenthesise each individual comparison on creation. Even if problems with such situations is rare, it doesn't hurt and also makes debugging later much easier. On that point, it may be worth building in a display of your SQL on the form so that you can check the results are as expected.

[DamePique]

First of all, thanks to the 3 of you for your quick and useful answers, and sorry for the lack of feedback for a time, but I had to stop the project for several days. However I'm back.

Well, nico, the users are precisely the ones demanding a 'graphic' interface in order to facilitate the Population Design. So I don't think that solution will fullfill their expectations. Usually they do it manually, but they're handling hundreds and sometimes thousands of people!

On the other hand using Boolean Logic is OK for them, since they're not exactly Medical Doctors (MD) but Biomedical Engineers, so most of them have programmed at least once in their lives! However it is true that the final user will probably be a MD so a used-friendly interface and working-logic will be needed.

In reference to what you are mentioning:

1. Nominal Variables: I was thinking, when a nominal variable was selected from the CombBox in the Form, to switch the second and third Input-boxes corresponding to that variable, into another Combobox with the allowed options for it (e.i. [=] and [Female/Male])
   
2. Boolean Items and 'Between': For a first approach I was just thinking on allowing only the boolean operators mentioned in my first post. However, I have to admit it's a very good idea and I will consider it after getting something work in a simplified protoype of the initial idea
   
3. SQL Sequences: so the key-question seems to be to pass the answers retrieved by the form as a SQL command string to the Query. Could somebody give me a piece of code with just 2 variables selected (let's say Blood Pressure = 80 AND Diagnose = 1) so it can let me figure out how to extrapolate it to the use of multiple variables and multiple booleans?to make me
   
4. SQL Failing Checking: thanks for the suggestion. In effect, I was thinking in doing something like that, but first I need to make work cooperatively both the Form and the Query.

Thanks again for all your help!

[NeoPa]

We don't just provide boilerplate answers for you, but we are happy to work along with you and prompt you in the right direction.

You need to start off with controls for the following items :

Existing filter (showing what has already been determined so far).  A label is fine for this.
A list of the available/filterable fields in the record.  A ComboBox is fine for this.
Comparison operator.  A ComboBox is fine for this.
Main comparison value.  A TextBox is fine for this.
Alternate comparison value which is only available for the 'Between' construct if supported.  A TextBox is fine for this.

You will also need to share the names of these items when you have them set up if we're to help further.

Now you have to determine which comparisons you wish to support. The code will vary greatly depending on this decision. If In() is to be supported then parsing the main comparison value will need to allow for a comma-separated list. In that case you may choose to dispense with the alternate comparison value control and allow the operator to specify two separate values (comma-separated again) in the main one.

There may well be fields which contain values which are limited to a specific list (Like an Enum). To support that you'd need an extra control that could be set up that way and you'd need to have the information as to which values are allowed somewhere too. Your code should ensure that only one of this or the main comparison value control is visible at any one time.

[Mihail]

Styles and styles.

My style is to start by designed a form, with all controls I think I need. Then, step by step, I turn, one by one, to life, each of them.

Good luck with your project !