Responses in-line:
No, it's just a little quirky due to your design choices. Typically, I would have a "current status" value in the Consumer record that's an FK into the Status history table.
A common problem with data modeling is determining if your system is a reflection of the current version of the system, or the history of the states of the system, or a mix of both.
Interesting. I've always just followed the principle that if a parent can own more than one value for a particular item, you put that item in a child record, and keep the child records in a separate table and JOIN it as needed. I thought that was a requirement for normalization. It would seem to engender less upkeep than creating a need to update something in the parent record whenever you change something in the child record. Also, if a need develops for the parent to own more than one of something else that was not part of the original design, something which would require yet another child table, I would then also have to modify the parent table to add a "most recent" field for that. Modifying existing tables containing data seems more risky than simply adding empty tables--especially when I have to do it in a completely automated way for remote locations whose hardware I don't trust.
The results have to total the most recent hours records belonging to Consumers whose most recent Status is "Approved".
I think your description of the hours result is a little ambiguous, and since I've guessed wrong twice, I'll stop now. ;)
Are you SUM'ming Hours records for the Consumer SINCE their status became approved, or are you picking the LAST Hours record for Consumers currently Approved? Either is easy. Ish.
I need a total of the Hours stored to all of the LAST hours records that are owned by Consumers whose LAST status record contains "Approved" in the "type" field. Bear in mind that a Consumer may not own any records in the Hours table at all.
And my testing indicates that a GROUP BY clause is required somewhere.
It's in there, with the sub-select with the MAX
Yes it is. Sorry that I missed it.
Thanks for all of your help.
Ken