creteria macros

[Guest]

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."

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Tom Ellison]

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

 

[Guest]

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?

 

[Guest]

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?

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 <[email protected]>
Please feel free to quote anything I say here.

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."

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

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?

:

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 <[email protected]>
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."

 

[Guest]

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

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 <[email protected]>
Please feel free to quote anything I say here.

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?

:

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 <[email protected]>
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."

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

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
:

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 <[email protected]>
Please feel free to quote anything I say here.

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?

:



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 <[email protected]>
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."

 

[Guest]

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 are:
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.

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 <[email protected]>
Please feel free to quote anything I say here.

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
:

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 <[email protected]>
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?

:



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 <[email protected]>
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."

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

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 are:
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.

:

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 <[email protected]>
Please feel free to quote anything I say here.

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

:

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 <[email protected]>
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."

 

[Guest]

Each of the CLASS1 to CLASS8 tables is a linked table. The source is a huge
database, that I don't want to recreate with a join query. Are you asking me
to append CLASSES1-8 to one Table, ALLCLASSES? By doing an append query for
each class I create a small new table with just maybe 30 new records in the
table being appended to.
No way to do a macro for my task?

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 <[email protected]>
Please feel free to quote anything I say here.

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 are:
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.

:

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 <[email protected]>
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

:

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 <[email protected]>
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."

 

[Guest]

Tell me more about how this works.

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

 

[Vincent Johns]

I'm not sure what you mean by a "join query". The "JOIN" operator in
SQL is used to link datasets and filter the results, but it doesn't
exactly create anything.

Now that I know that your Tables are links to some other database (over
which I assume you have no control), no, I do not recommend creating a
Table, nor appending records to an existing one. (You could do that,
and maybe it would make sense from a performance viewpoint, but you'd
have to keep destroying and re-creating it when you wanted to update the
results.)

So yes, I am suggesting using the Union Query that I described to
combine the values from the various linked Tables into one dataset that
you can then operate on with any of various easy-to-use and
easy-to-define Queries. I think your easiest course of action is to use
this Union Query INSTEAD OF any Table that you might build. The Query
will be up to date any time you run it, whereas a Table can become out
of date without warning, and you might not think to update its contents.

Once you've defined your Union Query, you could write a parametric Query
based on it that would ask you to enter a number from 1 to 8 specifying
a "CLASS" Table, and this Query could then spit out just the selected
records from the Table you'd identified. Or you could define a Query
that would display records for a given date range from more than one of
the "CLASS" Tables.

There are ways you might use a Macro, such as having it display a
message telling you in some detail what you need to specify to get the
information you desire. But a Macro and a Query have somewhat different
purposes, and what you asked for seems to me to point to a Query. If
you wish, you could define a Macro which would run your Query, but if
that's all it does, why bother? You could just run the Query instead.
For that matter, you could write a Module to do all this using VBA code,
but I think that it would take longer to write than a Query would, and
it would be more of a pain to maintain. Usually, if something can be
done using only Queries, I think that's not a bad way to proceed, unless
you have some reason to want to use a different process (such as knowing
ahead of time that you will later want to do something that requires
using VBA).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Each of the CLASS1 to CLASS8 tables is a linked table. The source is a huge
database, that I don't want to recreate with a join query. Are you asking me
to append CLASSES1-8 to one Table, ALLCLASSES? By doing an append query for
each class I create a small new table with just maybe 30 new records in the
table being appended to.
No way to do a macro for my task?
:

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 <[email protected]>
Please feel free to quote anything I say here.

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 are:
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.

:



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 <[email protected]>
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

:


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 <[email protected]>
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."

 

[Vincent Johns]

You could have a Query read the value of a control and use that value to
filter your results. But your original post asked about using a
separate Query for each of your linked Tables. Using a control on a
Form (or, as I suggested in another post, defining a [Parameters] Table
to specify your values) to filter all of the Queries would save you some
work, but you'd still have to manage all those Queries.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.