You just need to change the first line of the query to this:
SELECT COUNT(Status.ConFK) AS ConsCount, SUM(LastHours.AppHours) AS
HoursSum;
Frank.
Frank Cazabon
On 22/06/2018 01:04 PM, Ken Dibble wrote:
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
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
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/b57c867d-3680-0e08-5120-fd6f268e7...@gmail.com
** 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.
