Combining duplicate records

[Subs]

Beginner question: I receive data in CSVs, and import them into access 2007 to generate reports. For one of the tables, I need to be able to combine records with the same ticket number into a single line like below.

Current
0001 CI1
0001 CI2
0001 CI4
0002 CI2
0002 CI3
0003 CI4

Goal
0001 CI1, CI2, CI4
0002 CI2, CI3
0003 CI4

What is the easiest way to do this?

Thank you.

[ssanfu]

I hope this is for a report.
You shouldn't have the table in the "Goal" structure - leave the table in the "Current" structure. If you need to see it like the "Goal", use a query and base your form/report off of the query.

Allen Browne has an example and code at:
http://allenbrowne.com/func-concat.html

[Dal Jeanis]

Given this table layout for your imported data:

tblTickets
   Field Name  Field Type      example
   TickPK      Autokey    (assigned by system)
   TickID      Number     0001
   TickItem    Text       CI1

Something like this will give you your requested results:

Transform First(TickItem) as TheItem
SELECT TickID
FROM
  (SELECT T1.TickID, T1.TickItem, T1.TickPK,  Count(T2.TickPK) As MyCount
  FROM tblTickets as T1 INNER JOIN tblTickets AS T2
  ON T1.TickID = T2.TickID 
  WHERE T1.TickPK >= T2.TickPK
  GROUP BY T1.TickID, T1.TickITem, T1.TickPK)
GROUP BY TickID
PIVOT "Item" & MyCount;

[Subs]

Thank you both. The concat function was exactly what I was looking for, and the other solution will help with another issue I was trying to figure out.

[Cassim]

I managed to use it too...:-)