SQL: How can I make two value from two record to appear in one line.

[mackymacmac]

[tt]I have a table [LOG] with no pkey on it. Records inside are as follows:

[Name] [TimeStamp]
Juan 3/29/2008 7:50AM
Macky 3/29/2008 8:00AM
Juan 3/29/2008 9:00PM
Macky 3/29/2008 5:00PM

Is there a SQL command that would display the result like this...

[Name] [LogIn] [LogOut]
Juan 3/29/2008 7:50AM 3/29/2008 9:00PM
Macky 3/29/2008 8:00AM 3/29/2008 5:00PM

Thanks!
[/tt]

 

[KenReay]

Not sure if you have simplified the table content to explain what you want, but if there is a column to indicate in/out then a cross tab query may give you what you are looking for

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[mackymacmac]

[tt]Thanks KenReay for your response. Actually the table i've shown is the real table itself. One field for the [name] and the other one is the [timestamp]. I named the table [Log]. I want a query that would display the 1st [timestamp] occurence as LogIn and the 2nd or the last [timstamp] occurence as LogOut.

e.g.
Table: LOG
[Name] [TimeStamp]
Juan 3/29/2008 7:50AM
Macky 3/29/2008 8:00AM
Juan 3/29/2008 9:00PM
Macky 3/29/2008 5:00PM

Query: ReportLOG
[Name] [LogIn] [LogOut]
Juan 3/29/2008 7:50AM 3/29/2008 9:00PM
Macky 3/29/2008 8:00AM 3/29/2008 5:00PM

Is there a simplier way other than crosstab query?

Thanks Ken!
[/tt]

 

[HarleyQuinn]

See if this (

http://www.tek-tips.com/faqs.cfm?fid=4233)

points you in the right direction.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

A starting point (SQL code):

SELECT Name, Min(TimeStamp) AS LogIn, Max(TimeStamp) AS LogOut
FROM LOG
GROUP BY Name, Int(TimeStamp)

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