Outstanding Drafts

[GreenSlime]

In the course of our business process, we have to draft some documents and
send them to another department (DJ) for advice. I have a table called
"tblDrafts" for recording the movement of these drafts.
It has 4 fields:
CaseID - the identifier of the case
DraftDate - The date the draft was sent out / returned from DJ
ToFromDJ - a text field, validation rule set as "To" and "From"
NoOfDraft - Integer, signaling how many times a certain case has been passed
to DJ for comment.

Some sample data:
CaseID DraftDate ToFromDJ NoOfDraft
268 01/01/2001 To 1
268 03/01/2001 From 1
268 05/01/2001 To 2
374 08/01/2001 To 1
374 10/01/2001 From 1
968 12/01/2001 To 1

Now I would like to make a report showing "outstanding drafts at DJ", which
means a list of case which we have passed to DJ and not yet returned. In the
situation of the sample data, cases 268 & 968 will show, but not case 374.

I have no idea how to make a query for the said purpose. Grateful for advice.
Many thanks

 

[John Spencer]

IF you want just a list of the CaseID's and if you can assume that there is
one "from" record for every "to" record that is completed then you could use

SELECT CaseID
FROM tblDrafts
GROUP BY CaseID
HAVING Count(ToFromDj="To",1,Null) <> Count(ToFromDJ="From",1,Null)

Another and probably better solution you might use.

SELECT A.*
FROM TblDrafts as A LEFT JOIN tblDrafts as B
ON A.CaseId = B.CaseID
AND A.NoOfDraft = B.NoOfDraft
WHERE B.CaseID is Null

IF you are missing records you might get false results. For instance changing
line 2 of your sample data (see below) would mean that the first line would be
returned.

Some sample data:
CaseID DraftDate ToFromDJ NoOfDraft
268 01/01/2001 To 1
268 03/01/2001 From 2<<<<
268 05/01/2001 To 2
374 08/01/2001 To 1
374 10/01/2001 From 1
968 12/01/2001 To 1


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[GreenSlime via AccessMonster.com]

Thank you John, but neither SQL works.

I finally find a SQL from another thread and change it field name according
to my database and got it work. Copy it here for reference, please.

SELECT *
FROM tblDraft AS I1
WHERE (((I1.DraftDate)=(SELECT MAX([DraftDate]) FROM [tblDraft] AS I2
WHERE I2.[CaseID] = I1.[CaseID])) AND ((I1.ToFromDOJ)="To"))
ORDER BY I1.DraftDate;


Still, I have no idea on why this SQL work. Any enlightening is greatly
appreciated.

IF you want just a list of the CaseID's and if you can assume that there is
one "from" record for every "to" record that is completed then you could use

SELECT CaseID
FROM tblDrafts
GROUP BY CaseID
HAVING Count(ToFromDj="To",1,Null) <> Count(ToFromDJ="From",1,Null)

Another and probably better solution you might use.

SELECT A.*
FROM TblDrafts as A LEFT JOIN tblDrafts as B
ON A.CaseId = B.CaseID
AND A.NoOfDraft = B.NoOfDraft
WHERE B.CaseID is Null

IF you are missing records you might get false results. For instance changing
line 2 of your sample data (see below) would mean that the first line would be
returned.

Some sample data:
CaseID DraftDate ToFromDJ NoOfDraft
268 01/01/2001 To 1
268 03/01/2001 From 2<<<<
268 05/01/2001 To 2
374 08/01/2001 To 1
374 10/01/2001 From 1
968 12/01/2001 To 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

In the course of our business process, we have to draft some documents and
send them to another department (DJ) for advice. I have a table called

[quoted text clipped - 21 lines]

I have no idea how to make a query for the said purpose. Grateful for advice.
Many thanks