Creating a Grouping query

[Dev]

Hi everyone,
I was wondering if someone can help me out in creating a query or new table. I have 2 columns from the same table and one column has many of the same numbers but the other column has unique values. I want to group the repeating number and insert a new field containing all the unique numbers associated with the repeating one.
here is the sample:

Number1 Number2
0168 0165
0171 0165
0420 0165
0148 0168
0138 0168
0137 0168
0136 0168
0418 0419
0052 0419
etc.

here is what I would like the output to be

Number2 Repeating Numbers


0165 0168, 0171, 0420
0168 0148, 0138, 0137, 0136
0419 0418, 0052

[ajetrumpet]

what you want is better done in excel because you're trying to output horizontally. Access isn't designed for this, as it is designed for vertical output of data in the form of records. It is easier to work with in this sense anyway.

But ... if vertical with satisfy you, try this for a query:

 select distinct col1, col2

from table

where dcount("col2", "table") > 1

this will give something like:

0165 | 0168
0165 | 0171
0165 | 0420

this is extremely better than columns because the only way to get values out of columns is to use internal functions or loops in UDF'S. This way gives you the option to refer to one field only to get these values out when you need them.

[Dev]

Thanks for the input...
Any ideas on how I can get my desired output in excel then??
I would really like the repeating numbers to appear once with the list of all unique values beside it in the same row.

Thanks again,

Devo

[ajetrumpet]

I am not good with excel interfaces at all, but I can write the code. E.G. - if I have a table that is two columns and starts in col A (cells start in A1, duplicate col is col A, related nums are in col B), I write this to transform it:

Option Explicit
Option Base 1

Sub trans()

Dim i As Long
Dim r As Range
Dim r2 As Range
Dim curVal As String
Dim NewListPos As Long 'record num
Dim NewListCol As Long
Dim usedVals() As String

NewListPos = 0
NewListCol = 0

'loop col1 for distinct values and pull related col2
For Each r In Range("a1", Range("a1").End(xlDown))

i = 1
curVal = r

'if this is first num in list, resize array and move on
If r.Address = "$A$1" Then
   ReDim Preserve usedVals(1)
   usedVals(1) = r
      GoTo Midline
End If
   

   For i = LBound(usedVals) To UBound(usedVals)
      
      'have we used this num as a ref before?
      If curVal = usedVals(i) Then
         GoTo NextLoop
      End If
   
   Next i

'if new num, resize array
ReDim Preserve usedVals(UBound(usedVals) + 1)
usedVals(UBound(usedVals)) = curVal

Midline:

'if new num, start a new transformed row
NewListPos = NewListPos + 1
Range("D" & CStr(NewListPos)) = r

'set up for first piece of data
NewListCol = 1

      'if new num, pull all nums in col2
      For Each r2 In Range("a1", Range("a1").End(xlDown))
      
         If r2 = curVal Then
            Range("D" & CStr(NewListPos)).Offset(0, NewListCol) = r2.Offset(0, 1)
            NewListCol = NewListCol + 1
         End If
      
      Next r2

NextLoop:

Next r

End Sub

[Dev]

Thanks again for your solutions.

Devo