Pairing non-similar rows (1 Viewer)

[David_P]

I am trying to get a report to group 2 rows together so I can do some calculations on them. The report has several fields that group together to match up the rows but I can't figure out how to only select two rows. Below is a sample of what the report looks like.

Job_No  Object_No Task_No  Employee_No   Task    Employee  Date        Time   Status
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  11:47   IN
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  12:14   OUT
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  14:51   IN
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  15:34   OUT
 
J000153 P000001    T0030     E0005       SAMPLE  CHRISC     1/29/2010  12:45   IN
J000153 P000001    T0030     E0005       SAMPLE  CHRISC     1/29/2010  13:12   OUT
 
J014516 P000001    T0001     E0011       PREP    TAWNYA    11/23/2009  14:59   IN
J014516 P000001    T0001     E0011       PREP    TAWNYA    11/23/2009  15:03   OUT

What I need it to look like is like this:

Job_No  Object_No Task_No  Employee_No   Task    Employee  Date        Time   Status
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  11:47   IN
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  12:14   OUT
Time: 27 min
 
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  14:51   IN
J000153 P000001    T0030     E0005       SAMPLE  CHRISC    12/15/2009  15:34   OUT
Time: 43 min
 
J000153 P000001    T0030     E0005       SAMPLE  CHRISC     1/29/2010  12:45   IN
J000153 P000001    T0030     E0005       SAMPLE  CHRISC     1/29/2010  13:12   OUT
Time: 27 min
 
J014516 P000001    T0001     E0011       PREP    TAWNYA    11/23/2009  14:59   IN
J014516 P000001    T0001     E0011       PREP    TAWNYA    11/23/2009  15:03   OUT
Time: 4 min

So I am not sure what is the best way to go about getting the results I want.

Any suggestions or help would be greatly appreciated.

 

[vbaInet]

If in your records there's always going to be a pairing of IN/OUT AND it starts with an IN record, then getting the pairing wouldn't be much of a problem.

 

[gemma-the-husky]

there should be someway of including "OUT" in the report grouping. That will probably do it.

 

[David_P]

Each record should have an IN and Out paired. (Will have to create a report to determine if there is a missing out or in later).

Each record is a different line entry in the table so they are not paired up and are only paired after I run a query to pair them up.

So would it be easier to run a query to pair them up, place them in a table where the INs and OUTs are on the same line and then do the calculation in that table of the time difference. If so what would the code look like?

 

[David_P]

Combine Two Rows to One Row

I have a query table that I need to combine two rows of information to one row. This is what the query looks like:

Job_No.    Part_No.    Task_No.   Employee_No.   Task     Employee     Status     Date      In      Out
J015978     P000001     T0007       E0004        QA         TARA       IN     09/21/2010    11:33     
J015978     P000001     T0007       E0004        QA         TARA       OUT     09/21/2010           12:05
J016006     P005408     T0007       E0004        QA         TARA       IN     09/21/2010    13:40     
J016006     P005408     T0007       E0004        QA         TARA       OUT     09/21/2010           16:00
J016130     P000000     T0007       E0019        QA         COLEAH     IN     09/21/2010    15:54     
J016130     P000000     T0007       E0019        QA         COLEAH     OUT     09/21/2010           16:43
J016130     P001031     T0002       E0003       PAPERSCAN   SUE        IN     09/21/2010    13:11     
J016130     P001031     T0002       E0003       PAPERSCAN   SUE        OUT     09/21/2010           13:18
J016130     P001032     T0002       E0010       PAPERSCAN   DANIEL     IN     09/21/2010    11:13     
J016130     P001032     T0002       E0010       PAPERSCAN   DANIEL     OUT     09/21/2010           13:38

This is what I need the quety to look like:

Job_No.    Part_No.    Task_No.   Employee_No.   Task     Employee     Status     Date      In      Out
J015978     P000001     T0007       E0004        QA         TARA       IN     09/21/2010    11:33   12:05
J016006     P005408     T0007       E0004        QA         TARA       IN     09/21/2010    13:40   16:00
J016130     P000000     T0007       E0019        QA         COLEAH     IN     09/21/2010    15:54   16:43
J016130     P001031     T0002       E0003       PAPERSCAN   SUE        IN     09/21/2010    13:11   13:18
J016130     P001032     T0002       E0010       PAPERSCAN   DANIEL     IN     09/21/2010    11:13   13:38

Eash line is a different row in the tables I pull the query from. I have to pull from 3 different tables to get each row of information. I was thinking of combining the data into a new temp table to run reports and calculations from but can't figure out how to assign each to its proper cell in the new table with the IN and OUTs on the same row.

Here is the query code I use. Any help would be appreciated.

SELECT Left(dbo_Traceables.BarCode,7) AS Query_Job_Number, Right(dbo_Traceables.BarCode,7) AS Query_Part_Number, Left(dbo_Checkpoints.BarCode,5) AS Query_Task_Number, Mid(dbo_Checkpoints.BarCode,6,5) AS Query_Employee_Number, Left(dbo_Checkpoints.Name,InStr(1,dbo_Checkpoints.Name,",")-1) AS Query_Task, Mid([dbo_Checkpoints].[Name],InStr(1,[dbo_Checkpoints].[Name],",")+1,InStrRev([dbo_Checkpoints].[Name],",",-1)-InStr(1,[dbo_Checkpoints].[Name],",")-1) AS Query_Employee, Mid(dbo_Checkpoints.Name,InStrRev(dbo_Checkpoints.Name,",",-1)+1,6) AS Query_Status, dbo_Transactions.Timestamp AS Query_Date, IIf(Mid(dbo_Checkpoints.Name,InStrRev(dbo_Checkpoints.Name,",",-1)+1,6)="IN",Format([dbo_Transactions.Timestamp],"Short Time"),"") AS Query_In, IIf(Mid(dbo_Checkpoints.Name,InStrRev(dbo_Checkpoints.Name,",",-1)+1,6)="OUT",Format([dbo_Transactions.Timestamp],"Short Time"),"") AS Query_Out
FROM (dbo_Transactions INNER JOIN dbo_Traceables ON dbo_Transactions.TraceableID = dbo_Traceables.ID) INNER JOIN dbo_Checkpoints ON dbo_Transactions.CheckpointID = dbo_Checkpoints.ID
WHERE (((dbo_Transactions.Timestamp) Between #9/21/2010# And #9/22/2010#))
ORDER BY Left(dbo_Traceables.BarCode,7), Right(dbo_Traceables.BarCode,7), Left(dbo_Checkpoints.BarCode,5), Mid(dbo_Checkpoints.BarCode,6,5), dbo_Transactions.Timestamp;

 

[vbaInet]

Here's an idea. Hasn't been fully tested but it should work.

Place this in a module:

Option Compare Database
Option Explicit

Public prevVals As Variant, increment As Long

Public Function GetGrouping(status As String, ParamArray Vals()) As Variant
    Dim i As Long, notSame As Boolean

    If Not IsArray(prevVals) Then
        ReDim prevVals(UBound(Vals))
    End If
    
    For i = LBound(Vals) To UBound(Vals)
        If status = "out" Then
            If Vals(i) <> prevVals(i) Then
                notSame = True
            End If
        End If
        
        prevVals(i) = Vals(i)
    Next
    
    Select Case status
        Case "in"
            increment = increment + 1
            GetGrouping = increment
        Case "out"
            If notSame Then
                increment = increment + 1
                GetGrouping = increment
            Else
                GetGrouping = increment
                
                For i = LBound(prevVals) To UBound(prevVals)
                    prevVals(i) = Null
                Next
            End If
        Case Else
            GetGrouping = Null
    End Select
End Function

In the query that groups, call the function in the exact order as below:

Pair: GetGrouping([Status],[Job_No],[Object_No],[Task_No],[Employee_No],[Date])

In your report, you need to initialise the global variables in the Open event:

Private Sub Report_Open(Cancel As Integer)
    prevVals = Null
    increment = 0
End Sub

You should now be able to group by the field Pair. The time difference can be calculated in the Print event or Format event of the footer for Pair.

 

[vbaInet]

Re: Combine Two Rows to One Row

You would need to do two things:

1. Exclude the OUTs in Status i.e. WHERE Status = "In"
2. Use a subquery which would look something like:

Alias_Out: Nz([Out], (Select Q.Out From TableName AS Q Where  Q.Status = "Out" AND Q.Job_No = TableName.Job_No AND Q.Part_no =  TableName.PartNo AND Q.Task_No TableName.Task_No AND Q.Employee_No =  TableName.Employee_No))

By the way, have a look at your other thread.

I'm beginning to think that you need to change your table structure slightly. It's either you have an ID that distinctively distinguishes pairs (i.e. for In and Out) or create another field that will hold Out time (like in your first table above).

 

[DCrake]

Re: Combine Two Rows to One Row

Create a totals query that groups by the folowing fields

Job_No.
Part_No.
Task_No.
Employee_No.
Task
Employee
Date

Then add 2 columns

TimeIn:IIF(Status="IN",TimeField,"")
TimeOut:IIF(Status="OUT","TimeField,"")

Define these as expressions

Working Blind here but I think that's right

 

[DCrake]

Two threads merged by AWF

 

[vbaInet]

Two threads merged by AWF

Interestingly the threads titles also stayed the same. Nice!

 

[vbaInet]

David, can you move things about a bit? Like move #6 up a notch or bring #5 down 1 step?

 

[DCrake]

Cannot reorder the posts, sorry.

 

[vbaInet]

No worries. It would be a nice feature isn't it!?

 

[David_P]

Thanks for your help DCrake and vbaInet.

DCrake - I tried the way you suggested, and it gives me an error (see below). As you can see by the my code I posted ealier, I have to pull apart the information out of 3 tables with each table having the information I need combined so I have to tear it apart. I know it is bad set up but I have to work with the database I am given. Oh, and thanks for combining the two threads.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=33960&stc=1&d=1286307600

vbaInet - I have place the code in the module and in the report open event. I'm not sure where to put the code:
Code:
Pair: GetGrouping([Status],[Job_No],[Object_No],[Task_No],[Employee_No],[Date])

Is it suppose to take the place of the In & Out Expression?

 

[vbaInet]

As a field in the query, so you've done it right.

Under that field alone, change GROUP BY to EXPRESSION.