Single Parameter Multiple Queries

[saschmeling]

Hi,


I have a base Query that pulls in data to be used in multiple queries.

  Select
    ClientActivities.FacilityID
    ,Facility.FacilityName
    ,ClientActivities.ClientID
    ,ClientActivities.ActivityID
    ,ClientActivities.ActivityDate
    From
    ClientActivities
    Inner Join
    Facility
    on
    ClientActivities.FacilityID = FAcility.FacilityID
    Where
    ClientActivities.ActivityDate Between [StartDate] and [EndDate]

This feeds two other Queries.

  Select
    FacilityName
    ,Count(ClientID) 
    From
    BaseQuery

and

   Select
    ActivityID
    ,Count(ClientID)
    From
    BaseQuery

When I put them both on a single report as subreports- it asks me for the StartDate and EndDate twice. I would like for it to ask only once. Any suggestions on how this can be done? While keeping it simple as once I turn this over to the user I will be leaving and the extent of their Access training is one college class.


Thanks,

 

[MAP8458]

Have you tried altering your first query to select into a temporary table? The SELECT...INTO statement has these parts:

Part	Description
field1, field2	The name of the fields to be copied into the new table.
newtable	The name of the table to be created. It must conform to standard naming conventions . If newtable is the same as the name of an existing table, a trappable error occurs.
externaldatabase	The path to an external database . For a description of the path, see the IN clause.
source	The name of the existing table from which records are selected. This can be single or multiple tables or a query. Then change the two subqueries to select from the temporary table. When the second query is complete simply drop the temporary table. It's been a while since I've worked in Access but it works pretty well in SQL. Give it a try. The temporary table will be created when you run the first query using select into instead of select.

<tbody>
</tbody>

 

[SydneyGeek]

if you put the parameters on a form and reference them from there, you can hide the form when you run the report and the parameters will be available to both queries.

On a form called frmFilter you could have a command button and put something like this in the Click event:

Private Sub SomeButton_Click()
   Me.Visible = False
   DoCmd.OpenReport "MyReport", acPreview
EndSub

Denis