Printing Same Report several times different query

[marieannie]

Hello, Hope you can help me with this...
It is possible to print the same report several times
changing the query for that report?
if so, can you give me any suggestions how to do it?
_____________________________________________________
I want to create a list of employees using different criteria every time (there are several criterias and I would like to avoid creating fixed queries and reports) furthermore I want to print the resulting queries/reports at the same time.

For example:
Name Employee | Address | City | Occupation
Less than five years in the company (First query)
Employee 2 | unknown | xxxxx | xxxxxxxx
Employee 3 | xxxxxxx | xxxxx | xxxxxxxx
More than five years in the company (Second query)
Employee 1 | xxxxxxx|xxxxxxxxx|xxxxxxxxxxx
Employee 4 |xxxxxx|xxxxxxxx|xxxxxxxxxxxxxxx

As you can see is the same report but using different criteria. I just want to change the query and send the info to the report

Thanks in advance for any suggestion

 

[sxschech]

There are several ways to do this. One way that I used to do was use a "Standard" query name for the reports, meaning that the record source for the report would be the same generic name such as qryReport, then all I needed to do was copy the query I wanted to use as qryReport. Then the report would run using the criteria of the copied query.

Another way is to use code to change the record source of the report to point to the query that has the criteria you need.

If you don't want fixed queries, then you could create one query and use the query def to change the criteria...

The example shows copying a query and making criteria changes to the copy, but you don't need to, although it makes things easier as you always start from the same point and know where in the query to make the change.

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb

    Dim stReportTable As String
...
...
...
   ElseIf Me!CboReportGroupLevel.Value = 14 Then
        'Added code for Athletes and Non Athletes
        '10-Dec-2007
        Debug.Print "Athlete"
        DoCmd.CopyObject , "qryRates", acQuery, "qryRatesFA"
        DoCmd.CopyObject , "qryAttritSource", acQuery, "qryFA"
        'change where clause to Athletes Only
        Set qd = db.QueryDefs("qryRates")
        qd.SQL = Replace(qd.SQL, "Between 1996 And 2005))", "Between 1996 And 2005) AND (([sporttype])='ATH'))")
        Set qd = db.QueryDefs("qryAttritSource")
        qd.SQL = Replace(qd.SQL, "FROM FA;", "FROM FA WHERE SportType='ATH';")
...
...
...

The snippet above changes the query criteria based on what the user selected from a drop down list on the form, but you can either hard code the critera, if everything is known and you want it to run one after the other.


If the only thing that is changing in your criteria are the number of years, then what about adding a field to your query that displays the calculated value "< 5 Yrs" or "> 5 Yrs", then in the report you could do a group by on the < 5 Yrs, > 5 Yrs field. Along with the group by, you could add a page break on change in group so that the information for the two groups would not appear on the same page of the report.

 

[marieannie]

Thank you for your suggestions. I'm going to try the group by option.

Regards