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
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
