Originally posted by 3nder
I want to filter the data on my table before I run a total query on the results. I can't use ApplyFilter because the query doesn't see the filter that's been applied. Any suggestions? TIA.
pls tell us more about how you filter your table (by a query or filtered by form) i believe by just a simple query, we can both filter the table and calculate the totals
What I currently have (and I'm certainly open to a more elegant solution) is this:
A form (MainReport) which contains a series of subforms.
On_Current triggers an event on this MainReport form -- Me.Filter = "Semester = [Enter semester]"
I just realized that the problem is probably in the fact that the subforms aren't bound to this filter since the textbox on each subform that is supposed to hold the results of these various counts has a recordsource like this: SELECT [YearQuery] FROM YearQuery.
The problem I'm trying to solve is this:
I need a form with textboxes which count totals for different groups of information. So, if the Sessions table has a field for Year which is a combobox, I need something on this "report" form which will say we have had 2 First-Year, 6 Sophomore visits (and so on).
My boss does not like the report feature since he wants to copy and paste (and he probably wants to edit, for all I know).
Even if I can "sell" him on the report feature of Access, I can't seem to get an Access report which will run a query count for the 7 or so different subcategories that he needs.
First-Year, Sophomore, Junior, Senior, Graduate, Staff, Other
ESL = Yes or No
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
# of individual people who visited
average # of visits per person.)
Right now, each of these is a separate subform tied to this MainReport form (except for the average # of visits per person which I can do with a calculated result).
However, I have to enter the semester for which I want these results 7 different times -- 14 times if I want to print that page.
A typical query (YearQuery in this case) looks like this right now:
SELECT Sessions.Year, Count(Sessions.Year) AS CountOfYear
FROM Semesters INNER JOIN Sessions ON Semesters.semesters = Sessions.Semester
GROUP BY Sessions.Year, Semesters.semesters
HAVING (((Semesters.semesters)=[Enter semester]));
I've had to leave that [Enter semester] alone here at the end in order to get any results at all -- I know this is why I'm having to enter the semester so often.
I know there's GOT to be a more elegant solution here, but this is the best I've been able to kludge together so far.
in the 'MainReport' form, can you put a combo box (lets name it cmbSemester) that holds all semester value from Semesters table and base the form recordsource to a query like this: (it requires vb codes)
Private Sub cmbSemester_Change()
dim stSQL as String
stSQL="SELECT field1, field2 FROM Semesters WHERE semester=" & me.cmbSemester.value
me.RecordSource = stSQL
field1, field2 are all the field names you need on the form. if you try this, pls remove the query in on_current event of the form.
you will not need to enter semester many times, also all other existing calculated textboxes may work correctly.
Unfortunately, this isn't getting me anywhere either, probably due to my still being new to Access/VB/SQL.
My understanding is that a form can only be bound to a query or table -- so, how do I bind the table to the VB sub in the previous message?
What I was able to do was get the calculated result to give me a total for ALL records -- but it still wouldn't give me the result for what I'd selected in the combobox. In fact, I keep getting a runtime 2001 error telling me that I cancelled the process (I didn't -- obviously I've got something set wrong).
Form - Sessions (this contains most of the data which needs to be counted -- it also contains fields bound to the next table -- also fields which need to be counted. The form is bound to the Sessions Table.)
Table - StudentBasic (contains the fields Sex and ESL which need to be counted -- these fields are on the Sessions form as well, but they pull their data from this form)
Table - Semester
Form I need is "MainReport" where a not computer-savvy user should be able to select Semester X and then the following fields would show the count for the chosen semester:
1) Total number of visits (could be a simple count of the Sessions.SessionID field)
2) Total number of individuals (count of distinct Sessions.StuID)
2a) Average # of visits per person -- I think I can still make this one work!!
3) Number of each sex (count of StudentBasic.Sex, grouped by Sex --- could this be counted from the Sessions form since the field appears there? Does that make things simpler or even worse?)
4) Number of students identified as ESL (count of StudentBasic.ESL, grouped by ESL -- this is a checkbox field, if that makes any difference)
5) Number per day of the week (count of Sessions.DayWeek, grouped by DayWeek)
6) Number per year (count of Sessions.Year, grouped by Year)
7) Number per major (count of Sessions.College, grouped by College)
Is my boss asking the impossible?
Is it easier to get an actual Access report to list all of these things? (I haven't been able to get that to work either)
He does NOT like the report feature because he can't copy and paste, but if I'm essentially wasting time trying to get Access to go through gyrations it can't quite do, I'd rather just tell him to deal with a report (if I could get that to work right, that is).
Would it be easier to somehow dump the necessary info into Excel and try to program Excel to perform the counts? (I'm not sure I could get Excel to do it either.)