make several records into one ?

[stonehead]

Hi,
Exemple of my current data:
Account Date_Time Queue
123 2006-03-02 13:28:14.577 I
123 2006-07-13 15:03:33.000 ASmith
123 2006-07-13 16:03:33.000 P
123 2006-07-13 15:03:33.000 BClinton

What I need to do is to make those same account# into one record with fields like

Acct Informal_Date_Time InFomal_Agent_Name Formal_Date_time Formal_Agent_Name
123 2006-07-13 15:03:33.000 ASmith 2006-07-13 15:03:33.000 BClinton



It will ignore records with "queue" value less than 2 characters (in order words, it only pick up records with valid name only) and the record with earlier date_time will be informal_date_time/informal_agent_name, the other (most recent date_time) will be formal.

I am not familiar with SQL that much so this is a bit complicated for me. Thanks in advance for your help.

 

[Crowley16]

use a self join...

select
a.acct,
a.date_time,
a.queue,
b.date_time,
b.queue
from
tblName a inner join tblName b on a.acct = b.acct
where
len(queue) > 2


--------------------
Procrastinate Now!

 

[stonehead]

I tried your code, got some error, so I modified it a little bit to

select
a.acct,
min(a.date_time) as Informal,
a.queue,
max(b.date_time) as Formal,
b.queue
from
tblName a inner join tblName b on a.acct = b.acct
where
len(queue) > 2
group by acct

by using group by, the error goes away but I can't get the min/max date_time. It just returns the same date_time for the two fields. Do you have any idea how to fix it ? Thanks a bunch.

 

[stonehead]

I'm sorry. Max/min(date) works fine. I picked the wrong field. My bad. Again thank you for your help.