we have accounts that are assigned a status: red, yellow, green
(actually these are values 3, 2, 1)
every time the status of an account changes - the change is date stamped
and stored in tblAcctHist (history). this table records AcctID, old
status, new status, date & time.


my question: i want to know how many accounts were red every day for the
last month. (show count for every day)


I'm not sure how to go about it because i don't have a record for
everyday the account is red.
just a record when it went red (3) and another when it changed "out of
red" ( to yellow (2) or green (1))

help please?

thanks
laurel

So the table you described looks something like this:

tblAcctHist
AcctID OldStatus NewStatus DateStamp
1 3 2 12/29/2004
1 3 2 12/29/2004
2 2 1 12/27/2004
3 3 1 12/26/2004


You want to know the count for a month for OldStatus equaling 3 (Red) then
the SQL would look like this:

SELECT tblAcctHist.DateStamp, Count(tblAcctHist.OldStatus) AS
CountOfOldStatus
FROM tblAcctHist
WHERE tblAcctHist.OldStatus=3 AND tblAcctHist.DateStamp Between #12/1/2004#
And #12/31/2004#
GROUP BY tblAcctHist.DateStamp;

And would return:

DateStamp CountOfOldStatus
12/26/2004 1
12/29/2004 2


Hope this helps.

_______________________
Steve Huff
http://www.huffs.us
Generic email: null (AT) huffs (DOT) us


"parn" <lparnellDELETEME (AT) san (DOT) rr.com> wrote