Storing multi status dates

[CarolynD]

Hi -

I've searched for the forum and have read everything on the audit trails, and storing history and have found nothing that quite fits my predicament.

Here's what I have:

tblFiles
FileNum
CompanyNum
...

tblStatus
StatusID
StatType: Case, Med, Psych, ProjectRisk
Status: different for each of the 4 above
[ie Case : Open, Inactive, Closed; Med : 1, 2, 3, 4)

tblStatusLog
FileNum
StatusID
Provider
Date

Reporting for this has been a nightmare - since CaseStatus needs to be "Open" and each case needs to have a Med or Psych Severity; doing a count of the different status' by Company number is a multistep, inefficient set of queries (using the max date of the type hasn't worked well)

Is this the best way to set something like this up? ... or are there alternative ways to store this data that will accomplish this and provide a better way to report on it?


Thanks in advance,
Carolyn

 

[FoFa]

We could talk our way through it. I am not sure I understand totally by your description. What ties your tables together?

Because it would seem, just to start us off that to get a count you could:
SELECT T1.CompanyNum, Count(T2.StatusID) as Cases
FROM tblFiles T1
INNER JOIN tblStatusLog T3 ON
T3.FileNum = T1.FileNum
INNER JOIN tblStatus T2 ON
T2.StatusID = T3.StatusID
WHERE T2.Status = "OPEN"
AND T2.StatType in ("Med","Psych")
Group By T1.CompanyNum

 

[CarolynD]

Hi -

tblFiles is related to tblStatusLog by the FileNum ... and tblStatusLog is related to the tblStatus by the StatusID.

A report would look something like this:

Company
Files Opened 6
Med Stat 1 2
Med Stat 2 3
Psych Stat 2 1
Files Closed 4


Each file has a process like:

File A

[StatusType: Case]
Opened 1/1/03

[StatusType : Med)
Med Stat 1 1/1/03

[StatusType: Case]
Inactive 1/1/04

[StatusType: Case]
Re-activated 1/2/04

[StatusType : Med)
Med Stat 2 1/3/04


The current status' are shown using the max date for each type:
File A
Re-activated 1/2/04
Med Stat 2 1/3/04


But reporting at the high level has been a nightmare ... any way to store the most current status in a table ... and somehow just archive the history of the changes with a date they were changed?

So, the File tbl could look something like :

tblFiles
FileNum
CompanyNum
CaseStatus
CaseStatusDate
MedStatus
MedStatusDate
PsychStatus
PsychStatusDate
ProjRiskStatus
ProjRiskStatusDate
...

Thanks in advance !

 

[FoFa]

OK What if you try:
SELECT T1.CompanyNum as Company, T2.StatusID as Status, Count(*) as HowMany
FROM tblFiles T1
INNER JOIN tblStatusLog T3 ON
T3.FileNum = T1.FileNum
INNER JOIN tblStatus T2 ON
T2.StatusID = T3.StatusID
Group By T1.CompanyNum, T2.StatusID