Combine records in query

[Mary M]

We have a table that has multiple records for a contact with a code
indicating the contact type.

Example:

NAME CODE
john doe BROT
john doe EM1
john doe EM2
joe smith COUS
joe smith EM1

Is there a way to run a query that would combine the codes to one contact
line?

Example of Desired Output:

NAME CODE1 CODE2 CODE3
john doe BROT EM1 EM2
joe smith COUS EM1

Many thanks for any help that can be offered.

 

[Dale Fye]

Mary,

Start out by creating a query that will give you a field that contain the
code sequence (I'll assume you want alphabetical)

SELECT [Name], Code, (SELECT COUNT(*) FROM YourTable
WHERE yourTable.Name = T.Name
AND yourTable.Code <= T.Code) as Sequence
FROM yourTable T

Save this query, it should give you results like:

Name Code Sequence
john doe BROT 1
john doe EM1 2
john doe EM2 3
joe smith COUS 1
joe smith EM1 2

Now use this query in a new query. Add the Name, Sequence, and Code fields
to the grid, then make it a crosstab query. Make the Name column a Row
Heading, the Sequence column the Column Heading, and the in the Code column,
change GroupBy to First,and make it a Value.

HTH
Dale