Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2016
    Posts
    1

    Unanswered: MS Access 2016 Queries

    Hi,
    I am using MS Access 2016 to create a table keeping records of Students tests results.
    The table consists of 6 columns (name, test1 .... test5)
    Using a simple query to find who got 2 specific marks, e.g. 15 or 31.
    Now I want to shortlist the query and show only the student/s who got both marks, i.e. 15 and 31
    How can I do this?
    Thanks JTZ

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well a first step would be to design your database in compliance with the rules of normalisation

    if the design was normalised it would be something like
    Code:
    select count(name) from mytable
    where testresult in (15,31) and count(name >=2)
    ..mind you if you ONLY wanted where students got both marks that woudl be
    Code:
    select count(name) from mytable
    where testresult in (15,31) and count(name = 2)
    ..and if you only wanted students who had scored 15 or 31 and you had 5 test results
    Code:
    select count(name) from mytable
    where testresult in (15,31) and count(name = 5)
    ..and if you only wanted students who had scored 15 or 31 or had no results
    Code:
    select count(name) from mytable
    where is NULL (testresult)  OR testresult in (15,31) and count(name >= 2)
    granted if you use a normalised design you'd probably have a parent table for students and a child table for exam results, JOINed / linked on (probably) a student ID
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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