Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    South Bend, IN (but Texas is home!)
    Posts
    8

    Unanswered: How to query filtered results

    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.


    (This was actually posted last July by someone else -- but there weren't any replies and now I've got the same question. )

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: How to query filtered results

    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

    cheers
    qha_vn

  3. #3
    Join Date
    Jun 2003
    Location
    South Bend, IN (but Texas is home!)
    Posts
    8
    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.
    (Counts Needed:
    Males, Females
    First-Year, Sophomore, Junior, Senior, Graduate, Staff, Other
    ESL = Yes or No
    Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
    Specific major
    Total Visits
    # 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.
    TIA

  4. #4
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Dear 3nder,

    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
    end sub

    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.

    hope i understand your need correctly
    Last edited by qha_vn; 06-24-03 at 04:12.
    qha_vn

  5. #5
    Join Date
    Jun 2003
    Location
    South Bend, IN (but Texas is home!)
    Posts
    8
    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).

    More details:
    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.)

    TIA for any light shed on any of this mess!
    3nder

  6. #6
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    sorry for not making things clear coz ur still new to access

    can you please put the file here so that we can take a look and do that thing for you. it would be easier then showing you how (or send the file to: qha_vn@yahoo.com)

    note: if the file has lots of records, pls delete and leave just some sample data

    cheers
    qha_vn

  7. #7
    Join Date
    Jun 2003
    Location
    South Bend, IN (but Texas is home!)
    Posts
    8
    Let's see if this helps clear things up.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •