none
How to get MAX(date) from other table RRS feed

  • Question

  • I have two table, ie, task_schedule and task_run_history.

    <task_schedule>

    <task_run_history>

    task_run_history's foreign key is task_id which reference task_schedule's task_id.

    I want to select * from task_schedule and max(run_datetime) from task_run_history and run_result like following.


    task_id     title      file_path                       start_time     ........................  max(run_time)

    --------     -----     ------------------------     ------------                               -----------------

    1             title1     c:\cdm\cdm......            6/1/2018 10:20:30 AM              3/4/2018 9:20:10 AM

    2             title2     c:\cdm\cdm......            6/1/2018 11:10:21 AM              4/5/2018 9:20:10 AM

    3             title3     c:\cdm\cdm......            6/1/2018  1:21:22 PM               5/3/2018 9:20:10 AM

    4             title4     c:\cdm\cdm......            6/1/2018  1:21:22 PM               6/2/2018 9:20:10 AM

    To get this result, how do I make sql statement?


    • Edited by Jeff0803 Thursday, June 14, 2018 10:55 PM
    Thursday, June 14, 2018 10:47 PM

All replies

  • If it does not have to be updatable, just join with the other table, turn the query into a Totals query (sigma button), choose "group by" for most columns, and Max for the date column.


    -Tom. Microsoft Access MVP

    Friday, June 15, 2018 2:54 AM
  • Hello Jeff0803,

    Try some code like.

    SELECT task_schedule.task_id, task_schedule.title, task_schedule.file_path, task_schedule.starttime, max( task_run_history.run_datetime) AS run_datetime 
    FROM task_schedule
    left JOIN task_run_history
    ON task_schedule.task_id = task_run_history.task_id
    Group By task_schedule.task_id, task_schedule.title, task_schedule.file_path, task_schedule.starttime;
    
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 15, 2018 6:10 AM
  • Alternatively, to return a non-updatable result table, you can join the  task_schedule to a subquery:

    SELECT task_schedule.*, LatestRunTime
    FROM  task_schedule INNER JOIN
        (SELECT task_id, MAX(run_datetime) AS LatestRunTime
         FROM task_run_history
         GROUP BY task_id) AS LatestRuns
    ON  task_schedule.task_id = LatestRuns.task_id);

    Or if you want to return an updatable result table:

    SELECT task_schedule.*
    DMAX("run_datetime","task_run_history","task_id = " & [task_id]) AS LatestRunTime
    FROM  task_schedule;

    Ken Sheridan, Stafford, England

    Friday, June 15, 2018 11:19 AM