Macro/code to add records to fields

[bg_bb]

I have a table that contains duplicate ID’s for those who have more than one telephone number. How can I write a macro or a module that would take each phone number and add it to a column (Phone Number 1, Phone Number 2) so that I will only have 1 record for each ID.

How it is currently

ID	Phone Number
1	111-111-1111
2	222-222-2222
2	222-000-0000
3	333-333-3333
4	444-444-4444
4	444-000-0000

How i want it to be

ID	Phone Number 1	Phone Number 2
1	111-111-1111
2	222-222-2222	222-000-0000
3	333-333-3333
4	444-444-4444	444-444-0000

[Bob Fitz]

I think you could do this with a crosstab query

[June7]

A CROSSTAB query would require a group record identifier. This can be calculated in a query with DCount() function and then use that query as source for CROSSTAB. Can be nested subquery.

TRANSFORM First(PhoneNumber) AS FirstOfPhoneNumber
SELECT ID
FROM (SELECT ID, PhoneNumber, DCount("*","TableName","ID=" & [ID] & " AND PhoneNumber<" & [PhoneNumber])+1 AS GrpSeq FROM TableName) AS Q1
GROUP BY ID
PIVOT GrpSeq;