Multiple Criteria

[Thebrads]

Hi
I wonder if someone could guide me with a database I am developing for a
horse-racing friend.
The rating of a horse for racing purposes depend on about 24 different
criteria, age, sex, jockey, surface, ground condition, draw, distance etc
etc.
I have categorised each of these (eg Distance has 7 different distances)
which are selected from a listbox , but only an integer number 1 to 7 is
entered into the Db (to save on size and speed).

My friend has no knowledge of access other than data entry, so I would like
to make him a user defined query form..

I have tried an unbound form and referenced the fields in a query, this
works fine until I leave one of the fields blank, where it shows no records.
I read this newsgroup and found that if I include (or Is Null) to the
control reference it will then return all the records of that field
correctly.
I did however notice that the query now has 4 OR criteria for only 2 fields,
where as I am going to need any mix of 24!!!!

Therefore I would welcome some guidance of how best to allow him to select
any of 24 different fields and to enter an integer 1 to 10 to give horses
with these certain characteristics, but also to pass all records where the
criteria field is left blank (as per normal query). It would also be useful
to be able to use between and greater than to select hoses within a range of
values.

The data is held in 1 table and another thought I had was to make another
form for the table without the list boxes and use filter form to query the
data. I would then need to find some way of performing some adding, dividing
and counting on the filtered data to give a possible profit scenario.

That's All!!!!!!!!!!

Thanks in Anticipation

Ray Bradshaw UK

 

[Jeff Boyce]

Ray

Another approach, instead of trying to build all possible combinations in a
query design grid, would be to use what's been entered and/or selected on
your form to dynamically build an SQL statement.

When a criterion is chose, your SQL statement includes, as part of the WHERE
clause, the appropriate phrase. When a criterion is "left blank", your SQL
statement doesn't include any WHERE clause for that criterion.

It will still be a bit of work, but not nearly as complex/confusing as
trying to make the query grid work.

Good luck

Jeff Boyce
<Access MVP>

 

[Thebrads]

Hi Jeff
Thanks,hat sounds fine because I think only perhaps 10 of the criteria will
be needed at any 1 time.
I don't have much experience with SQL other than I know the syntax can be
full of brackets etc.
Can you suggest anywhere I might find a tutorial .

Regards

Ray Bradshaw

 

[Jeff Boyce]

Maybe Google.com or the mvps.org websites can point you to something.
Consider building just one or two criteria in the query design window, then
clicking on the toolbar button, SQL view. Take a look at how Access
"writes" SQL.

Brackets (square brackets "[" and "]") go around fieldnames when there's a
space or the name could be ambiguously interpreted by Access (say, using
"Date" as a fieldname, when Access uses "Date" as a reserved word).

Parentheses ( "(" and ")" ) are used to show Access and other SQL consumers
which elements to group together, quite like you'd do if you had a long
arithmetic expression, with many operators, and wanted to specify the order
of operation.

Good luck

Jeff Boyce
<Access MVP>

 

[Thebrads]

Thank you very much for your time Jeff.

Regards

Ray Bradshaw