Alpha Video Training
Results 1 to 10 of 10

Thread: Query Help Needed

  1. #1
    Member
    Real Name
    David Theoharides
    Join Date
    May 2000
    Location
    Sanford, Maine
    Posts
    203

    Default Query Help Needed

    I keep track of students who make honor roll each quarter.

    I have two tables - One labeled EMF that contains the student's name and ID#, and another named HNR that contains ID#, HR_Grade, and HR_Type. HR_Grade can have the value "QT1GRADE" OR "QT2GRADE" OR "QT3GRADE" - meaning 1st, 2nd or 3rd quarter. HR_Type can have values of "000", "AB" or "A" - meaning "Didn't make honor roll", "Made A's & B's" and "Made all A's"

    The SET is based on EMF table with a ONE to MANY link to HNR with the linking field ID#.

    I need to know which students made the HONOR ROLL 2 out of 3 quarters. In other words they need to have A or AB on two of the three quarters.

    I wrote this query expression, BUT get an error that I have specified an invaled filter order... Can anyone help? Graduation is this week! Thanks - Dave

    ((Hnr->Hr_grade = "QT1 GRADE") .and.
    (Hnr->Hr_type = "A" .or. Hnr->Hr_type = "AB" ) .AND. (Hnr->Hr_grade = "QT2 GRADE") .AND.
    (Hnr->Hr_type = "A" .or. Hnr->Hr_type = "AB" ))

    .OR.

    ((Hnr->Hr_grade = "QT1 GRADE") .and.
    (Hnr->Hr_type = "A" .or. Hnr->Hr_type = "AB" ) .AND. (Hnr->Hr_grade = "QT3 GRADE").AND.
    (Hnr->Hr_type = "A" .or. Hnr->Hr_type = "AB" ))

    .OR.

    ((Hnr->Hr_grade = "QT2 GRADE" ) .and.
    (Hnr->Hr_type = "A" .or. Hnr->Hr_type = "AB" )
    .AND. (Hnr->Hr_grade = "QT3 GRADE" ) .and.
    (Hnr->Hr_type = "A" .or. Hnr->Hr_type = "AB" ))

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Query Help Needed

    David, that makes my head hurt.

    If it were me, I'd design 3 separate operations, to copy the honorees to another table, and base the report on that table.

    Q1 & Q2

    Q1 & Q3

    Q2 & Q3

    When finished I'd weed out the duplicates. If someone is eligible in all 3 quarters, they'll be in the list 3x.

    -- tom

  3. #3
    Member
    Real Name
    David Theoharides
    Join Date
    May 2000
    Location
    Sanford, Maine
    Posts
    203

    Default RE: Query Help Needed

    Thanks Tom... My head hurts too... I've been working on this supposedly simple problem too long. All I want to do is select parent records from a set by querying the records of the child table. Can that be done? If a parent record contains child records that, in this case, equal a certain value, then select it.

    How would you do what you explained in your reply?

    Thanks

    Dave

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Query Help Needed

    David, yes, in principle a cross-level query will find the correct parent table records. The difficulty is how the data is arranged in your tables, coupled with the permutations and combinations that must be dealt with in your retrieval criteria. My first suggestion was an effort to try to break the complex down into smaller more manageable pieces. Let me think about it some more. I'll be back.

    -- tom

  5. #5
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Query Help Needed

    David,

    If the HNR table only contains records for the current school year, here's how I'd approach this problem:

    1) I'd write a short script to process the records in the HNR table, copying only the ID# field value from that table to a temporary table whenever the student made the honor roll in 2 out of 3 quarters.

    2) I'd create a simple 1:1 set with the temp table as parent, and the EMF table as child.

    3) I'd print the report listing the names of eligible students from the set.

    If this is something you wish to explore I'll be happy to coach you through it via email.

    -- tom

  6. #6
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default RE: Query Help Needed

    I think a simpler solution would be to create a field, "honors_total", in the EMF table. Then do a posting operation from the HNR table to add "1" to the appropriate record in the HNR table for every "A" or "AB" found. Then all you have to do is to query the EMF table for everyone with a 2 or a 3 in the honors_total field.

  7. #7
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Query Help Needed

    Dr. Wayne,

    Indeed! Very good! If modifying the structure is an option that looks like it's the easy ticket! (Though it would be fun to write a little script with nested while : end while loops, too!)

    Let's hope the HNR table only has records for the current school year.

    -- tom

  8. #8
    Member
    Real Name
    David Theoharides
    Join Date
    May 2000
    Location
    Sanford, Maine
    Posts
    203

    Default RE: Query Help Needed

    Thanks Guys,

    before reading your responses, I made a copy operation to copy only records that were A or AB from Q1. I then used the same operation for Q2 and then Q3 - resulting in three new tables. I then JOINED Q1 and Q2, then JOINED that new table with Q3. UGH... what a hassle! It did work though and I was able to then filter the resulting like this:

    (Hr_Typeq1"" .AND. Hr_Typeq2"") .OR. (Hr_Typeq1"" .AND. Hr_Typeq3"") .OR. (Hr_Typeq2"" .AND. Hr_Type3"")

    Thanks for both of your suggestions.

    Dave

    David Theoharides, Principal
    Mattanawcook JHS
    Lincoln, ME 04457

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Query Help Needed

    Glad you got your problem solved. In looking at your original post, I'm confused. You posted the filter expression but state that the error message was

    "get an error that I have specified an invaled filter order"

    That error refers to something entirely separate, namely the order expression, not the filter expression.

    Could well be that your original filter, as posted would have worked.

  10. #10
    Member
    Real Name
    David Theoharides
    Join Date
    May 2000
    Location
    Sanford, Maine
    Posts
    203

    Default RE: Query Help Needed

    I understand what you mean, but I tried with and without an order expression. I'll play with that approach some more.
    Thanks

Similar Threads

  1. Serious Help Needed
    By Jared Sutherland in forum Web Application Server v6
    Replies: 1
    Last Post: 05-13-2005, 03:03 PM
  2. Query filter help needed
    By tlyon in forum Alpha Five Version 4
    Replies: 16
    Last Post: 05-20-2002, 04:06 AM
  3. Blank Dates in a Query Within a Query
    By Raymond Lyons in forum Alpha Five Version 4
    Replies: 1
    Last Post: 10-19-2000, 04:03 PM
  4. Query.run() never returns accessible query
    By csda1 in forum Alpha Five Version 4
    Replies: 14
    Last Post: 08-10-2000, 12:34 PM
  5. Help needed ....
    By drgarytraub in forum Alpha Five Version 4
    Replies: 4
    Last Post: 07-24-2000, 01:17 PM

Bookmarks

Posting Permissions

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