Acc2003 - Need some suggestion/guidance/ solution

[Ignorant]

Hi
I have a table which stores every date a case is posted. Now with the below query I was able to pick out the latest stage of a case based on the date.

SELECT *
FROM CaseStagesTran AS a
WHERE (((Exists (SELECT 1 FROM CaseStagesTran b 
                     WHERE b.caseID = a.caseID
                         AND b.caseDate > a.caseDate 
      ))=False));

As it happens when a case is posted for some purpose say, 'arguments' and the case is disposed off the same day (i.e. decreed/dismissed) the table would continue to hold both the data and date of transaction of both the events arguments & decree would be the same.
There is a case stage master and tran which hold the casestageID
So how do I go about to do my desired task of retrieving the last record of each case and exclude the resultset it if it has certain (31,55,65,68,70) caseStageID
presently my query is as below but if a "case" has the same date then it picks up only the 1st record it encounters (obviously) so Iam of the view some query must run deeper if the dates are same for a caseID and determine if it falls in any of the exception caseStageIDs (eg 31,55,65,68,70) if so exclude it for the query results

Iam of opinion we make a query to retrieve latest records of each case and group them by caseID. Then have some code to check if there exist the excluded list of caseStageIDs (31,55,65,68,70) for each particular case, if its true then that record will not form part of the query result..is it ok.

The table data looks like this....
CaseStageTran

caseID   stageID   stageDate
1            21           1/10/10    
1            32           5/10/10
2            45           29/9/10
3            12           5/10/10
2            10           5/10/10
1            31           5/10/10
2            70           29/9/10

so how to exclude rec having caseid 1, 2 as it falls within the exclusions having caseStageID 31,55,65,68,70

Any suggestion is welcome

[GolferGuy]

When I have a complicated series of tests that need to be done to get the final record set, I generally will use multiple queries. Query A will do the first selection process, then query B will use Query A as it's source (as if it were a table) and then further select the needed records. Then generally only works for up to 3 or 4 queries before Access states the query is too complex to run. At that point, I have the last query that is not too complex be a build table query. Then I pick up doing more queries from that "work" table.
Hope this helps.

[Ignorant]

Thanks golfer that's what I am doing, but the diffculty is in making a query to exclude if and only if the record has a particular caseStageID. See if you can help me more from this query..
QryRetrieveLatestStage

SELECT *
FROM CaseStagesTran AS a
WHERE (((Exists (SELECT 1 FROM CaseStagesTran b 
                     WHERE b.caseID = a.caseID
                         AND b.caseDate > a.caseDate 
      ))=False));

[GolferGuy]

It seems like approaching the situation from this angle is not working, so walk around the problem and look for a different way to accomplish what you want. This is my suggestion without really studying this because I don't understand the difference between a and b.
First I think you are going to have to start recording the time so you can tell which record is truly the most recent or the oldest when two records have the same date.
Next, it seems you are looking for the oldest record (or newest, I'm not sure) so my thinking would be to only select these records and ignore all the rest. Then you would have all the oldest (or newest) b records which you could compare to the a records in another query to determine which is the oldest (or newest) for your final elimination.
Hope this makes some sense for you.

[Allan Murphy]

Would TOP or DMAX be useful?

[Ignorant]

A friend suggested this qry to retrieve the list of caseIDs having the excluded list...
Qry1

SELECT CaseStagesTran.caseID, CaseStagesTran.caseStageID
FROM CaseStagesTran
GROUP BY CaseStagesTran.caseID, CaseStagesTran.caseStageID
HAVING (((CaseStagesTran.caseStageID) In (31,35,55,65,68,70)));

Now the below qry outputs only the records which are not part of the above query...
Qry2

SELECT CaseStagesTran.*
FROM CaseStagesTran
WHERE (((CaseStagesTran.CaseID) Not In (SELECT CaseID from Qry1)));

Now the only requirement is to extract the latest dates for each caseID. I tried

SELECT *
FROM Qry2 AS a
WHERE (((Exists (SELECT 1 FROM Qry2 b 
                     WHERE b.caseID = a.caseID
                         AND b.caseDate > a.caseDate 
      ))=False));

but it goes into some kind of infinite loop

[Allan Murphy]

Query 1 - select the maximum date for each Case ID

SELECT casestagetran.caseid, Max(casestagetran.stagedate) AS MaxOfstagedate
FROM casestagetran
GROUP BY casestagetran.caseid;

Now use query 1 to exclude the Stage ID as per your list, also use the table to display the stageid in the result

SELECT casestagetran.caseid, casestagetran.stageid, casestagetran.stagedate
FROM Query2 INNER JOIN casestagetran ON (Query2.caseid = casestagetran.caseid) AND (Query2.MaxOfstagedate = casestagetran.stagedate)
WHERE (((casestagetran.stageid)<>31 And (casestagetran.stageid)<>55 And (casestagetran.stageid)<>65<>68 And (casestagetran.stageid)<>70))
ORDER BY casestagetran.caseid;

This will give you
Case id Stage ID Stage date
1 32 5/10/2010
2 10 5/10/2010
3 12 5/10/2010

[Ignorant]

Sorry Allan the results are same as in my previous post (Qry1, Qry2)

[Allan Murphy]

My results as shown in my previous post are as per your original post requirements.