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:
[TABLE="class: collapse"]
<tbody>[TR="class: trbgeven"]
[TH]Part[/TH]
[TH]Description[/TH]
[/TR]
[TR="class: trbgodd"]
[TD]field1, field2[/TD]
[TD]The name of the fields to be copied into the new table.[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]newtable[/TD]
[TD]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.[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]externaldatabase[/TD]
[TD]The path to an external database . For a description of the path, see the IN clause.[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]source[/TD]
[TD]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.[/TD]
[/TR]
</tbody>[/TABLE]

 

[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