CODEWITHSUNDEEP
sqlms-access-2010ms-access-2007maxdate
Jason Chan
Jason Chan

Reputation: 45

SQL Access 2007/2010 Selecting Max Date with Distinct ID

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!

Upvotes: 0

Views: 65

Answers (1)

user5992977
user5992977

Reputation:

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..

Upvotes: 1

Related Questions