Need help changing a row of results into a column

[sandstrm]

I have a table that I imported from another program. It gives columns listing the times that our call center logs in and out of the phone system. Another row lists if that time was a login or a logout. Ultimately, we want to form a report that shows how long each person was logged in each day. What I believe I need to do is create a column for login and another for logout and put the corresponding times in those columns to get my results. I can form a query to get the entire login times (or logout) but not to separate them both. If I do a pivot table (or crosstab query), it either wants to sum or count the numbers, but I don't want that either. I just want it to list them. Here are a couple rows of the table to see what I'm talking about.

ID Session ID Date Time Exit State Caller Name
1 1086736934 6/21/2004 9:02:34 AM LOGIN Person 1
2 1086736939 6/21/2004 9:07:09 AM LOGIN Person 2
3 1086736942 6/21/2004 9:07:16 AM LOGIN Person 3
4 1086736973 6/21/2004 9:51:36 AM LOGIN Person 4
5 1086736998 6/21/2004 10:27:01 AM LOGOUT Person 2
6 1086737110 6/21/2004 12:16:20 PM LOGOUT Person 3
7 1086737120 6/21/2004 12:26:52 PM LOGIN Person 3
8 1086737124 6/21/2004 12:28:28 PM LOGOUT Person 3

What I need are columns that read: Date, Time, Caller Name, Login, Logout

We thought maybe a "Nested If" statements, but I'm either not writing it correctly or not putting it in the correct place (more then likely it's both, coding is not my strong suit). So, if anyone can help, I'd be very appreciative.

 

[PHV]

Something like this ?
SELECT Format(S.Date+S.Time, "mm/dd/yyyy hh:nn:ss") As Login, S.[Caller Name]
, Format(Min(E.Date+E.Time), "mm/dd/yyyy hh:nn:ss") As Logout
FROM tblSessions S LEFT JOIN
(SELECT * FROM tblSessions WHERE [Exit State]='LOGOUT') E
ON (S.[Caller Name]=E.[Caller Name]) AND (S.ID<E.ID)
WHERE S.[Exit State]='LOGIN'
GROUP BY Format(S.Date+S.Time, "mm/dd/yyyy hh:nn:ss"), S.[Caller Name]
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[smandoli9]

Are you going to reconstitute the table? That is, are you happy to replace the imported table with a different design? If so, (1) dump your records into Excel, (2) sort and arrange until your target design is achieved, and (3) re-import the data into Access.

_______________________________
_{Never confuse movement with action -- E. Hemingway}
_______________________________