multi row to single row

[studentcp]

Hi every one,
I am a Access beginer, I have some questions to ask. Here is my question: I want to compare the id first, if the ids are the same pick the row with the earliest time, if id is unique then store. For example:
id Arrive Time
417 2006-01-06 7:50:56 PM
417 2006-01-06 8:15:17 PM
64 2006-01-07 1:10:51 AM
63 2006-01-07 1:15:00 AM
78 2006-01-10 2:41:34 AM
78 2006-01-10 3:03:28 AM
78 2006-01-10 1:39:44 PM

the result would be

417 2006-01-06 7:50:56 PM
64 2006-01-07 1:10:51 AM
63 2006-01-07 1:15:00 AM
78 2006-01-10 2:41:34 AM


Thank you very much.

 

[gmmastros]

Select Id, Min(ArriveTime)
From   Table
Group By Id

Since you admit to using Access, it would be best if you post in one of the Access forums.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Golom]

Select ID, MIN([Arrive Time]) As [EarlyTime]

From SomeTable

Group By ID

 

[studentcp]

It works. Thank you very much, George

I got another question:
I have a column with this kind of data: 2006-01-23 2:16:11 PM
but I only want the date which is 2006-01-23.
How could I do it? Thanks again for your help.

 

[studentcp]

Thanks for your help,Golom
You guys, have a nice day.

 

[gmmastros]

My best suggestion would be to format the data in your front end. Every front end I've ever used (VB, Access, ASP, etc...) has had some sort of date formatting functions.

VB has Format
Access and ASP have FormatDateTime

It is better to format the date on the front end so that you can use the regional settings for the user. Some countries use Month-Day-Year and others use Day-Month-Year. By formatting the date on the front end, you can use their regional settings.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[studentcp]

Did you mean that I need to change the format of that column? Since I got the database from a system, I cannot set the format. Sorry, I am a really new for Access. I am really apreciate your help.

 

[gmmastros]

No.

I was not suggesting that you change the format of the field in the database. Instead, I was suggesting that you alter the code you use to display the data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom