MS Access converting row information to column

Question

I've spent hours trying to figure this one out. This is for a manufacturing plant.

A person on the plant floor logs their activities on different machines using a computer system which produces data like this:

Empnbr  Datestamp   Shift   Machnbr Time
00090   6/30/2010   1   0354    11:10
00090   6/30/2010   1   S       12:00
00098   6/30/2010   1   0920    7:00
00098   6/30/2010   1   0143    7:30
00098   6/30/2010   1   S       15:00
00101   6/30/2010   1   0920    7:00
00101   6/30/2010   1   0247    7:30
00101   6/30/2010   1   0147    7:40
00101   6/30/2010   1   S       15:00
00107   6/30/2010   1   0585    7:00
00107   6/30/2010   1   0143    12:00
00107   6/30/2010   1   S       15:00
00109   6/30/2010   1   0920    7:00
00109   6/30/2010   1   0154    7:30
00109   6/30/2010   1   0154    9:00
00109   6/30/2010   1   0154    9:43
00109   6/30/2010   1   0254    10:49
00109   6/30/2010   1   0154    12:30
00109   6/30/2010   1   S       15:00
00111   6/30/2010   1   0591    5:00
00111   6/30/2010   1   S       17:00
00114   6/30/2010   2   0585    15:00
00114   6/30/2010   2   S       23:00

The report I'm trying to write needs the information in such a form

Empnbr      Datestamp      Shift      Time Start       Time End       Machine

This will allow me to calculate the amount of time spent on each machine per person per day. Basically, Access needs to see an employee number, datestamp, and shift and take the first time it sees. Then needs to take the next time it sees and attribute that time to the machine on the first entry. This continues to happen until Access comes across a machine "S" which means signout.

Now time to take some Advil for this headache -- thanks for your help!

Answer 1

I would use a temporary table. When you want to display the report use VBA code to go through your input table, build the report data, and populate the temporary table. Then display your report using the data in the temporary table. The function to populate the reporting table would look something like this:

Public Sub BuildReportTable()
    Dim prevEmp As Integer
    Dim prevDate As Date
    Dim prevTime As Date
    Dim prevMachine As String
    Dim prevShift As Integer
    Dim rs As Recordset

    'Empty report table
    CurrentDb.Execute "delete from tblHourReport"

    'Open the data table
    Set rs = CurrentDb.OpenRecordset("tblHours", dbOpenDynaset)
    If Not rs.EOF Then rs.MoveFirst

    'Loop over each data record and create the report record
    prevEmp = 0
    Do While Not rs.EOF
        If prevEmp <> 0 And prevMachine <> "S" Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL "insert into tblHourReport values (" _
                & prevEmp & ", '" _
                & prevDate & "', '" _
                & prevMachine & "', " _
                & prevShift & ", '" _
                & prevTime & "', '" _
                & rs!Time & "');"
            DoCmd.SetWarnings True
        End If
        prevDate = rs!Date
        prevShift = rs!shift
        prevEmp = rs!employee
        prevTime = rs!Time
        prevMachine = rs!machine
        rs.MoveNext
    Loop
End Sub

Answer 2

Is there any reason why a grouping query would not suit? For example:

SELECT Empnbr, Datestamp, Shift, Machnbr, 
       Min(CDate([datestamp] & " " & [Time])) AS TimeStart, 
       Max(CDate([datestamp] & " " & [Time])) AS TimeEnd
FROM MachineLog 
GROUP BY Empnbr, Datestamp, Shift, Machnbr

EDIT re comment

Perhaps:

SELECT m.Empnbr, m.Datestamp, m.Shift, m.Machnbr, 
     Nz((SELECT Max(CDate([datestamp] & " " & [Time])) 
         FROM MachineLog x WHERE x.Empnbr=m.Empnbr 
         AND x.Machnbr<m.Machnbr),CDate([datestamp] & " 08:00")) AS st, 
     Max(CDate([datestamp] & " " & [Time])) AS TimeEnd
FROM MachineLog m
GROUP BY m.Empnbr, m.Datestamp, m.Shift, m.Machnbr