By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

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

P: 16
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!
Mar 5 '14 #1
Share this Question
Share on Google+
14 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Mar 5 '14 #2

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 5 '14 #3

P: 16
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.
Mar 6 '14 #4

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 6 '14 #5

P: 16
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.
Mar 7 '14 #6

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. ([qryPI01].[Program]='Spanish') AND ([qryPI02].[Program]='French')
Mar 7 '14 #7

P: 16
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT lastMonth() AS [Grew In], oma.customerid, oma.[last name], oma.[first name], oma.program
  2. 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)
  3. WHERE oma.program=[Prog] And tma.program Is Null
  4. ORDER BY oma.customerid;
  5.  
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!
Mar 13 '14 #8

NeoPa
Expert Mod 15k+
P: 31,768
I cannot add any more than I have already as there is no new and relevant information in your post.
Mar 13 '14 #9

P: 16
Hey NeoPa,

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

Expand|Select|Wrap|Line Numbers
  1. ([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.
Mar 14 '14 #10

NeoPa
Expert Mod 15k+
P: 31,768
... 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.
Mar 14 '14 #11

P: 16
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT lastMonth() AS [Grew In], oma.customerid, oma.[last name], oma.[first name], oma.program
  2. FROM [Attendance: two months past] AS tma
  3. RIGHT JOIN [Attendance: one month past] AS oma
  4. ON oma.program = tma.program
  5. AND oma.customerid = tma.customerid
  6. WHERE oma.program = [Prog]
  7. AND tma.program IS NULL
  8. 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!
Mar 16 '14 #12

NeoPa
Expert Mod 15k+
P: 31,768
Excellent. Change the SQL to the following :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE oma.Program Is Not Null
  3.   AND tma.Program Is Null
  4. ...
Now, for each subreport object, set the .Filter property to :
Expand|Select|Wrap|Line Numbers
  1. ([Program]='French')
Change this for the other subreports to reflect the required language.
Mar 17 '14 #13

P: 16
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!
Mar 19 '14 #14

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 19 '14 #15

Post your reply

Sign in to post your reply or Sign up for a free account.