>Good question, my first response would be the value only. However it might be nice to be able to provide the origin of the transaction as well.

In this case I suggest to use table valued function (inline), e.g.

create function GetLastTransDate (@RepId int)

RETURN table 
AS

RETURN

with cte as (select max(TransDate) as LastDate, 'T1' as tableName from TransTable1 
WHERE RepID = @RepID
UNION ALL
select max(TransDate) as LastDate, 'T2' as tableName from TransTable2 
WHERE RepID = @RepID
UNION ALL
..),
cte1 as (select *, row_number() over (order by LastDate DESC) as Rn from cte)

select LastDate, tableName from cte1 WHERE Rn = 1

and invoke it this way:

select ..., F.LastDate, F.TableName from myMainTable T CROSS APPLY dbo.GetLastTransDate(T.RepID) F