Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    Feb 2004

    Exclamation Unanswered: Query Help

    I have a database which contains several tables about people in my school and their test results,

    One of the tables has the different subject details, each subject had a code eg, Maths = 0001, English = 0002 ect

    Then I have a table which had the marks of each student

    I want to make a query which shows the higest mark in each subject

    How do i do this???

    Thank You In Advance

  2. #2
    Join Date
    Sep 2003
    Caldes de Malavella, Spain
    Assuming your "marks" table contains the Course Codes, you need to use the DMax function. create a new query and add the fields from your courses table (the ID field and the descriptive field). Then create another field that would look like this:

    HighestMarkMax("Mark","TblMarks","CourseID = " &ID)

    where ID is your primary key field from your courses table (the one you've added into the query).

    In case you're not familiar with DMax, you can get a full description in help. You can use DCount, DSum, DMin etc. as well.

    Hope this helps!!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy

  3. #3
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    Subjects (code, columns)
    Results (subject_code, student, result)

    select v.*, s.*
    (Select subject_code, MAX(result)
    from results
    group by subject_code) v
    subjects s ON
    v.subject_code = s.code;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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