Maybe this will do what you want:
SELECT Status.ConFK, LastHours.AppHours; FROM Status ; INNER JOIN (SELECT ConFK, MAX(Date) AS MaxDate; FROM Status; GROUP BY ConFK) M ON Status.ConFK = M.ConFK AND Status.Date = M.MaxDate ; AND Type = "Approved"; LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours; FROM Hours; INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate; FROM Hours; Â GROUP BY ConFK) MH ; Â ON Hours.ConFK = MH.ConFK AND Hours.Date = MH.MaxHoursDate ) LastHours ; ON Status.ConFK = LastHours.ConFK
Yes, using the data I provided (and which you assembled into tables), this gets:
CONFK APPHOURS 1 .NULL. 3 12
The query itself doesn't sum anything, but I assume I can convert NULLs to 0 and sum the APPHOURS column, and use _TALLY or RECCOUNT() to provide a total of approved Consumers.
Thank you very much!!!
Ken