Results 1 to 6 of 6

Thread: Help With a Querry!

  1. #1
    Join Date
    Jul 2003
    Posts
    43

    Help With a Querry!

    I have a column named PASS of 'y' & 'n'values. I need to run a querry that will pull a list of students who have 8 'y' only. In design view, I'm o.k. pulling the names & some other info but when setting the criteria under PASS, I can't seem to get the desired result.

    Shoul I be using the COUNT or COUNTIF statement? Or any suggestions would be much appreciated. Thank you.


  2. #2
    Join Date
    Aug 2003
    Location
    Chicago, for now!
    Posts
    6

    More info.

    What is the criteria under the column 'pass'...and if it's 1 column and 1 answer how will you get 8 'y's' for 1 name?

    Just a lil more info is need. I'm willing to help though.

  3. #3
    Join Date
    Jul 2003
    Posts
    43
    Here is the querry that I created:

    SELECT MAIN_DATABASE.LNAME, MAIN_DATABASE.FNAME, [STUDENT DATABASE].PASS
    FROM MAIN_DATABASE INNER JOIN [STUDENT DATABASE] ON MAIN_DATABASE.MEM_NUMBER = [STUDENT DATABASE].MEM_NUMBER
    WHERE ((([STUDENT DATABASE].PASS)="y"));

    Sample of the result after running the above:

    LNAME FNAME PASS
    PAT BEENA Y
    VAC JOSEE Y
    TAR JAMES Y
    KON KRYS Y
    DUR NAV Y
    DUR NAV Y
    DUR NAV Y
    DUR NAV Y
    SHE GLA Y
    TOU MIC Y
    TAY LIN Y
    TAY LIN Y
    GUA JANA Y
    GUA JANA Y
    GUA JANA Y

    The output that I would like to see:


    LNAME FNAME COUNT
    PAT BEENA Y = 1
    VAC JOSEE Y = 1
    TAR JAMES Y = 1
    KON KRYS Y = 1
    DUR NAV Y = 4

    And so on, and so forth.

    Even better, how would I also include a COUNT for 'N'for every student.

    Thanks for helping.

  4. #4
    Join Date
    Aug 2003
    Location
    In a galaxy far, far away...
    Posts
    28
    Why don't U user "group by"?

    SELECT MAIN_DATABASE.LNAME, MAIN_DATABASE.FNAME, [STUDENT DATABASE].PASS, count(main_database.lname)
    FROM MAIN_DATABASE INNER JOIN [STUDENT DATABASE] ON MAIN_DATABASE.MEM_NUMBER = [STUDENT DATABASE].MEM_NUMBER
    WHERE [STUDENT DATABASE].PASS="y" group by MAIN_DATABASE.LNAME, MAIN_DATABASE.FNAME

    The query syntax might be incorrect but the main concept is this.

  5. #5
    Join Date
    Jul 2003
    Posts
    43

    Querry

    I tried the recommendation but now I'm getting an error message: 'you tried to execute a query that does not include the specified expression 'PASS' as part of an agregate function.

    Please offer some input.

    Thank you.

  6. #6
    Join Date
    Aug 2003
    Location
    In a galaxy far, far away...
    Posts
    28
    I do apologize.

    The server is absolutely right.
    You have two options. Either leave [STUDENT DATABASE].PASS from your selection - since you count records only where this column is "Y" - or put [STUDENT DATABASE].PASS into the group by clause and it will work.

Posting Permissions

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