Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Location
    Hawaii
    Posts
    5

    Question Unanswered: Query building - finding the "highest" value

    Hello - I'm trying to figure out the "right" way to build a query that looks at personnel certifications and identifies the highest level of certification. In electrician terms, the below ERD shows an example. If a residential electrician gets certified as an apprentice, then later a journeyman, I'd want to only show the name, highest level (journeyman), and date of certification (pretend date is a column). Since the first letter is the only one that changes, and also happens to fall into alphabetical order, I was thinking a string compare of some sort would work. I know the StrComp function returns a 1,0,-1 value but I'm not sure that's the best way. I've also played around with a 'weight' column for each cert, but that seemed like rubbish. Your thoughts?

    Click image for larger version. 

Name:	Screen Shot 2015-02-20 at 9.44.43 PM.png 
Views:	5 
Size:	40.6 KB 
ID:	16184

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use a query with a TOP 1 clause and an ORDER BY clause to retrieve data for one row in the table PERSONNEL:
    Code:
    SELECT TOP 1 PERSONNEL.person_fname, PERSONNEL.person_lname, CERTIFICATIONS.certification_name
        FROM (PERSONNEL INNER JOIN 
                 CERTASSIGNMENTS ON PERSONNEL.person_id = CERTASSIGNMENTS.person_id
                ) INNER JOIN 
                 CERTIFICATIONS ON CERTASSIGNMENTS.Certification_id = CERTIFICATIONS.certification_id
        WHERE PERSONNEL.person_id = [Person Id]
        ORDER BY CERTIFICATIONS.certification_name DESC;
    Where [Person id] is the id of the desired row.
    Have a nice day!

  3. #3
    Join Date
    Jun 2009
    Location
    Hawaii
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    You can use a query with a TOP 1 clause and an ORDER BY clause to retrieve data for one row in the table PERSONNEL:
    Code:
    SELECT TOP 1 PERSONNEL.person_fname, PERSONNEL.person_lname, CERTIFICATIONS.certification_name
        FROM (PERSONNEL INNER JOIN 
                 CERTASSIGNMENTS ON PERSONNEL.person_id = CERTASSIGNMENTS.person_id
                ) INNER JOIN 
                 CERTIFICATIONS ON CERTASSIGNMENTS.Certification_id = CERTIFICATIONS.certification_id
        WHERE PERSONNEL.person_id = [Person Id]
        ORDER BY CERTIFICATIONS.certification_name DESC;
    Where [Person id] is the id of the desired row.
    Thanks, Sinndho! That's almost what I'm looking for, but it's dependent on one ID. In pseudo-code what I'm looking for would be something like:

    foreach person in personnel
    select top 1 certification from certassignments

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To retrieve a set that comprises every person in PERSONNEL, we first create a (sub)query that yields the desired row from CERTASSIGNMENTS (person_id and certification_id):
    Code:
    SELECT CERTASSIGNMENTS.person_id, MAX(CERTASSIGNMENTS.certification_id) AS certification_id
         FROM CERTASSIGNMENTS
         GROUP BY CERTASSIGNMENTS.person_id;
    We must link (INNER JOIN) this query to both PERSONNEL and CERTIFICATIONS according to the database diagram you posted. With a SQL Server, we can use:
    Code:
    SELECT PERSONNEL.person_fname, PERSONNEL.person_lname, CERTIFICATIONS.certification_name
    FROM PERSONNEL INNER JOIN 
        (SELECT CERTASSIGNMENTS.person_id, MAX(CERTASSIGNMENTS.certification_id) AS certification_id
         FROM CERTASSIGNMENTS
         GROUP BY CERTASSIGNMENTS.person_id
        ) AS A ON PERSONNEL.person_id = A.person_id INNER JOIN
         CERTIFICATIONS ON CERTIFICATIONS.certification_id = A.certification_id
    Unfortunately, the SQL interpretor of Access cannot understand this expression, so:
    1. We build a first query (Qry_Max):
    Code:
    SELECT CERTASSIGNMENTS.person_id, MAX(CERTASSIGNMENTS.certification_id) AS certification_id
         FROM CERTASSIGNMENTS
         GROUP BY CERTASSIGNMENTS.person_id;
    2. We link (INNER JOIN) Qry_Max to both PERSONNEL and CERTIFICATIONS, as described above:
    Code:
    SELECT PERSONNEL.person_fname, PERSONNEL.person_lname, CERTIFICATIONS.certification_name
    FROM (PERSONNEL INNER JOIN 
          Qry_Max ON PERSONNEL.person_id = Qry_Max.person_id
         ) INNER JOIN 
          CERTIFICATIONS ON Qry_Max.certification_id = CERTIFICATIONS.certification_id;
    Have a nice day!

Tags for this Thread

Posting Permissions

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