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
_______________________________________________
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.
