SELECT Consumer.*, Status.*, Hours.AppHours
FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
WHERE Status.Date =
(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK)
AND Status.Type = "Approved"
AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE
Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
I actually can't get this to run without errors. Interpolating, I
**think** you meant:
SELECT Consumer.*, Status.*, Hours.AppHours
FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
WHERE Status.Date =
(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK AND Status.Type = "Approved" GROUP BY
Stat2.ConFK)
AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE
Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
However, I get "Function missing )" on the last subquery on this
this, and neither intellisense nor I can find any unmatched parens.
Also, the first subquery is grouping on the Status table FK, while
the second subquery groups on the Hours table PK. I think they should
both group on the same key, but I don't understand which one that should be.
Thanks.
Ken
_______________________________________________
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/
** 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.
