Combinations

[mac_see]

Following is a macro based solution form Myrna Larson (Microsoft MVP) on
permutation and combinations that works on EXCEL VBA. (**I am looking for a
similar result in ACCESS VBA**).

1. It allows Combinations or Permutations. (**I am looking only for
combinations**)
2. The macro handles numbers, text strings, words (e.g. names of people) or
symbols.
3. The combinations are written to a new sheet. (**I want the combination to
be written in a table with just one field**)
4. Results are returned almost instantaneously.

Setup:
In sheet1:
Cell A1, put “C†(Combinations) or “P†(Permutations). - (**in my case "C"**)
Cell A2, put the number of items in the subset – (**in my case it’s 7**)
Cells A3 down, your list. - (**in my case names of 21 employees**)

My question is:
================

What is a better solution? Modify the code in EXCEL so that the result is
transferred to an ACCESS table.

OR

Write a new code in ACCESS VBA

I am no expert in this. Can any body modify this code to get a similar
result in Access.

I can do this in excel but it puts 65536 names in Column A and the remaining
50744 in Column B as there are 116280 combination (Excel formula
=COMBIN(21,7)

I want everything in one column and thought of asking this question in
Access forum.

Can anybody help?

Maxi
====

CODE:

Option Explicit

Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
'
' Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc

Sub ListPermutationsOrCombinations()
Dim Rng As Range
Dim PopSize As Integer
Dim SetSize As Integer
Dim Which As String
Dim n As Double
Const BufferSize As Long = 4096

Worksheets("Sheet1").Range("A1").Select
Set Rng = Selection.Columns(1).Cells
If Rng.Cells.Count = 1 Then
Set Rng = Range(Rng, Rng.End(xlDown))
End If

PopSize = Rng.Cells.Count - 2
If PopSize < 2 Then GoTo DataError

SetSize = Rng.Cells(2).Value
If SetSize > PopSize Then GoTo DataError

Which = UCase$(Rng.Cells(1).Value)
Select Case Which
Case "C"
n = Application.WorksheetFunction.Combin(PopSize, SetSize)
Case "P"
n = Application.WorksheetFunction.Permut(PopSize, SetSize)
Case Else
GoTo DataError
End Select
If n > Cells.Count Then GoTo DataError

Application.ScreenUpdating = False

Set Results = Worksheets.Add

vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
ReDim Buffer(1 To BufferSize) As String
BufferPtr = 0

If Which = "C" Then
AddCombination PopSize, SetSize
Else
AddPermutation PopSize, SetSize
End If
vAllItems = 0

Application.ScreenUpdating = True
Exit Sub

DataError:
If n = 0 Then
Which = "Enter your data in a vertical range of at least 4 cells." _
& String$(2, 10) _
& "Top cell must contain the letter C or P, 2nd cell is the Number" _
& "of items in a subset, the cells below are the values from Which" _
& "the subset is to be chosen."

Else
Which = "This requires " & Format$(n, "#,##0") & _
" cells, more than are available on the worksheet!"
End If
MsgBox Which, vbOKOnly, "DATA ERROR"
Exit Sub
End Sub

Private Sub AddPermutation(Optional PopSize As Integer = 0, _
Optional SetSize As Integer = 0, _
Optional NextMember As Integer = 0)

Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Static Used() As Integer
Dim i As Integer

If PopSize <> 0 Then
iPopSize = PopSize
iSetSize = SetSize
ReDim SetMembers(1 To iSetSize) As Integer
ReDim Used(1 To iPopSize) As Integer
NextMember = 1
End If

For i = 1 To iPopSize
If Used(i) = 0 Then
SetMembers(NextMember) = i
If NextMember <> iSetSize Then
Used(i) = True
AddPermutation , , NextMember + 1
Used(i) = False
Else
SavePermutation SetMembers()
End If
End If
Next i

If NextMember = 1 Then
SavePermutation SetMembers(), True
Erase SetMembers
Erase Used
End If

End Sub 'AddPermutation

Private Sub AddCombination(Optional PopSize As Integer = 0, _
Optional SetSize As Integer = 0, _
Optional NextMember As Integer = 0, _
Optional NextItem As Integer = 0)

Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Dim i As Integer

If PopSize <> 0 Then
iPopSize = PopSize
iSetSize = SetSize
ReDim SetMembers(1 To iSetSize) As Integer
NextMember = 1
NextItem = 1
End If

For i = NextItem To iPopSize
SetMembers(NextMember) = i
If NextMember <> iSetSize Then
AddCombination , , NextMember + 1, i + 1
Else
SavePermutation SetMembers()
End If
Next i

If NextMember = 1 Then
SavePermutation SetMembers(), True
Erase SetMembers
End If

End Sub 'AddCombination

Private Sub SavePermutation(ItemsChosen() As Integer, _
Optional FlushBuffer As Boolean = False)

Dim i As Integer, sValue As String
Static RowNum As Long, ColNum As Long

If RowNum = 0 Then RowNum = 1
If ColNum = 0 Then ColNum = 1

If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
If BufferPtr > 0 Then
If (RowNum + BufferPtr - 1) > Rows.Count Then
RowNum = 1
ColNum = ColNum + 1
If ColNum > 256 Then Exit Sub
End If

Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
= Application.WorksheetFunction.Transpose(Buffer())
RowNum = RowNum + BufferPtr
End If

BufferPtr = 0
If FlushBuffer = True Then
Erase Buffer
RowNum = 0
ColNum = 0
Exit Sub
Else
ReDim Buffer(1 To UBound(Buffer))
End If

End If

'construct the next set
For i = 1 To UBound(ItemsChosen)
sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
Next i

'and save it in the buffer
BufferPtr = BufferPtr + 1
Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub 'SavePermutation

 

[John Vinson]

Following is a macro based solution form Myrna Larson (Microsoft MVP) on
permutation and combinations that works on EXCEL VBA. (**I am looking for a
similar result in ACCESS VBA**).

1. It allows Combinations or Permutations. (**I am looking only for
combinations**)
2. The macro handles numbers, text strings, words (e.g. names of people) or
symbols.
3. The combinations are written to a new sheet. (**I want the combination to
be written in a table with just one field**)
4. Results are returned almost instantaneously.

As it happens, combinations and permutations are duck soup to Access.
You don't need any code AT ALL. All you need is a cartesian join
Query!

If you want all possible combinations of the 21 employees in groups of
seven, create a Query by adding the Employee table to the query grid
seven times. Select whichever fields you want to see - let's say that
you just have a EmpName field. Access will alias the table names by
appending a number - i.e. you'll have Employees, Employees1,
Employees2 etc.

As a criterion under Employees1.EmpName put

<> [Employees].[EmpName])

Under Employees2.EmpName put

<> [Employees].[EmpName] AND <> [Employees1].[EmpName]

and so on through Employees6:

<> [Employees].[EmpName] AND <> [Employees1].[EmpName] AND <>
[Employees2].[EmpName] AND <> [Employees3].[EmpName] AND <>
[Employees4].[EmpName] AND <> [Employees5].[EmpName]

Open this query datasheet and you'll see all the combinations; or,
change it to a MakeTable query to write all the combinations to a
large table.

John W. Vinson[MVP]

 

[mac_see]

That was a terrific fix. This is what I tried:

I made a table "Employees" with only field named "EmpName"
Added "Employees" table thrice to the query grid.
I came up with Employees, Employees_1 and Employees_2
Employees1.EmpName criteria = <> [Employees].[EmpName])
Employees2.EmpName criteria = <> [Employees].[EmpName] AND <>
[Employees_1].[EmpName]

It worked !!!.

But it gave me all permutations (60 records) whereas I just need
combinations which should be 2

Any ideas???

 

[mac_see]

Sorry the combinations would be 10 and not 2.

Can you change the criteria to get combinations instead of permutations?

That was a terrific fix. This is what I tried:

I made a table "Employees" with only field named "EmpName"
Added "Employees" table thrice to the query grid.
I came up with Employees, Employees_1 and Employees_2
Employees1.EmpName criteria = <> [Employees].[EmpName])
Employees2.EmpName criteria = <> [Employees].[EmpName] AND <>
[Employees_1].[EmpName]

It worked !!!.

But it gave me all permutations (60 records) whereas I just need
combinations which should be 2

Any ideas???

On Fri, 4 Feb 2005 13:15:04 -0800, "mac_see"

As it happens, combinations and permutations are duck soup to Access.
You don't need any code AT ALL. All you need is a cartesian join
Query!

If you want all possible combinations of the 21 employees in groups of
seven, create a Query by adding the Employee table to the query grid
seven times. Select whichever fields you want to see - let's say that
you just have a EmpName field. Access will alias the table names by
appending a number - i.e. you'll have Employees, Employees1,
Employees2 etc.

As a criterion under Employees1.EmpName put

<> [Employees].[EmpName])

Under Employees2.EmpName put

<> [Employees].[EmpName] AND <> [Employees1].[EmpName]

and so on through Employees6:

<> [Employees].[EmpName] AND <> [Employees1].[EmpName] AND <>
[Employees2].[EmpName] AND <> [Employees3].[EmpName] AND <>
[Employees4].[EmpName] AND <> [Employees5].[EmpName]

Open this query datasheet and you'll see all the combinations; or,
change it to a MakeTable query to write all the combinations to a
large table.

John W. Vinson[MVP]

 

[John Vinson]

Sorry the combinations would be 10 and not 2.

Can you change the criteria to get combinations instead of permutations?

Sorry! My mistake. Change all the <> to >, and have each instance
check only the immediate preceding instance - that is,

Employees_1.FullName > Employees.FullName
AND Employees_2.FullName > Employees_1.FullName
AND Employees_3.FullName > Employees_2.FullName
<etc>


John W. Vinson[MVP]

 

[mac_see]

Issue resolved !!!

One more question.

Instead of getting a result something like:

Field1 Field2 Field3
Tom Tim Ron
Tom Tim Mac

Can we get something like:

Tom,Tim,Ron
Tom,Tim,Mac

All in one field seperated by a comma?

 

[John Vinson]

Issue resolved !!!

One more question.

Instead of getting a result something like:

Field1 Field2 Field3
Tom Tim Ron
Tom Tim Mac

Can we get something like:

Tom,Tim,Ron
Tom,Tim,Mac

All in one field seperated by a comma?

Yep:

Employees.EmpName & ", " & Employees_1.EmpName & ", " &
Employees_2.EmpName & ", " & <etc>

as a calculated field (the only field) in the query.

John W. Vinson[MVP]

 

[mac_see]

I did not understand your last post. Please excuse me, I am a beginner in
Access.

Do you mean to say that I put the table only once in the query grid and then
wite the criteria as Employees.EmpName & ", " & Employees_1.EmpName & ", " &
Employees_2.EmpName & ", " & etc... ?

My structure will remain the same.

Employees table will have 5 names,

I want to create combinations of 3 employees which should fetch me a result
only in one row with all the three names seperated by comma.

Maxi

 

[John Vinson]

I did not understand your last post. Please excuse me, I am a beginner in
Access.

Do you mean to say that I put the table only once in the query grid and then
wite the criteria as Employees.EmpName & ", " & Employees_1.EmpName & ", " &
Employees_2.EmpName & ", " & etc... ?

No - sorry for not explaining!

Put that expression in a vacant Field cell in the query; and uncheck
the Show boxes under all the EmpName fields (at any rate, do so when
you've got it working to your satisfaction). This is a "calculated
field" generated by concatenating (using the & string-concatenation
operator) the three (or seven or however many) names returned by the
query.


John W. Vinson[MVP]

 

[mac_see]

I was in the process of making 10 cominations of 12 numbers.

In Table1, I have 12 numbers serially from 1 to 12 in Field1

I Added Table1 10 times in the query grid.

Following is the criteria in all the fields and one calculated field at the
end.

[Table1].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1] And >[Table1_7].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1] And >[Table1_7].[Field1] And >[Table1_8].[Field1]

=Table1.Field1&","&Table1_1.Field1&","&Table1_2.Field1&","&Table1_3.Field1&","&Table1_4.Field1&","&Table1_5.Field1&","&Table1_6.Field1&","&Table1_7.Field1&","&Table1_8.Field1&","&Table1_9.Field1

It gives me 66 combinations which is correct but I am not getting them in
the order which I want.

For instance the combinations are:

How it is How I want
Expr1
1,10,11,12,2,3,4,5,6,7 1,2,3,4,5,6,7,10,11,12
1,10,11,12,2,3,4,5,6,8 and so on......
1,10,11,12,2,3,4,5,7,8
1,10,11,12,2,3,4,6,7,8
1,10,11,12,2,3,5,6,7,8
1,10,11,12,2,4,5,6,7,8
1,10,11,2,3,4,5,6,7,8
1,10,12,2,3,4,5,6,7,8
1,11,12,2,3,4,5,6,7,8
10,11,12,2,3,4,5,6,7,8
1,10,11,12,3,4,5,6,7,8
1,10,11,12,2,3,4,5,6,9

It should be sorted according to the numbers in Table1

Maxi

 

[mac_see]

My Field type is Text and Field size is 2 (I don't know if that matters)

The Excel VBA code that I posted in my first post works fine as I want.

Help please...

I was in the process of making 10 cominations of 12 numbers.

In Table1, I have 12 numbers serially from 1 to 12 in Field1

I Added Table1 10 times in the query grid.

Following is the criteria in all the fields and one calculated field at the
end.

[Table1].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1] And >[Table1_7].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1] And >[Table1_7].[Field1] And >[Table1_8].[Field1]

=Table1.Field1&","&Table1_1.Field1&","&Table1_2.Field1&","&Table1_3.Field1&","&Table1_4.Field1&","&Table1_5.Field1&","&Table1_6.Field1&","&Table1_7.Field1&","&Table1_8.Field1&","&Table1_9.Field1

It gives me 66 combinations which is correct but I am not getting them in
the order which I want.

For instance the combinations are:

How it is How I want
Expr1
1,10,11,12,2,3,4,5,6,7 1,2,3,4,5,6,7,10,11,12
1,10,11,12,2,3,4,5,6,8 and so on......
1,10,11,12,2,3,4,5,7,8
1,10,11,12,2,3,4,6,7,8
1,10,11,12,2,3,5,6,7,8
1,10,11,12,2,4,5,6,7,8
1,10,11,2,3,4,5,6,7,8
1,10,12,2,3,4,5,6,7,8
1,11,12,2,3,4,5,6,7,8
10,11,12,2,3,4,5,6,7,8
1,10,11,12,3,4,5,6,7,8
1,10,11,12,2,3,4,5,6,9

It should be sorted according to the numbers in Table1

Maxi

No - sorry for not explaining!

Put that expression in a vacant Field cell in the query; and uncheck
the Show boxes under all the EmpName fields (at any rate, do so when
you've got it working to your satisfaction). This is a "calculated
field" generated by concatenating (using the & string-concatenation
operator) the three (or seven or however many) names returned by the
query.


John W. Vinson[MVP]

 

[mac_see]

I changed the type to Byte and it is working now.

One more question.

If we make a "Make-Table" query, can we specify what data type that we want?

For instance, in my case, I want the data type to be "Byte". I think by
default it takes Long Integer as the data type.

Maxi

My Field type is Text and Field size is 2 (I don't know if that matters)

The Excel VBA code that I posted in my first post works fine as I want.

Help please...

I was in the process of making 10 cominations of 12 numbers.

In Table1, I have 12 numbers serially from 1 to 12 in Field1

I Added Table1 10 times in the query grid.

Following is the criteria in all the fields and one calculated field at the
end.

[Table1].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1] And >[Table1_7].[Field1]

[Table1].[Field1] And >[Table1_1].[Field1] And >[Table1_2].[Field1] And >[Table1_3].[Field1] And >[Table1_4].[Field1] And >[Table1_5].[Field1] And >[Table1_6].[Field1] And >[Table1_7].[Field1] And >[Table1_8].[Field1]

=Table1.Field1&","&Table1_1.Field1&","&Table1_2.Field1&","&Table1_3.Field1&","&Table1_4.Field1&","&Table1_5.Field1&","&Table1_6.Field1&","&Table1_7.Field1&","&Table1_8.Field1&","&Table1_9.Field1

It gives me 66 combinations which is correct but I am not getting them in
the order which I want.

For instance the combinations are:

How it is How I want
Expr1
1,10,11,12,2,3,4,5,6,7 1,2,3,4,5,6,7,10,11,12
1,10,11,12,2,3,4,5,6,8 and so on......
1,10,11,12,2,3,4,5,7,8
1,10,11,12,2,3,4,6,7,8
1,10,11,12,2,3,5,6,7,8
1,10,11,12,2,4,5,6,7,8
1,10,11,2,3,4,5,6,7,8
1,10,12,2,3,4,5,6,7,8
1,11,12,2,3,4,5,6,7,8
10,11,12,2,3,4,5,6,7,8
1,10,11,12,3,4,5,6,7,8
1,10,11,12,2,3,4,5,6,9

It should be sorted according to the numbers in Table1

Maxi

On Sat, 5 Feb 2005 20:53:01 -0800, "mac_see"

I did not understand your last post. Please excuse me, I am a beginner in
Access.

Do you mean to say that I put the table only once in the query grid and then
wite the criteria as Employees.EmpName & ", " & Employees_1.EmpName & ", " &
Employees_2.EmpName & ", " & etc... ?

No - sorry for not explaining!

Put that expression in a vacant Field cell in the query; and uncheck
the Show boxes under all the EmpName fields (at any rate, do so when
you've got it working to your satisfaction). This is a "calculated
field" generated by concatenating (using the & string-concatenation
operator) the three (or seven or however many) names returned by the
query.


John W. Vinson[MVP]

 

[John Vinson]

My Field type is Text and Field size is 2 (I don't know if that matters)

Certainly it matters. Text fields sort alphabetically: the text string
"2" sorts after the text string "10" because "2" sorts after "1", and
it's sorting them AS STRINGS.

You can get the data sorted (though it's going to slow this down
markedly) by including a calculated field

SortKey1: Val([Table1].[Field1])

in the query and setting its Sort to Ascending. If needed, do the same
with Table1_1.Field1, Table_2.Field1 and so on. Uncheck the Show
checkbox on these fields so they won't be included in the output, just
used for sorting.

The Excel VBA code that I posted in my first post works fine as I want.

Any reason not to use it then?



John W. Vinson[MVP]

 

[mac_see]

The only reason why I am not using is it is in Excel and it has a limit of
65536 records per row.

Thank you John, for all your help with this issue. It was really helpful

Maxi

My Field type is Text and Field size is 2 (I don't know if that matters)

Certainly it matters. Text fields sort alphabetically: the text string
"2" sorts after the text string "10" because "2" sorts after "1", and
it's sorting them AS STRINGS.

You can get the data sorted (though it's going to slow this down
markedly) by including a calculated field

SortKey1: Val([Table1].[Field1])

in the query and setting its Sort to Ascending. If needed, do the same
with Table1_1.Field1, Table_2.Field1 and so on. Uncheck the Show
checkbox on these fields so they won't be included in the output, just
used for sorting.

The Excel VBA code that I posted in my first post works fine as I want.

Any reason not to use it then?



John W. Vinson[MVP]

 

[mac_see]

One more question John if you don't mind.

Before you helped me with this query, I was doing the same task in a very
difficult way.

First I was calculating combinations in Excel using the VBA
Then exporting to excel.

I have now so many combinations in an Access database that I need to separate.

Example

I have a table with one field "Field1" and lacks of records.

Every record is a combination of 10 numbers separated by a a comma and then
a space (1, 2, 5, 8, and so on)

Can we write a query or VBA code to separate all the ten numbers in 10
different fields? Every field type should be of "Byte" data type.

Maxi

The only reason why I am not using is it is in Excel and it has a limit of
65536 records per row.

Thank you John, for all your help with this issue. It was really helpful

Maxi

My Field type is Text and Field size is 2 (I don't know if that matters)

Certainly it matters. Text fields sort alphabetically: the text string
"2" sorts after the text string "10" because "2" sorts after "1", and
it's sorting them AS STRINGS.

You can get the data sorted (though it's going to slow this down
markedly) by including a calculated field

SortKey1: Val([Table1].[Field1])

in the query and setting its Sort to Ascending. If needed, do the same
with Table1_1.Field1, Table_2.Field1 and so on. Uncheck the Show
checkbox on these fields so they won't be included in the output, just
used for sorting.

The Excel VBA code that I posted in my first post works fine as I want.

Any reason not to use it then?



John W. Vinson[MVP]

 

[John Vinson]

Can we write a query or VBA code to separate all the ten numbers in 10
different fields? Every field type should be of "Byte" data type.

Well, don't combine them in the first place!!!

Go back through this thread. My first suggestion was to do just that -
give you ten fields. You didn't want that, and wanted to get a comma
separated string instead; I revised the query using the concatenation
operator to satisfy that request.

To quote from your previous message:

====
Instead of getting a result something like:

Field1 Field2 Field3
Tom Tim Ron
Tom Tim Mac

Can we get something like:

Tom,Tim,Ron
Tom,Tim,Mac

All in one field seperated by a comma?
====

Just get rid of the calculated field with all the & concatenators and
use the ten individual fields to start with.

John W. Vinson[MVP]

 

[mac_see]

Yes John, I agree with your views with not to combine them in the first place
but as I said, before you helped me with this query, I was doing the same
task in a very difficult way. First I was calculating combinations in Excel
using the VBA Then exporting to excel. So I have now so many concatenated
combinations in an Access database that I need to separate.

Is there any way to do this?

 

[mac_see]

Hi! John,

Your query works in design mode but when I write a VBA for executing the
same thing in loop, it does not work. Probably therez something wrong in my
code. Can you please check?

In Table1, there are 5 fields Field1,Field2,Field3,Field4,Field5
Every field has 21 records and I want to create all possible combinations of
21 records of Field1 and put it in table "1" using Make Query and continue
the same thing till Field5

So that I have Five tables created at the end 1,2,3,4,5 and each talbe will
have all possible combinations of 21 recrods from each field.

Here is the code

Sub MakeTable()
Dim I As Integer
Dim strSQL As String
For I = 1 To 5
strSQL = “SELECT Table1.Field35 & "," & Table1_1.Field†& _
“35 & "," & Table1_2.Field35 & "," & Table1_3.Field†& _
“35 & "," & Table1_4.Field35 & "," & Table1_5.Field†& _
“35 & "," & Table1_6.Field35 & "," & Table1_7.Field†& _
“35 & "," & Table1_8.Field35 & "," & Table1_9.Field†& _
“35 AS Expr1, Table1.Field35+Table1_1.Field35+Table1_2.Field†& _
“35+Table1_3.Field35+Table1_4.Field35+Table1_5.Field†& _
“35+Table1_6.Field35+Table1_7.Field35+Table1_8.Field†& _
“35+Table1_9.Field35 AS Expr2 INTO†& I & _
“ FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1†& _
“ AS Table1_3, Table1 AS Table1_4, Table1 AS Table1_5, Table1 AS†& _
“ Table1_6, Table1 AS Table1_7, Table1 AS Table1_8, Table1 AS Table1_9†& _
“ WHERE (((Table1_1.Field35)>[Table1].[Field35])†& _
“ AND ((Table1_2.Field35)>[Table1].[Field35]†& _
“ And (Table1_2.Field35)>[Table1_1].[Field35])†& _
“ AND ((Table1_3.Field35)>[Table1].[Field35]†& _
“ And (Table1_3.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_3.Field35)>[Table1_2].[Field35])†& _
“ AND ((Table1_4.Field35)>[Table1].[Field35]†& _
“ And (Table1_4.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_4.Field35)>[Table1_2].[Field35]†& _
“ And (Table1_4.Field35)>[Table1_3].[Field35])†& _
“ AND ((Table1_5.Field35)>[Table1].[Field35]†& _
“ And (Table1_5.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_5.Field35)>[Table1_2].[Field35]†& _
“ And (Table1_5.Field35)>[Table1_3].[Field35]†& _
“ And (Table1_5.Field35)>[Table1_4].[Field35])†& _
“ AND ((Table1_6.Field35)>[Table1].[Field35]†& _
“ And (Table1_6.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_6.Field35)>[Table1_2].[Field35]†& _
“ And (Table1_6.Field35)>[Table1_3].[Field35]†& _
“ And (Table1_6.Field35)>[Table1_4].[Field35]†& _
“ And (Table1_6.Field35)>[Table1_5].[Field35])†& _
“ AND ((Table1_7.Field35)>[Table1].[Field35]†& _
“ And (Table1_7.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_7.Field35)>[Table1_2].[Field35]†& _
“ And (Table1_7.Field35)>[Table1_3].[Field35]†& _
“ And (Table1_7.Field35)>[Table1_4].[Field35]†& _
“ And (Table1_7.Field35)>[Table1_5].[Field35]†& _
“ And (Table1_7.Field35)>[Table1_6].[Field35])†& _
“ AND ((Table1_8.Field35)>[Table1].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_2].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_3].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_4].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_5].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_6].[Field35]†& _
“ And (Table1_8.Field35)>[Table1_7].[Field35])†& _
“ AND ((Table1_9.Field35)>[Table1].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_1].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_2].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_3].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_4].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_5].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_6].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_7].[Field35]†& _
“ And (Table1_9.Field35)>[Table1_8].[Field35]));â€
DoCmd.RunSQL (strSQL)
Next I
End Sub

Maxi

 

[John Vinson]

Can you please check?

sorry... not for at least two weeks. Just zipping through the groups
tonight, and off on a crosscountry trip.

Please repost in a new thread to see if someone else can jump in.

John W. Vinson[MVP]