How can I pass a query parameter to a report with >1 use of the same query?

[clintonf]

Hi there,

I am designing an Access report that will employ the same query ("Attendance") multiple times.

Attendance accepts one parameter: Month ("...WHERE classmonth = [Month]"), and I have 12 sub-forms in my main form, each employing an instance of Attendance but with a different month supplied. Currently when I run the Attendance query a dialog box asks me to specify the month.

I actually have to run the query 12 distinct times; I cannot have the month supplied in the query itself, and thus cannot apply a simple filter to the query on month. I simplified the actual situation to get to the crux of my problem and prevent an overly long post.

I want to be able to use the same query 12 times in this report rather than having to save the query 12 distinct times.

Any ideas?

Thanks!

[Rabbit]

Just leave out the filter altogether and return month in the query. No need to run it 12 times. Run it once and return all the data.

[NeoPa]

It can be helpful to simplify a question. On the other hand, simplifying often leaves out the crux of the matter.

As Rabbit says, the most sensible approach appears to be to include the month in the data. Each subform then filters for its own required month.

If that's not possible for any reason then that's definitely important information to include in the question.

[clintonf]

Hah, fair enough!

So, here's a better explanation; keep in mind that month is no longer the problem.

I'm tracking attendance data across 2 months in the various programs at my school. A student may be involved in more than 1 program.

One of the stats I'm trying to generate is retention (a student was enrolled in the Spanish program in January and also in February). That one's a simple join on the student_id and program_name across the 2 months.

Growth is where I'm having a problem. This is the situation where the student was not enrolled in Spanish in January but is enrolled in February. I can't join on program_name because no record exists for January.

However if I specify program_name as a parameter then I can do an outer join (where progam_name IS NULL in January).

So that's why I have to manually specify the program name and why I can't filter by it.

Thanks.

[NeoPa]

If your looking for the growth of Spanish (for instance) and you've linked with a LEFT JOIN then you still have to filter by 'Spanish'. You just need also to filter by Null. The first filtered field will be from the primary record source and the second (Null) will be from the secondary one. You still filter the values returned in your data - whichever way you look at it.

[clintonf]

Right. I am filtering by program = "Spanish", and that filter is user-defined. I hadn't thought of it that way but you're absolutely correct.

So how can I have my report pass 'Spanish' to 1 instance of the query and 'French' to another instance, both in the same report?

Thanks.

[NeoPa]

That's hard to answer as only you have a good understanding of how these separate references to the query are addressed in your database. However they are referenced though, you use that as a qualifier for the reference to the particular field.

Say the field is called [Program] and the query is called [qryProgInfo], but referenced in the first instance as [qryPI01] and the other as [qryPI02]. Then your filtering would be something like :

([qryPI01].[Program]='Spanish') AND ([qryPI02].[Program]='French')

[clintonf]

Hmmm I'm not sure I'm getting my question across clearly.

First, the query I'm using. It relies on two other queries which don't have parameters so I think can safely be omitted as they'd make the code more complicated and harder to read. lastMonth() is a variable that I define using VBA. I do this instead of using a parameter because lastMonth changes every month and several other queries rely on this variable (ie. I can change several queries in one place).

SELECT DISTINCT lastMonth() AS [Grew In], oma.customerid, oma.[last name], oma.[first name], oma.program
FROM [Attendance: two months past] AS tma RIGHT JOIN [Attendance: one month past] AS oma ON (oma.program=tma.program) AND (oma.customerid=tma.customerid)
WHERE oma.program=[Prog] And tma.program Is Null
ORDER BY oma.customerid;
 

In my report I want this query to report twice ("Q1" "Q2"): once where [Prog] = 'Spanish' and once where [Prog] = 'French'. And for conceivably more programs as my school grows. I'm doing this by drag-and-dropping the query twice into my report. So I'm trying to make a report with two sub-reports.

How to I get the report to automatically supply [Prog] = "Spanish" to the Q1 report and [Prog] = "French" to the Q2 report?

Thanks!

[NeoPa]

I cannot add any more than I have already as there is no new and relevant information in your post.

[clintonf]

Hey NeoPa,

I'm afraid I don't understand where I would apply your suggestion:

([qryPI01].[Program]='Spanish') AND ([qryPI02].[Program]='French')

Does this go somewhere in the Report or in the query?

I appreciate the help you've supplied so far but I'm having trouble figuring out what you're suggesting.

[NeoPa]

... and for my part I'm finding your explanations leave me without much idea of what you want help with. I have [qryPI01] & [qryPI02] and you have something else. Until you can make it clear what you have then I cannot make the answer any clearer for you. One thing depends directly on the other.

What are your subreports called? What do they use as a record source?

If you can tell me the names of the sub-report objects that contain the reports you're referring to I can help you set the .Filter property in order to filter one by 'Spanish' and the other by 'French'. Without that information from you though, I am in no position to tell you any more than I have already. I'm scrabbling in the dark.

[clintonf]

Thanks; I'll do my best to give useful information.

OK, my main report is called 'Monthly report' and has 3 sub-reports

• French growth
• Spanish growth
• German growth

Each of the sub-reports use the same query, 'Program growth' , as their record source:

SELECT DISTINCT lastMonth() AS [Grew In], oma.customerid, oma.[last name], oma.[first name], oma.program
FROM [Attendance: two months past] AS tma
RIGHT JOIN [Attendance: one month past] AS oma
ON oma.program = tma.program
AND oma.customerid = tma.customerid
WHERE oma.program = [Prog]
AND tma.program IS NULL
ORDER BY oma.customerid

When I run the report I'm prompted three times for [Prog], whose inputs are 'French', 'Spanish', and 'German', respectively.

I'm trying to have the 'French growth' sub-report automatically supply the parameter 'French' to it's record source's [Prog] parameter, ('Spanish growth' automatically supplied 'Spanish', etc). when 'Monthly report' is run.

Thanks!

[NeoPa]

Excellent. Change the SQL to the following :

...
WHERE oma.Program Is Not Null
  AND tma.Program Is Null
...

Now, for each subreport object, set the .Filter property to :

([Program]='French')

Change this for the other subreports to reflect the required language.

[clintonf]

This helped brilliantly NeoPa.. sort of.

Your SQL suggestion eliminated the need for me to make 'program' a parameter in the first place (my earlier attempts with SQL gave me false-positive cross-products and I concluded executing the query once-per-program was just my lot in life), so thanks!

But, for the sake of completeness, I couldn't the .Filter property in my sub-report. Clicking around in different parts of the report reveals different properties but never the .Filter. I have seen .Filter before but I couldn't find it this time around.

At any rate I didn't search too carefully for it because you solved my problem in a different way.

Thanks so much for your patience and advice!

[NeoPa]

Good point. The .Filter property is of the Report object and not of the Subreport object of a Report.

As it's perfectly possible that you're using the same actual Report object to go into each of your Subreports it may not make sense to set the .Filter for the Report (used in the Subreports) as part of the inherent design. However, you can set it at run-time.

The main Report's .OnOpen or .OnLoad event procedure can be set up to handle the updating of the .Filter property of the Reports in each of the Subreports.