Combining two records into one

[merlynsdad]

I'm modifying a database for a call center. I have a make table query that uses calculated fields to determine the signon events and signoff events, from event types and time stamps. Each is a separate record. How do I combine the signon and signoff events so both appear in one record? I know I could do it in a report, but they want to combine signon and signoff in the table to save space in the db.

If the square peg won't fit in the round hole, sand off the corners.

 

[PHV]

I have a make table query
Any chance you could post the SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[merlynsdad]

"SELECT tblTemp_Event.SEID, tblTemp_Event.date_stamp, tblTemp_Event.time_stamp, tblTemp_Event.event_type, " _
            & "IIf((tblTemp_Event!event_type=2),[time_stamp],Null) AS signon, " _
            & "IIf((tblTemp_Event!event_type=4),[time_stamp],Null) AS Idle, " _
            & "tblTemp_Event.duration, " _
            & "IIf((tblTemp_Event!event_type=3),[time_stamp],Null) AS signoff, " _
            & "(IIf((tblTemp_Event!event_type=3),[time_stamp],Null))-(IIf((tblTemp_Event!event_type=2),[time_stamp],Null)) AS ReadyTime, " _
            & "Sum([ReadyTime]) AS TotalRdyTm " _
            & "IIf(([tblTemp_Event]![event_type]=4) AND SEID = ('" & strSEID & "'),Dsum[duration],null) AS TotalIdle " _
            & "((IIf((tblTemp_Event!event_type=3),[time_stamp],Null))-(IIf((tblTemp_Event!event_type=2),[time_stamp],Null)))-(IIf((tblTemp_Event!event_type=4),[time_stamp],Null)) AS Available " _
            & "INTO tblSEIDtoExt_Num " _
            & "FROM tblTemp_Event INNER JOIN TEMP_AGENT ON (tblTemp_Event.SEID = TEMP_AGENT.unum) AND (tblTemp_Event.agrp = TEMP_AGENT.agrp) " _
            & "GROUP BY tblTemp_Event.SEID, tblTemp_Event.date_stamp, tblTemp_Event.time_stamp, tblTemp_Event.event_type, tblTemp_Event.duration, " _
            & "(IIf((tblTemp_Event!event_type=3),[time_stamp],Null))-(IIf((tblTemp_Event!event_type=2),[time_stamp],Null)) " _
            & "WHERE (((tblTemp_Event.SEID) = 'BHRBB', 'ZN2KB') And ((tblTemp_Event.date_stamp) = #9/26/2011#)) " _
            & "ORDER BY tblTemp_Event.SEID DESC , tblTemp_Event.time_stamp, tblTemp_Event.event_type DESC;"

If the square peg won't fit in the round hole, sand off the corners.

 

[TheAceMan1]

To All . . .

The SQL for viewing only:

[blue]"SELECT SEID, " & _
       "date_stamp, " & _
       "time_stamp, " & _
       "event_type, " _

       "IIf((event_type=2),[time_stamp],Null) AS signon, " & _

       "IIf((event_type=4),[time_stamp],Null) AS Idle, " & _

       "duration, " & _

       "IIf((event_type=3),[time_stamp],Null) AS signoff, " & _

       "(IIf((event_type=3),[time_stamp],Null))-" & _
       "(IIf((event_type=2),[time_stamp],Null)) AS ReadyTime, " & _

       "Sum([ReadyTime]) AS TotalRdyTm " & _

       "SEID = ('" & strSEID & "'),Dsum[duration],null) AS TotalIdle " & _

       "((IIf((event_type=3),[time_stamp],Null))-" & _
        "(IIf((event_type=2),[time_stamp],Null)))-" & _
        "(IIf((event_type=4),[time_stamp],Null)) AS Available " & _

"INTO tblSEIDtoExt_Num " & _
"FROM tblTemp_Event" & _
"INNER JOIN TEMP_AGENT ON (SEID = TEMP_AGENT.unum) AND (agrp = TEMP_AGENT.agrp) " & _

"GROUP BY SEID, date_stamp, time_stamp, event_type, duration, " & _
     "(IIf((event_type=3),[time_stamp],Null))-" & _
     "(IIf((event_type=2),[time_stamp],Null)) " & _

"WHERE (((SEID) = 'BHRBB', 'ZN2KB') And ((date_stamp) = #9/26/2011#)) " & _
"ORDER BY SEID DESC , time_stamp, event_type DESC;"[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

merlynsdad . . .

I query your [blue]WHERE[/blue] clause:

[blue]"WHERE ([purple][b]((SEID) = 'BHRBB', 'ZN2KB')[/b][/purple] And ((date_stamp) = #9/26/2011#))[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[merlynsdad]

Because when run this brings up several thousand SEIDs and a full two weeks worth of data, which is close to 100k records, I'm testing it using 2 people and one date. Tests a lot quicker that way.

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

This problem has gone away for the time being. Thanks for your input.

If the square peg won't fit in the round hole, sand off the corners.