Crosstab query

[bb_]

I am using a nested query in a crosstab query to essentially do this:



original table:

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

query:

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

SQL:
TRANSFORM First([Phone No]) AS Phone1
SELECT [ID]
FROM (Select [ID],[Phone No],DCount("*","Table","[ID] ='" & [ID] & "'
AND [Phone No] <'" & [Phone No] & "'")+1 AS Seq FROM LD) AS Query1
Group by [ID]
PIVOT Seq;


However I need all of the phone numbers to be transposed into the columns even if they are duplicated. (see ex with ID 2)

Any ideas?

[CJ_London]

you need an additional ID column (e.g. primary key) to differentiate these two records

ID	Phone Number

2	222-222-2222
2	222-222-2222

because this

[ID] ='" & [ID] & "' AND [Phone No] <'" & [Phone No] & "'"

will produce the same result for both records

[bb_]

okay thank u. where can i add the autoID field to the sql code

[CJ_London]

you don't add it to the code, it needs to be in the table

edit: and then referenced by the sql code

[bb_]

to the Dcount?

[CJ_London]

DCount("*","Table","[ID] ='" & [ID] & "' AND [PK] <=" & [PK])

edit: PK will be a number so treat PK as a number