creteria macros

I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

Depending on what your criterion is, you might be able to do it by
storing a value or values in a Table. For example, if your criterion is
for the field to be between two limits, you can define a Table (with
just one record) in which [High] and [Low] fields contain the upper &
lower limits, and in the criterion you can compare with these values.

You might get a better answer if you post the SQL of at least some of
your 6 Queries.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:

I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

Dear Sierra:

One way of doing this is to put a control on a form for each criterion. If
you use Jet, the queries can reference those controls. You could have
controls on that same form to initiate each query, or any combination of
them.

Tom Ellison


"sierralightfoot" wrote in
message ...
I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words
I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

I have no idea what you're talking about. I'm not that sophistocated. Can't I
use some kind of query macro to do this?
"Tom Ellison" wrote:

Dear Sierra:

One way of doing this is to put a control on a form for each criterion. If
you use Jet, the queries can reference those controls. You could have
controls on that same form to initiate each query, or any combination of
them.

Tom Ellison


"sierralightfoot" wrote in
message ...
I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words
I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

I have no idea what you're talking about. I'm not that sophistocated. Can't I
use some kind of query macro to do this?

"Vincent Johns" wrote:

Depending on what your criterion is, you might be able to do it by
storing a value or values in a Table. For example, if your criterion is
for the field to be between two limits, you can define a Table (with
just one record) in which [High] and [Low] fields contain the upper &
lower limits, and in the criterion you can compare with these values.

You might get a better answer if you post the SQL of at least some of
your 6 Queries.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:

I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

As I kind of suggested earlier, I don't have a very good idea what
you're trying to do. I still think you should post an example of...
- your data (a couple of records from each Table)
- the SQL of the Queries you're using right now
- an example of what you'd like to see your Queries produce

Then I might be able to suggest something useful.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:
I have no idea what you're talking about. I'm not that sophistocated. Can't I
use some kind of query macro to do this?

"Vincent Johns" wrote:


Depending on what your criterion is, you might be able to do it by
storing a value or values in a Table. For example, if your criterion is
for the field to be between two limits, you can define a Table (with
just one record) in which [High] and [Low] fields contain the upper &
lower limits, and in the criterion you can compare with these values.

You might get a better answer if you post the SQL of at least some of
your 6 Queries.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:


I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

I have six queries with say 20 fields. Each query has a field:
Status Field Date Field
Active 01/01/06
Canceled xx/xx/xx
ETC

These are the two fields that, on a daily basis, I may want the change the
creteria in various ways: give me the records that are canceled on 2/1/06. I
need this creteria to flow through all six queries. I don't want to manually
enter it six time.

Thanks
"Vincent Johns" wrote:

As I kind of suggested earlier, I don't have a very good idea what
you're trying to do. I still think you should post an example of...
- your data (a couple of records from each Table)
- the SQL of the Queries you're using right now
- an example of what you'd like to see your Queries produce

Then I might be able to suggest something useful.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:
I have no idea what you're talking about. I'm not that sophistocated. Can't I
use some kind of query macro to do this?

"Vincent Johns" wrote:


Depending on what your criterion is, you might be able to do it by
storing a value or values in a Table. For example, if your criterion is
for the field to be between two limits, you can define a Table (with
just one record) in which [High] and [Low] fields contain the upper &
lower limits, and in the criterion you can compare with these values.

You might get a better answer if you post the SQL of at least some of
your 6 Queries.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:


I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

Well, OK, that's part of what I asked for -- you showed me one Query
instead of six, and apparently no Tables. But maybe you can do
something like the following.

I defined a Table containing only one record (enforced by making
[Parameters_ID] a primary key with a Validation Rule property forcing
the value to be "Unique"). It could have several other fields, but I
have included only one, called [MyDate], which specifies the date you
want to look for.

[Parameters] Table Datasheet View:

Parameters_ID MyDate
------------- --------
Unique 1/1/2006

Also, I am guessing that your data Table might contain records looking
like this:

[MyTable] Table Datasheet View:

MyTable_ID Active Canceled
----------- ---------- --------
1772038542 12/15/2005 1/1/2006
-1378885129 1/1/2006
-1318204191 1/1/2006 2/3/2006
1044311136 2/8/2006

Then you can define Queries that link the data Table with the new
[Parameters] Table. I show two examples here. The first one shows
records having [Active] equal to the specified date.

[Q_Active] SQL:

SELECT MyTable.MyTable_ID,
Parameters.MyDate AS [Active On]
FROM MyTable INNER JOIN [Parameters]
ON MyTable.Active = Parameters.MyDate
ORDER BY MyTable.MyTable_ID;

[Q_Active] Query Datasheet View:

MyTable_ID Active On
----------- ---------
-1378885129 1/1/2006
-1318204191 1/1/2006

The second Query shows records from the same Table having [Canceled]
equal to the specified date.

[Q_Canceled] SQL:

SELECT MyTable.MyTable_ID,
Parameters.MyDate AS [Canceled On]
FROM MyTable INNER JOIN [Parameters]
ON MyTable.Canceled = Parameters.MyDate
ORDER BY MyTable.MyTable_ID;

[Q_Canceled] Query Datasheet View:

MyTable_ID Canceled On
---------- -----------
1772038542 1/1/2006

To change the chosen date, just edit the [Parameters] Table.

-- Vincent Johns
Please feel free to quote anything I say here.



sierralightfoot wrote:

I have six queries with say 20 fields. Each query has a field:
Status Field Date Field
Active 01/01/06
Canceled xx/xx/xx
ETC

These are the two fields that, on a daily basis, I may want the change the
creteria in various ways: give me the records that are canceled on 2/1/06. I
need this creteria to flow through all six queries. I don't want to manually
enter it six time.

Thanks
"Vincent Johns" wrote:


As I kind of suggested earlier, I don't have a very good idea what
you're trying to do. I still think you should post an example of...
- your data (a couple of records from each Table)
- the SQL of the Queries you're using right now
- an example of what you'd like to see your Queries produce

Then I might be able to suggest something useful.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:

I have no idea what you're talking about. I'm not that sophistocated. Can't I
use some kind of query macro to do this?

"Vincent Johns" wrote:



Depending on what your criterion is, you might be able to do it by
storing a value or values in a Table. For example, if your criterion is
for the field to be between two limits, you can define a Table (with
just one record) in which [High] and [Low] fields contain the upper &
lower limits, and in the criterion you can compare with these values.

You might get a better answer if you post the SQL of at least some of
your 6 Queries.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:



I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

Thanks, I'll try to work through this.
To clarify my earlier example: I gave you one example because all my tables
have identical fields
To help me further let's call eack table;
Class1
Class2
Class3
Class5
Class6
Class7
Class8
The two fields that I want to enter new creteria a
STATUSFIELD and DATEFIELD
There are queries already set up for each of these tables. Your example
should actually be:
STATUSFIELD the search creteria for this could be :active,
canceled,pending, etc..

obviously the DAREFIELDfield returns a date.

So let's enter the new creteria once for Date and Status field and have it
forwarded to each of the queries for CLASS1 through to CLASS8. Thankls sorry
I wasn't clear previously.

"Vincent Johns" wrote:

Well, OK, that's part of what I asked for -- you showed me one Query
instead of six, and apparently no Tables. But maybe you can do
something like the following.

I defined a Table containing only one record (enforced by making
[Parameters_ID] a primary key with a Validation Rule property forcing
the value to be "Unique"). It could have several other fields, but I
have included only one, called [MyDate], which specifies the date you
want to look for.

[Parameters] Table Datasheet View:

Parameters_ID MyDate
------------- --------
Unique 1/1/2006

Also, I am guessing that your data Table might contain records looking
like this:

[MyTable] Table Datasheet View:

MyTable_ID Active Canceled
----------- ---------- --------
1772038542 12/15/2005 1/1/2006
-1378885129 1/1/2006
-1318204191 1/1/2006 2/3/2006
1044311136 2/8/2006

Then you can define Queries that link the data Table with the new
[Parameters] Table. I show two examples here. The first one shows
records having [Active] equal to the specified date.

[Q_Active] SQL:

SELECT MyTable.MyTable_ID,
Parameters.MyDate AS [Active On]
FROM MyTable INNER JOIN [Parameters]
ON MyTable.Active = Parameters.MyDate
ORDER BY MyTable.MyTable_ID;

[Q_Active] Query Datasheet View:

MyTable_ID Active On
----------- ---------
-1378885129 1/1/2006
-1318204191 1/1/2006

The second Query shows records from the same Table having [Canceled]
equal to the specified date.

[Q_Canceled] SQL:

SELECT MyTable.MyTable_ID,
Parameters.MyDate AS [Canceled On]
FROM MyTable INNER JOIN [Parameters]
ON MyTable.Canceled = Parameters.MyDate
ORDER BY MyTable.MyTable_ID;

[Q_Canceled] Query Datasheet View:

MyTable_ID Canceled On
---------- -----------
1772038542 1/1/2006

To change the chosen date, just edit the [Parameters] Table.

-- Vincent Johns
Please feel free to quote anything I say here.



sierralightfoot wrote:

I have six queries with say 20 fields. Each query has a field:
Status Field Date Field
Active 01/01/06
Canceled xx/xx/xx
ETC

These are the two fields that, on a daily basis, I may want the change the
creteria in various ways: give me the records that are canceled on 2/1/06. I
need this creteria to flow through all six queries. I don't want to manually
enter it six time.

Thanks
"Vincent Johns" wrote:


As I kind of suggested earlier, I don't have a very good idea what
you're trying to do. I still think you should post an example of...
- your data (a couple of records from each Table)
- the SQL of the Queries you're using right now
- an example of what you'd like to see your Queries produce

Then I might be able to suggest something useful.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:

I have no idea what you're talking about. I'm not that sophistocated. Can't I
use some kind of query macro to do this?

"Vincent Johns" wrote:



Depending on what your criterion is, you might be able to do it by
storing a value or values in a Table. For example, if your criterion is
for the field to be between two limits, you can define a Table (with
just one record) in which [High] and [Low] fields contain the upper &
lower limits, and in the criterion you can compare with these values.

You might get a better answer if you post the SQL of at least some of
your 6 Queries.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:



I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."

OK, that clarifies things a bit; thanks.

What I'm going to suggest depends on whether you have control over the
contents & structure of the Tables [Class1] ... [Class8]. In either
case, I suggest first writing a Union Query that will combine them into
one dataset. (I would include an extra field identifying what Table each
record comes from -- 1 for [Class1], 2 for [Class2], etc.)

Having written the Union Query, if you have control over the Tables, I
suggest basing a Make-Table Query on the Union Query and creating a
Table containing all the records from the separate Tables. (After
backing up your database, you could then delete the old Tables and the
Union Query.)

You can then write the Query you asked about, basing it either on the
Union Query (if you do not have control over the Tables) or on the
combined Table (if you can replace the original Tables). You can then
filter or sort the results in many ways, for example by class number or
date or status.

For example, suppose your original Tables contain data like these (I'm
showing only 3 of the Tables, for brevity):

[Class1] Table Datasheet View:

Class1_ID STATUSFIELD DATEFIELD
---------- ----------- ---------
-386348353 pending 3/6/2006
501406313 active 2/1/2006
1583004278 canceled 2/15/2006

[Class2] Table Datasheet View:

Class2_ID STATUSFIELD DATEFIELD
---------- ----------- ---------
-1365033580 pending 2/3/2006

....

[Class8] Table Datasheet View:

Class8_ID STATUSFIELD DATEFIELD
---------- ----------- ---------
-586888443 confirmed 3/6/2006

Then your Union Query, combining them into a single dataset, might look
like this:

[QU_Classes] SQL:

SELECT 1 AS Class, Class1.STATUSFIELD, Class1.DATEFIELD
FROM Class1
UNION ALL
SELECT 2 AS Class, Class2.STATUSFIELD, Class2.DATEFIELD
FROM Class2

UNION ALL
SELECT 8 AS Class, Class8.STATUSFIELD, Class8.DATEFIELD
FROM Class8
ORDER BY Class1.STATUSFIELD, Class1.DATEFIELD;

I'm omitting the [Class1_ID] fields, since they might not be unique.
(The same value might exist in more than one of the original Tables.)
You can always add a primary key field later, if necessary.

[QU_Classes] Query Datasheet View:

Class STATUSFIELD DATEFIELD
----- ----------- ---------
1 active 2/1/2006
1 canceled 2/15/2006
8 confirmed 3/6/2006
2 pending 2/3/2006
1 pending 3/6/2006

Having combined the records into one recordset, you may optionally write
them to a new Table, [T_AllClasses], via a Make-Table Query:

[QM_AllClasses] SQL:

SELECT QU_Classes.Class, QU_Classes.STATUSFIELD,
QU_Classes.DATEFIELD
INTO T_AllClasses
FROM QU_Classes
ORDER BY QU_Classes.DATEFIELD, QU_Classes.Class;

Running this places the following records into [T_AllClasses]:

[T_AllClasses] Table Datasheet View:

Class STATUSFIELD DATEFIELD
----- ----------- ---------
1 active 2/1/2006
2 pending 2/3/2006
1 canceled 2/15/2006
1 pending 3/6/2006
8 confirmed 3/6/2006


Now you may select the records you wish to see, by setting filter and
sorting criteria in another Query. Although this one is based on the
Union Query that we defined earlier, [QU_Classes], it could just as
easily have been based on the [T_AllClasses] Table.

[QS_AllClasses] SQL:

SELECT QU_Classes.Class, QU_Classes.STATUSFIELD,
QU_Classes.DATEFIELD
FROM QU_Classes
ORDER BY QU_Classes.DATEFIELD, QU_Classes.Class;

.... and the results (based on new Table or Union Query) look like this:

[QS_AllClasses] Query Datasheet View:

Class STATUSFIELD DATEFIELD
----- ----------- ---------
1 active 2/1/2006
2 pending 2/3/2006
1 canceled 2/15/2006
1 pending 3/6/2006
8 confirmed 3/6/2006

The resulting dataset can be sorted and filtered:

[Q_Selected] SQL:

SELECT T_AllClasses.*
FROM T_AllClasses
WHERE (((T_AllClasses.DATEFIELD)=#3/6/2006#))
ORDER BY T_AllClasses.Class;

[Q_Selected] Query Datasheet View:

Class STATUSFIELD DATEFIELD
----- ----------- ---------
1 pending 3/6/2006
8 confirmed 3/6/2006


-- Vincent Johns
Please feel free to quote anything I say here.


sierralightfoot wrote:
Thanks, I'll try to work through this.
To clarify my earlier example: I gave you one example because all my tables
have identical fields
To help me further let's call eack table;
Class1
Class2
Class3
Class5
Class6
Class7
Class8
The two fields that I want to enter new creteria a
STATUSFIELD and DATEFIELD
There are queries already set up for each of these tables. Your example
should actually be:
STATUSFIELD the search creteria for this could be :active,
canceled,pending, etc..

obviously the DAREFIELDfield returns a date.

So let's enter the new creteria once for Date and Status field and have it
forwarded to each of the queries for CLASS1 through to CLASS8. Thankls sorry
I wasn't clear previously.

"Vincent Johns" wrote:


Well, OK, that's part of what I asked for -- you showed me one Query
instead of six, and apparently no Tables. But maybe you can do
something like the following.

I defined a Table containing only one record (enforced by making
[Parameters_ID] a primary key with a Validation Rule property forcing
the value to be "Unique"). It could have several other fields, but I
have included only one, called [MyDate], which specifies the date you
want to look for.

[Parameters] Table Datasheet View:

Parameters_ID MyDate
------------- --------
Unique 1/1/2006

Also, I am guessing that your data Table might contain records looking
like this:

[MyTable] Table Datasheet View:

MyTable_ID Active Canceled
----------- ---------- --------
1772038542 12/15/2005 1/1/2006
-1378885129 1/1/2006
-1318204191 1/1/2006 2/3/2006
1044311136 2/8/2006

Then you can define Queries that link the data Table with the new
[Parameters] Table. I show two examples here. The first one shows
records having [Active] equal to the specified date.

[Q_Active] SQL:

SELECT MyTable.MyTable_ID,
Parameters.MyDate AS [Active On]
FROM MyTable INNER JOIN [Parameters]
ON MyTable.Active = Parameters.MyDate
ORDER BY MyTable.MyTable_ID;

[Q_Active] Query Datasheet View:

MyTable_ID Active On
----------- ---------
-1378885129 1/1/2006
-1318204191 1/1/2006

The second Query shows records from the same Table having [Canceled]
equal to the specified date.

[Q_Canceled] SQL:

SELECT MyTable.MyTable_ID,
Parameters.MyDate AS [Canceled On]
FROM MyTable INNER JOIN [Parameters]
ON MyTable.Canceled = Parameters.MyDate
ORDER BY MyTable.MyTable_ID;

[Q_Canceled] Query Datasheet View:

MyTable_ID Canceled On
---------- -----------
1772038542 1/1/2006

To change the chosen date, just edit the [Parameters] Table.

-- Vincent Johns
Please feel free to quote anything I say here.

sierralightfoot wrote:

I have six queries with say 20 fields. Each query has a field:
Status Field Date Field
Active 01/01/06
Canceled xx/xx/xx
ETC

These are the two fields that, on a daily basis, I may want the change the
creteria in various ways: give me the records that are canceled on 2/1/06. I
need this creteria to flow through all six queries. I don't want to manually
enter it six time.

Thanks

"Vincent Johns" wrote:

As I kind of suggested earlier, I don't have a very good idea what
you're trying to do. I still think you should post an example of...
- your data (a couple of records from each Table)
- the SQL of the Queries you're using right now
- an example of what you'd like to see your Queries produce

Then I might be able to suggest something useful.

-- Vincent Johns
Please feel free to quote anything I say here.

[...]
sierralightfoot wrote:

I have many queries.
I want to enter certain creteria once (for identical fiield names in each
query) and have it become the creteria for all the queries. In other words I
don't want to enter the creteria six times, once for each query. All these
queries append to "Table X."