Hi.

I want to gather data from 3 tables, and display on a form the most recent record (1 record only), for each Job#.

This union query gathers the similar information from the three tables flawlessly, however I am at a loss when trying to return only the most recent date for each Job#. Here is my Union Query:


SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC]
FROM [TblSubFCR]

UNION SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC]
FROM [TblSubFIR]
ORDER BY [Job#];

UNION SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC]
FROM [TblSubHTR]
ORDER BY [Job#];


I tried to make a second query based on this, and I can sort by date or Job#, and I can get the latest date period, but how to get the latest date for each job? Thanks for any help I can get!