Acc2003 - Need some suggestion/guidance/ solution

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.

Code:

---

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

Code:

---

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

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.

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

Code:

---

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

---

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.

Would TOP or DMAX be useful?

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

Code:

---

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

Code:

---

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

Code:

---

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

Query 1 - select the maximum date for each Case ID

Code:

---

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

Code:

---

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

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

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