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...
clintonf
Last Activity: Mar 19 '14, 11:36 PM
Joined: Sep 6 '11
Location:
-
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:
...Code:SELECT DISTINCT lastMonth() AS [Grew In], oma.customerid, oma.[last name], oma.[first name], oma.program FROM [Attendance: two months past] AS tma
Leave a comment:
-
Hey NeoPa,
I'm afraid I don't understand where I would apply your suggestion:
Does this go somewhere in the Report or in the query?Code:([qryPI01].[Program]='Spanish') AND ([qryPI02].[Program]='French')
I appreciate the help you've supplied so far but I'm having trouble figuring out what you're suggesting.Leave a comment:
-
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...Leave a comment:
-
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.Leave a comment:
-
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...Last edited by clintonf; Mar 6 '14, 12:55 AM. Reason: Figured out a better way to describe the challenge.Leave a comment:
-
clintonf started a topic How can I pass a query parameter to a report with >1 use of the same query?in AccessHow can I pass a query parameter to a report with >1 use of the same query?
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... -
Can queries be re-used or should they be re-coded?
Hi all,
I am wondering if I should re-type the SQL in my queries if I am using the same query as a sub-query in a different query.
An example:
I have a query, check_attendanc e
Now, say I have a second query, check_attendanc e_by_monthCode:SELECT customerid, class_date, class_month FROM attendance
...Code:SELECT customerid, class_date, class_month FROM attendance WHERE class_month=[mont
-
Thanks NeoPa!
I've been trying out your suggestion and it's working great!Leave a comment:
-
Access SQL spanning years
Hi all,
How can I span two years using SQL?
I'm using Access 2007 and writing the queries directly in the SQL editor.
Here's an example of what I'm trying to accomplish:
I have a table, 'attendance' that tracks class attendance
...Code:CREATE TABLE attendance attendanceid INT AUTOINCREMENT, last_name TEXT, first_name TEXT, classmonth INT, classyear INT
-
I'm looking at attendance data, in 6 classes, for each month. I want to use the combobox to output one month of attendance data at a time and move back and forth between months.
Thanks!Leave a comment:
-
How do I Make ComboBoxes Visible in Report Header
Hey all,
Apparently this isn't as obvious as I thought in Access 2007.
In my report's design view I added a combobox to the Report Header area. The combobox is filled via a query and I used the default options for the rest of wizard that pops up when you create a combobox.
The problem is: the combobox isn't visible!
Under the properties- enabled = yes
- column count = 2 (default)
- everything
-
Awesome!
Thanks so much; that's very helpful.Leave a comment:
-
Cool!
Let's see then...
The checkboxes:- grade_12_chk
- grade_11_chk
- grade_10_chk
- grade_9_chk
Grade is a text field, ie. "twelve", "eleven", etc.
Thanks!Leave a comment:
-
Hey NeoPa,
Thanks for the reply.
I'll clarify what I'm trying to accomplish, thanks for your patience.
Each grade (9 - 12) will have its own checkbox. If I check "Grade 12" only Grade 12's will be displayed. If I then check "Grade 10", grade 10's AND grade 12's will be displayed. If I then uncheck the "Grade 12" checkbox only grade 10's will be displayed. At least, that's what I...Leave a comment:
-
How to add VBA filters to reports
Hey all,
I'm a little new to Access 2007 and need help coding filters in VBA.
Right now my report has 3 fields:
FirstName LastName Grade
In the header of my report are 1 checkbox for each grade (9 - 12).
I want to be able to display, for example, only students in Grade 12 when I click the "Grade 12" checkbox.
The ApplyFilter method won't work because, according...
No activity results to display
Leave a comment: