I am working on a report for a imaging database. I am lost in terms on how to approach this.

 

Here is the ONLY table available from the database with data:

 

CustomerID | Name | TechID | TotalPages |         Action           | DateTime

       A01          John        ED             0            Request_Submit    08/30/2012
       A01          Jon          ED             4            Request_Submit    08/30/2012
       A01          Jon          ED             4            Process_1             08/30/2012
       A01          Jon          ED             4            Process_2             08/30/2012
       A01          Jon          ED             4            Process_Comp      08/30/2012
       A02          Dave       ED             4            Request_Submit    08/30/2012
       A02          Dave       ED             2            Process_1             08/30/2012
       A02          Dave       ED             2            Process_2             08/30/2012
       A02          Dave       ED             2            Process_Comp      08/30/2012

In this scenario, TechnicianID ED is scanning the following customer's documents. First, he submitted a request to scan John's documents. Then, he went back and had to change his name, due to typo, to Jon. He then have to submit another request which is followed by proccess and finally Process_Comp (Completed).

 

I would like my report to show that if Action=Request_Submit and followed by another Action=Request_Submit, display initial record. If Action=Request_Submit and followed by Process, show end result?

Example of what report would look like:

TechID:ED (Grouped by techID)

CustomerID | Name |  TotalPages |         Action           | DateTime

       A01          John             0            Request_Submit    08/30/2012
       A01          Jon               4            Process_Comp       08/30/2012

       A02          Dave            2            Process_Comp       08/30/2012

Is this possible?! It seems ridiculous how the DB doesn't have a transactionID field to uniquely group transactions.

 

Any help would be appreciated!

 

Thank you.

 

 

Thank you. I don't think the total pages is always set. Most of the time when you see a request_submit it'll be 0 pages and Process_1 will also be 0. That was probably a bad example, the one below is what most records are displaying.

 

CustomerID | Name | TechID | TotalPages |         Action           | DateTime

       A01          John        ED             0            Request_Submit    08/30/2012
       A01          Jon          ED             0            Request_Submit    08/30/2012
       A01          Jon          ED             0            Process_1             08/30/2012
       A01          Jon          ED             4            Process_2             08/30/2012
       A01          Jon          ED             4            Process_Comp      08/30/2012
       A02          Dave       ED             0            Request_Submit    08/30/2012
       A02          Dave       ED             0            Process_1             08/30/2012
       A02          Dave       ED             2            Process_2             08/30/2012
       A02          Dave       ED             2            Process_Comp      08/30/2012