queries based on queries

  • Thread starter Thread starter Cadburys
  • Start date Start date
C

Cadburys

Hi

I have a form that users select their criteria eg. Staff Name, Client Name
and date ranges. The user then clicks on a button that displays a form
listing the matching records.

My problem is that the form that opens is based on 14 queries. I am sure
that there must be a more efficient way to do this.

Query1 gets all the data from the underlying table
Query2 examines the Staff name box on the criteria form and if there is a
name then the results screen shows only that staff members records if there
is no staff name then it shows all records.
Query3 is based on Query2 (so it once again examines the staff name box) and
it checks for the Client on the criteria form and if there is one then
filters by the staff name and the client.
etc up to Query14.

Is there another way to do this?

Thanks
Nicky
 
You can bring all the possible criteria in query1. If you use the query
designer, simply add each criteria under each relevant field.

Vanderghast, Access MVP
 
On Thu, 18 Sep 2008 06:13:01 -0700, Cadburys

ALL queries based on queries can be written as a single query. We use
multiple queries to avoid the complexities of writing a single one,
but we don't go overboard and write a new one for each simple step.

It's up to you to find that happy medium.

To your specific question, if I understand correctly you want to show
all records if StaffName control is blank, and matching records
otherwise:
.... where StaffNameField=Nz(Forms!YourForm!StaffNameControl,
StaffNameField)

Then, assuming there is an OR condition between this one and the next,
you write the next condition on the OR line below the original
condition, or in code:
.... where (StaffNameField=Nz(Forms!YourForm!StaffNameControl,
StaffNameField)) OR
(ClientNameField=Nz(Forms!YourForm!ClientNameControl, ClientNameField)

-Tom.
Microsoft Access MVP
 
Thanks.

I have a another questions - I have 15 conditions to tests for - these
include 5 different date fields which are searched for using a >= start date
and <= end date. How would I structure these?
 
You can use the operator BETWEEN, in the criteria line, under the field you
test:



BETWEEN [start date] AND [end date]


Vanderghast, Access MVP
 
I have tried that but the fields are actually date and time fields.

Do you think that by running the query using code will speed it up?
--
Cheers


Michel Walsh said:
You can use the operator BETWEEN, in the criteria line, under the field you
test:



BETWEEN [start date] AND [end date]


Vanderghast, Access MVP
Click to expand...
 
The queries won't run faster if they are run from code than through the User
Interface: it is the same database engine that works behind the scene.

The criteria should work for date and time field as well as for any other
data type. One thing to remember when working on date_time field, is that if
you specify a date only, as upper limit, then, any data having the same
date, but with some value of the time, won't pass the criteria. Example:

BETWEEN #01/01/2008# AND #02/02/2008#


will not include a record having #02/02/2008 12:56:34#

since that is almost 13 hours past the upper limit. The problem is that when
you enter the upper limit date, you probably mean to include the whole day.
In such a case, better to use:
= #01/01/2008# AND < #03/02/2008#
Click to expand...


as example.


Vanderghast, Access MVP


Cadburys said:
I have tried that but the fields are actually date and time fields.

Do you think that by running the query using code will speed it up?
--
Cheers


Michel Walsh said:
You can use the operator BETWEEN, in the criteria line, under the field
you
test:



BETWEEN [start date] AND [end date]


Vanderghast, Access MVP


Cadburys said:
Thanks.

I have a another questions - I have 15 conditions to tests for - these
include 5 different date fields which are searched for using a >= start
date
and <= end date. How would I structure these?
--
Cheers and thank you for your help!


:

On Thu, 18 Sep 2008 06:13:01 -0700, Cadburys

ALL queries based on queries can be written as a single query. We use
multiple queries to avoid the complexities of writing a single one,
but we don't go overboard and write a new one for each simple step.

It's up to you to find that happy medium.

To your specific question, if I understand correctly you want to show
all records if StaffName control is blank, and matching records
otherwise:
.... where StaffNameField=Nz(Forms!YourForm!StaffNameControl,
StaffNameField)

Then, assuming there is an OR condition between this one and the next,
you write the next condition on the OR line below the original
condition, or in code:
.... where (StaffNameField=Nz(Forms!YourForm!StaffNameControl,
StaffNameField)) OR
(ClientNameField=Nz(Forms!YourForm!ClientNameControl, ClientNameField)

-Tom.
Microsoft Access MVP


Hi

I have a form that users select their criteria eg. Staff Name,
Client
Name
and date ranges. The user then clicks on a button that displays a
form
listing the matching records.

My problem is that the form that opens is based on 14 queries. I am
sure
that there must be a more efficient way to do this.

Query1 gets all the data from the underlying table
Query2 examines the Staff name box on the criteria form and if there
is
a
name then the results screen shows only that staff members records if
there
is no staff name then it shows all records.
Query3 is based on Query2 (so it once again examines the staff name
box)
and
it checks for the Client on the criteria form and if there is one
then
filters by the staff name and the client.
etc up to Query14.

Is there another way to do this?

Thanks
Nicky
Click to expand...
Click to expand...
Click to expand...
 
Back
Top