I am writing a report for the local sheriffs office here and I am trying to find a way to show evidence items that have been returned from FDLE agencies. Here is what a sample set of the table looks like.
I would do a join onto the item detail table to get more information about it but this is the only table that I need help in working this out:
Item number: Transaction Code: Transaction Date:
1 FDLE checkout 6/05/08
2 LAB checkout 6/05/08
1 CHECKIN 6/07/08
2 CHECKIN 6/07/08
1 DNA LAB 6/09/08
1 CHECKIN 06/10/08
Now what I need to do is to figure out what items have been checked in on a particular day that were last checked out to FDLE. For instance: If I was running the report on 6/07/08 I would see only Item 1 on my report since its last transaction was an FDLE checkout and it was checked in on the day that I had specified. Item 2 would not show up since it was not checked out to FDLE. Now if I run the report for 06/10/08, nothing should come up in my report since Item 1 was out to the DNA lab on its last transaction and not the FDLE. But how do I narrow that down?
This is where I have the trouble, I can narrow down which items were checked out to FDLE and I can narrow down which items were marked checked in and I could even do a left outer join from FDLE to the checkin result and if there was a field populated in the checkin side it would show me if the item was a. sent out to fdle and b. if it got checked in. However since items can have multiple transactions and could have been checked out on a different transaction yet still show a checkin date for the day that I am running the report, I do not know how to handle that. Any help is greatly appreciated.
Start Free Trial
