0

I believe a lot of people have already asked this question as I have read all the topic from here. But the Problem is I have 3 related tables instead of 2 and I'm not sure how to code for that

I have a table: tbl_Instruments, tbl_Record and tbl_Cal_By. tbl_Instruments has all the instruments information including their ID. tbl_Cal_By has the information for whoever is calibrating the tool. tbl_Records has all the instruments Records and their Calibration date. It inherits the ID from tbl_Instruments as Inst_ID and the Name from tbl_Cal_By as Name_ABBR.

tbl_Instruments: ID, Type

tbl_Cal_By: Cal_ID, Name_ABBR

tbl_Records: Record_ID, Inst_ID, Cal_Date, Name_ABBR

Here is my code.

SELECT tbl_Records.Inst_ID
    ,tbl_Instruments.Type
    ,Max(tbl_Records.Cal_Date) AS MaxOfCal_Date
    ,tbl_Cal_By.Name_ABBR
FROM tbl_Cal_By
RIGHT JOIN (
    tbl_Instruments INNER JOIN tbl_Records ON tbl_Instruments.ID = tbl_Records.Inst_ID
    ) ON tbl_Cal_By.ID = tbl_Records.BY
GROUP BY tbl_Records.Inst_ID
    ,tbl_Instruments.Type
    ,tbl_Cal_By.Name_ABBR;

Desired result:

enter image description here

Any help will be appreciated!

Shaohao
  • 3,471
  • 7
  • 26
  • 45
Jason Chan
  • 45
  • 10

1 Answers1

1

You can do this in several methods, one of the is exists :

SELECT tbl_Records.Inst_ID
    ,tbl_Instruments.Type
    ,tbl_Records.Cal_Date AS MaxOfCal_Date
    ,tbl_Cal_By.Name_ABBR
FROM tbl_Cal_By
RIGHT JOIN (
    tbl_Instruments INNER JOIN tbl_Records ON tbl_Instruments.ID = tbl_Records.Inst_ID
    ) ON tbl_Cal_By.ID = tbl_Records.BY
WHERE NOT EXISTS(SELECT 1 FROM tbl_Records t
                 WHERE t.Inst_ID = tbl_Instruments.ID
                      AND t.Cal_date > tbl_Records.Cal_Date)

I'm not entirely sure about access syntax and aliases.. so maybe you will have to adjust it a little bit - like alias the first tbl_records so it will recognize it, or maybe it will work..

  • This works Perfectly ! But Do you mind explaining the last part starting from WHERE? Sorry im kinda new to sql ~~ – Jason Chan Mar 01 '16 at 15:00
  • Sure, I'll also appreciate if you accept my answer (V) . There where uses NOT EXISTS function that checks if the query after returns something or not. It will filter any row that return something there. So what I've done, is for each record, check if there is a bigger date then that one, if so - it filters it from the result . This will leave us with only the latest dates. @JasonChan –  Mar 01 '16 at 15:03
  • i know it has been a long time but recently I have added tbl_Records.Path and the old issue came back. So to filter it again, I tried to add "AND t.Path = tbl_Records.Path" but seems like this is wrong? – Jason Chan Mar 21 '16 at 20:58