Setting criteria for a query used several times

[DougW via AccessMonster.com]

Hi - I have a query that I would like to use multiple times within a single
report. In each case I want to use a different set of criteria for the query.
The sets of criteria inputs will be pre-defined (I don't want to require
input from the user).

I realize that I could just copy the query several times and change the
criteria as needed.
I would prefer to use a single query and be able to drive it from a table (or
some other source for the criteria inputs). Can this be done?

thanks,
Doug

 

[Marshall Barton]

Hi - I have a query that I would like to use multiple times within a single
report.
How?

In each case I want to use a different set of criteria for the query.
The sets of criteria inputs will be pre-defined (I don't want to require
input from the user).

What are you trying to accomplish?

I realize that I could just copy the query several times and change the
criteria as needed.
I would prefer to use a single query and be able to drive it from a table (or
some other source for the criteria inputs). Can this be done?

If you are opening the report multiple times, then open it
in Dialog mode with your criteria in the WhereCondition
argument.

 

[DougW via AccessMonster.com]

I'm trying to display the results of a crosstab query.
It shows the forecast deliveries of products to various facilities.
On one report I want to have a section that lists the delivery forecast for
Facility A.
I also want similar sections showing the delivery forecast for Facility B,
Facility C, etc.
Besides the facilities, I also need to set criteria to limit the products
shown in each query.

So all I am trying to vary is the set of criteria that goes in the criteria
row in the query.
I'm trying to re-use the same query with different sets of criteria. I want
to include all my
queries for the various facilities on one report.

thanks, Doug

 

[George Nicholson]

Not sure exactly what you are trying to do that grouping doesn't address,
but, if you really do need to do what you are asking, maybe the following
will give you some ideas (my second guessing hat is at the cleaners today).


-------Within a General Module----------
Public lngCriteria1 as Long

Public Function GetCriteria1() as Long
GetCriteria1 = lngCriteria
End Function


----- Within qryWithCriteria -----
use GetCriteria1() (including parenthises) as Criteria and/or as a
FieldValue:
- ... WHERE Facility = GetCriteria1()


---- Within Report (???) -------
I have no idea how/when you want to change i within a report, or what you
expect to do with the results.

Public Sub RunQueries()
Dim i as Long
Dim db as Dao.database
Dim rs as Dao.recordset

set db = CurrentDB
For i = 1 to 10
lngCriteria1 = i
set rs = db.Openrecordset("qryWithCriteria")
' Do something with the results of this recordset
Next i
Set rs = Nothing
Set db = Nothing
End Sub

 

[DougW via AccessMonster.com]

George, thanks for the reply. I'm rather ignorant of VBA so I will do my
best to understand the code.

Really I'm just trying to select from multiple sets of pre-defined criteria
to plug into a query.
It seems like it would be a fairly common thing, but doesn't seem to be the
case.

thanks again,
Doug

Not sure exactly what you are trying to do that grouping doesn't address,
but, if you really do need to do what you are asking, maybe the following
will give you some ideas (my second guessing hat is at the cleaners today).

-------Within a General Module----------
Public lngCriteria1 as Long

Public Function GetCriteria1() as Long
GetCriteria1 = lngCriteria
End Function

----- Within qryWithCriteria -----
use GetCriteria1() (including parenthises) as Criteria and/or as a
FieldValue:
- ... WHERE Facility = GetCriteria1()

---- Within Report (???) -------
I have no idea how/when you want to change i within a report, or what you
expect to do with the results.

Public Sub RunQueries()
Dim i as Long
Dim db as Dao.database
Dim rs as Dao.recordset

set db = CurrentDB
For i = 1 to 10
lngCriteria1 = i
set rs = db.Openrecordset("qryWithCriteria")
' Do something with the results of this recordset
Next i
Set rs = Nothing
Set db = Nothing
End Sub

I'm trying to display the results of a crosstab query.
It shows the forecast deliveries of products to various facilities.

[quoted text clipped - 37 lines]

 

[Marshall Barton]

Sorry, but multiple queries is more about HOW you are trying
to do something. If you can explain WHAT the objective is,
maybe someone can recommend an alternative that would avoid
the issue.

Did you try my suggestion about using dialog mode and the
WhereCondition argument? If you don't understand that, say
so and provide more details about the "predefined criteria"
and how you are opening the reports and I will try to
clarify what it means.

 

[DougW via AccessMonster.com]

Hi Marshall,
I have a single query (a crosstab query) that I would like to view using
several different sets of criteria.
The sets of criteria include such things as PartType and Location. They are
numbers (primary keys) from some small tables. I am attempting to product
the query to show me results for Location A and Parts #1 thru #6. Then
Location A and Parts #7-8. Then Location B and Parts 1-6. Then Location B
and Parts 7-8. (For example). My sets of criteria will normally not change
so I would prefer the criteria to be stored somewhere rather than requiring
input from the user.

I realize I could copy the query several times and fix the criteria in each
individual copy of the query, but it seems more "orderly" to only have one
query to maintain. My thought was that I might tweak the general design of
the crosstab query over time, and with multiple copies I would have to go
into each one to update it.

I am unfamiliar with dialog mode for the report. Maybe that will get me
there.
Is that related to the "pop-up" option on the report properties? If you can
give me a little guidance on that I will give that a shot.

At the moment I am just opening the report from the Access reports list.
Eventually I want to open the report from a command button on my initial
"switchboard" form. It could be multiple reports if needed, but I would
prefer to get all the data on one printed report if possible.

I also made note of George's comment about grouping. I can see how that
could get me the report separated by Location, for example, but was not sure
if that had capability to limit my other criteria, such as a particular
selection of Parts.

I do appreciate your help and apologize for my ignorance. Sometimes the
biggest challenge for a novice like myself is how to correctly phrase the
question.

thanks very much,
Doug

Sorry, but multiple queries is more about HOW you are trying
to do something. If you can explain WHAT the objective is,
maybe someone can recommend an alternative that would avoid
the issue.

Did you try my suggestion about using dialog mode and the
WhereCondition argument? If you don't understand that, say
so and provide more details about the "predefined criteria"
and how you are opening the reports and I will try to
clarify what it means.

I'm trying to display the results of a crosstab query.
It shows the forecast deliveries of products to various facilities.

[quoted text clipped - 29 lines]

 

[DougW via AccessMonster.com]

ps if it would help I can post the code for the query, but I'm not sure how
to do that... thanks

Hi Marshall,
I have a single query (a crosstab query) that I would like to view using
several different sets of criteria.
The sets of criteria include such things as PartType and Location. They are
numbers (primary keys) from some small tables. I am attempting to product
the query to show me results for Location A and Parts #1 thru #6. Then
Location A and Parts #7-8. Then Location B and Parts 1-6. Then Location B
and Parts 7-8. (For example). My sets of criteria will normally not change
so I would prefer the criteria to be stored somewhere rather than requiring
input from the user.

I realize I could copy the query several times and fix the criteria in each
individual copy of the query, but it seems more "orderly" to only have one
query to maintain. My thought was that I might tweak the general design of
the crosstab query over time, and with multiple copies I would have to go
into each one to update it.

I am unfamiliar with dialog mode for the report. Maybe that will get me
there.
Is that related to the "pop-up" option on the report properties? If you can
give me a little guidance on that I will give that a shot.

At the moment I am just opening the report from the Access reports list.
Eventually I want to open the report from a command button on my initial
"switchboard" form. It could be multiple reports if needed, but I would
prefer to get all the data on one printed report if possible.

I also made note of George's comment about grouping. I can see how that
could get me the report separated by Location, for example, but was not sure
if that had capability to limit my other criteria, such as a particular
selection of Parts.

I do appreciate your help and apologize for my ignorance. Sometimes the
biggest challenge for a novice like myself is how to correctly phrase the
question.

thanks very much,
Doug

Sorry, but multiple queries is more about HOW you are trying
to do something. If you can explain WHAT the objective is,

[quoted text clipped - 11 lines]

 

[Marshall Barton]

ps if it would help I can post the code for the query, but I'm not sure how
to do that... thanks

Not sure it will help, but it shouldn't hurt - too much ;-)

Open your query in design view then use the View menu to
switch to SQL view. Then use Copy/Paste to put it in a
message.

 

[DougW via AccessMonster.com]

Marshall, thanks.
Here's the code for the crosstab query:

TRANSFORM Sum(CorePartArray.QtyPerComp) AS SumOfQtyPerComp
SELECT [Ordered Items].[Ship date (est)], [Ordered Items].[PO Number]
FROM [Purchase orders] INNER JOIN (CoreParts INNER JOIN ((Components INNER
JOIN CorePartArray ON Components.Comp_Index = CorePartArray.CompID) INNER
JOIN [Ordered Items] ON Components.Comp_Index = [Ordered Items].Comp_index)
ON CoreParts.CorePartIndex = CorePartArray.CorePartID) ON [Purchase orders].
[PO number] = [Ordered Items].[PO Number]
WHERE (((CorePartArray.CorePartID)<7) AND (([Purchase orders].DeliverLocnID)
=3))
GROUP BY [Ordered Items].[Ship date (est)], [Ordered Items].[PO Number],
[Purchase orders].DeliverLocnID
ORDER BY [Ordered Items].[Ship date (est)]
PIVOT CorePartArray.CorePartID;

In this case there are criteria set for CorePartArray.CorePartID < 7,
and [Purchase Orders].DeliverLocnID = 3

CorePartID identifies the parts of interest that are shipped to a facility.
DeliverLocnID identifies the facility to which the parts are shipped.

Those are the 2 fields that I would want to change as criteria for the
different versions of the report.
CorePartID would normally be a set of numbers (1 thru 6, or 7 thru 8, or 9
thru 11) and DeliverLocnID should always be a single number.

The crosstab query seems to do a good job of showing the overall information.
I'm just having trouble coming up with a good strategy to show the selected
information of interest.

If there's some way to do this with grouping so it all breaks out of a single
report, that would be great.

Any suggestions and ideas are appreciated.

thanks,
Doug

 

[Marshall Barton]

Here's the code for the crosstab query:

TRANSFORM Sum(CorePartArray.QtyPerComp) AS SumOfQtyPerComp
SELECT [Ordered Items].[Ship date (est)], [Ordered Items].[PO Number]
FROM [Purchase orders] INNER JOIN (CoreParts INNER JOIN ((Components INNER
JOIN CorePartArray ON Components.Comp_Index = CorePartArray.CompID) INNER
JOIN [Ordered Items] ON Components.Comp_Index = [Ordered Items].Comp_index)
ON CoreParts.CorePartIndex = CorePartArray.CorePartID) ON [Purchase orders].
[PO number] = [Ordered Items].[PO Number]
WHERE (((CorePartArray.CorePartID)<7) AND (([Purchase orders].DeliverLocnID)
=3))
GROUP BY [Ordered Items].[Ship date (est)], [Ordered Items].[PO Number],
[Purchase orders].DeliverLocnID
ORDER BY [Ordered Items].[Ship date (est)]
PIVOT CorePartArray.CorePartID;

In this case there are criteria set for CorePartArray.CorePartID < 7,
and [Purchase Orders].DeliverLocnID = 3

CorePartID identifies the parts of interest that are shipped to a facility.
DeliverLocnID identifies the facility to which the parts are shipped.

Those are the 2 fields that I would want to change as criteria for the
different versions of the report.
CorePartID would normally be a set of numbers (1 thru 6, or 7 thru 8, or 9
thru 11) and DeliverLocnID should always be a single number.

The crosstab query seems to do a good job of showing the overall information.
I'm just having trouble coming up with a good strategy to show the selected
information of interest.

If there's some way to do this with grouping so it all breaks out of a single
report, that would be great.

I think you can get all that in a single report without
using any criteria just by grouping on the DeliverLocnID
field and using landscape (and legal size paper?) so all 8
fields fit.

If you really have to have the CorePartID fields partitioned
into separate reports, then you can still use grouping to
reduce the problem to two querys. At this point I think you
can probably get away with that.

OTOH, If you really want to use a single query, then use a
form to control the process. The form would have a button
to initiate the reports and two text boxes to hold the
CorePartID criteria. One text box for the start part ID
number and the other for the end nunber.

The query's WHERE clause would then look like:

WHERE CorePartArray.CorePartID>=Forms!theform.txtPartStart
AND CorePartArray.CorePartID<=Forms!theform.txtPartEnd

You will probably also need to add a PARAMETERS clause at
the beginning of the query:

PARAMETERS Forms!theform.txtPartStart INTEGER,
Forms!theform.txtPartEnd INTEGER;

With that taken care of, the code for the button's Click
event would be like:

Me.txtPartStart = 1
Me.txtPartEnd = 6
DoCmd.OpenReport "reportname", WindowMode:=acDialog
Me.txtPartStart = 7
Me.txtPartEnd = 99
DoCmd.OpenReport "reportname"

 

[DougW via AccessMonster.com]

Marshall,
I will follow your suggestion and try to accomplish what I need using
grouping.
I think I'm a lot closer now, thanks to your help.
It appears I might need to create some calculated fields to get the final
results I need.
Which is presenting its own set of challenges - but I will save that for
another thread.

Thanks very much for your advice!

Best regards,
Doug

Here's the code for the crosstab query:

[quoted text clipped - 29 lines]

If there's some way to do this with grouping so it all breaks out of a single
report, that would be great.

I think you can get all that in a single report without
using any criteria just by grouping on the DeliverLocnID
field and using landscape (and legal size paper?) so all 8
fields fit.

If you really have to have the CorePartID fields partitioned
into separate reports, then you can still use grouping to
reduce the problem to two querys. At this point I think you
can probably get away with that.

OTOH, If you really want to use a single query, then use a
form to control the process. The form would have a button
to initiate the reports and two text boxes to hold the
CorePartID criteria. One text box for the start part ID
number and the other for the end nunber.

The query's WHERE clause would then look like:

WHERE CorePartArray.CorePartID>=Forms!theform.txtPartStart
AND CorePartArray.CorePartID<=Forms!theform.txtPartEnd

You will probably also need to add a PARAMETERS clause at
the beginning of the query:

PARAMETERS Forms!theform.txtPartStart INTEGER,
Forms!theform.txtPartEnd INTEGER;

With that taken care of, the code for the button's Click
event would be like:

Me.txtPartStart = 1
Me.txtPartEnd = 6
DoCmd.OpenReport "reportname", WindowMode:=acDialog
Me.txtPartStart = 7
Me.txtPartEnd = 99
DoCmd.OpenReport "reportname"