Intransit List

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,
I'm trying to created report called Intransit list, report would look at the
each entry in table tblFileLoc and find out last entry for each case (cases
are entered multiple times as location of case file changes) and then check
if the case was sent or received and if last entry was sent then case would
show up on file intransit list report. Table tblFileLoc consists of:
CaseNo (text)
UserName (text)
ReceivedDeptCode (text)
ReceivedDate (date/time)
SentDeptCode (text)
IntransitCode (text)
SentDate (date/time)
When someone is receiving files they will choose ReceivedDeptCode first on
Receive screen and then scan via barcode scanner all cases that he/she is
receiving for that dept. ReceivedDate and Username is automatically filled in.
Same deal with Sending case to some dept. you will select SentDeptCode and
IntrasitCode and then wand in cases, SentDate and UserName will automatically
fill in.
Procedure is If I have a case file I need to receive it into the system and
if I'm sending case file to someone I need to go to send files screen and
send them to specific dept.
Now when i send file to Taxing for example sometimes they will forget to
receive them in and cases should show up on this intransit list.
I hope I'm clear enough.
Thanx
 
Evil,

How about something like the following. This will return the most recent
record for each case where the ReceivedDate is NULL. It will not, however,
identify records that were not properly signed out (no way to tell a case has
not been checked in if it wasn't checked out properly).

SELECT T.CaseNo, UserName, SentDate IntransitCode
FROM yourTable T
WHERE SentDate = (SELECT Max(NZ(SentDate, 0))
From yourTable
Where CaseNo = T.CaseNo
and ISNULL(ReceivedDate))

HTH
Dale
 
When I ran that query you sent
SELECT T.CaseNo, T.UserName, T.SentDate, T.IntransitCode
FROM tblFileLoc AS T
WHERE (((T.SentDate)=(SELECT Max(NZ(SentDate, 0))
From tblFileLoc
Where CaseNo = T.CaseNo
and ISNULL(ReceivedDate))));
It gave me a last time this case was sent to some dept. but will not filter
it out if I receive that same case at later time... that was my pain, If I
receive case after I send it then it shouldn't show up on this "in-transit
list".
So I guess you can look at it this way if case was received after it was
sent then it shouldn't show up on the list, meaning all the one's that are
sent but not received at later time will show up.
I also have field called "ID" set as an primary field that I have forget to
mention in first post.
 
yup that's a show stopper and bigest pain in behind darn intransit list..
Ok, we shall talk later
 
Back
Top