Keeping point in time status info for reporting

[Ahzryn]

I need some suggestions on how to store historical information at a point in
time for reporting. Specifically, I am cleaning up a database for a customer
that tracks business opportunities. Each opportunity has a status such as
entered, in progress, on hold, win, loss, etc. Current reporting is no
problem. However, the issue is that if an opportunity is in progress in
December '04, and the status is updated to a win in January '05, their 04
reporting will no longer count it as in progress, which it was at the time.

I know it is a table design issue, it's just that for some reason correcting
it has stumped me. I considered creating an audit table that tracks every
change to every field with a date and time, however that doesn't seem to help
my reporting issue.

I am sure this is a common question with an easy solution, but I could not
come across anything similar in the archives.

Tammy

 

[Brendan Reynolds]

How about two tables, Opportunities and StatusChanges. Relationship one
Opportunity, many StatusChanges. StatusChange includes OpportunityID
(foreign key relating this table to Opportunities) Status, and DateOfChange.
You can now create your historical reports by joining the two tables ...

SELECT Opportunities.*, StatusChanges.* FROM Opportunities INNER JOIN
StatusChanges ON OpportunityID = StatusChangeID ORDER BY DateOfChange

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

Oops! That join would, of course, be ...

ON Opportunities.OpportunityID = StatusChanges.OpportunityID

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Ahzryn]

The thumping sound you hear is me beating my head against my cube wall. Of
course you are absolutely right. I thought the audit table seemed a bit much


I swear, the next time they offer me an access contract that is "just to
design a few reports they can't figure out" I am running. Usually they can't
figure them out because the structure was flawed to begin with! Give me help
desk work any day.

Tammy