sql required- in

[lee]

i have current table in below format

zip range
1-2
2-5
3-8


i want to convert the above field - zip range into individual values
in the vertical fashion- for example
1-2 has 1 and 2 digits all written down one below another . same with
2-5 row- 2,3,4,5 should be written one below the other

result required

zip
1
2
2
3
4
5
3
4
5
6
7
8

is there a squl query which can convert the above format

 

[Allen Browne]

The missing numbers need to come from somewhere, so the easiest way is to
use a Cartesian product on a Counting table.

It will be easiest if you can change your existing table so it has 2 fields
of type number for the range. Let's call them ZipMin and ZipMax. (If you
can't you'll have to use Instr() to parse the 2 numbers at the dash, along
with Left() and Mid() to get the 2 numbers, and Val() to convert them to
true numbers. It would be *much* better to use 2 fields.)

1. Create a table with just one field name (say) CountID, of type Number.
Mark it as primary key.
Save the table.

2. Enter numbers from 0 to the highest number you could need. If that could
be high, you could use the code at the bottom of this webpage to enter the
records for you:
http://allenbrowne.com/ser-39.html

3. Create a query using both this counting table and your original table. In
the upper pane of table design, there must be no line joining the 2 tables.
(This gives you every possible combination, called a Cartesian product.)

4. Drag CountID from the counting table into the query design grid.
In the Criteria row under this field, enter:
Between [ZipMin] And [ZipMax]
This will limit the numbers to the correct range, giving you the output you
desire.

 

[Dr GUI]

Not sure that you can accomplish this in a single query but here is a possible solution you might try. Array aRange will contain the values you need.

Dim strSQL As String
Dim aRange() As Integer
Dim rs As New ADODB.Recordset
Dim aTemp() As String
Dim i As Integer
Dim j As Integer
Dim strRange As String

' table tbl_Zips has the following values in field [zip range]
' 1-2
' 2-5
' 3-8

strSQL = "select [zip range] from tbl_ranges"
rs.Open strSQL, CurrentProject.Connection

i = 1
If Not (rs.BOF And rs.EOF) Then
Do Until rs.EOF
aTemp = Split(rs("range"), "-")
For j = Int(aTemp(0)) To Int(aTemp(1))
ReDim Preserve aRange(i) As Integer
aRange(i) = j
strRange = strRange & aRange(i) & vbCrLf
i = i + 1
Next
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

' the messagebox will display the results you seek
MsgBox strRange

i have current table in below format

zip range
1-2
2-5
3-8


i want to convert the above field - zip range into individual values
in the vertical fashion- for example
1-2 has 1 and 2 digits all written down one below another . same with
2-5 row- 2,3,4,5 should be written one below the other

result required

zip
1
2
2
3
4
5
3
4
5
6
7
8

is there a squl query which can convert the above format

On Monday, June 28, 2010 6:31 AM Allen Browne wrote:
The missing numbers need to come from somewhere, so the easiest way is to
use a Cartesian product on a Counting table.

It will be easiest if you can change your existing table so it has 2 fields
of type number for the range. Let's call them ZipMin and ZipMax. (If you
cannot you will have to use Instr() to parse the 2 numbers at the dash, along
with Left() and Mid() to get the 2 numbers, and Val() to convert them to
true numbers. It would be *much* better to use 2 fields.)

1. Create a table with just one field name (say) CountID, of type Number.
Mark it as primary key.
Save the table.

2. Enter numbers from 0 to the highest number you could need. If that could
be high, you could use the code at the bottom of this webpage to enter the
records for you:
http://allenbrowne.com/ser-39.html

3. Create a query using both this counting table and your original table. In
the upper pane of table design, there must be no line joining the 2 tables.
(This gives you every possible combination, called a Cartesian product.)

4. Drag CountID from the counting table into the query design grid.
In the Criteria row under this field, enter:
Between [ZipMin] And [ZipMax]
This will limit the numbers to the correct range, giving you the output you
desire.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorial...w-excel-2010--the-missing-manual-oreilly.aspx

 

[Dr GUI]

I'm not sure you can accomplish this in a query but you can try the following. The array aRange will contain the values you need.

Dim strSQL As String
Dim aRange() As Integer
Dim rs As New ADODB.Recordset
Dim aTemp() As String
Dim i As Integer
Dim j As Integer
Dim strRange As String

' table tbl_Zips has the following values in field [zip range]
' 1-2
' 2-5
' 3-8

strSQL = "select [zip range] from tbl_ranges"
rs.Open strSQL, CurrentProject.Connection

i = 1
If Not (rs.BOF And rs.EOF) Then
Do Until rs.EOF
aTemp = Split(rs("range"), "-")
For j = Int(aTemp(0)) To Int(aTemp(1))
ReDim Preserve aRange(i) As Integer
aRange(i) = j
strRange = strRange & aRange(i) & vbCrLf
i = i + 1
Next
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

' the messagebox will display the results you seek
MsgBox strRange

i have current table in below format

zip range
1-2
2-5
3-8


i want to convert the above field - zip range into individual values
in the vertical fashion- for example
1-2 has 1 and 2 digits all written down one below another . same with
2-5 row- 2,3,4,5 should be written one below the other

result required

zip
1
2
2
3
4
5
3
4
5
6
7
8

is there a squl query which can convert the above format

On Monday, June 28, 2010 6:31 AM Allen Browne wrote:
The missing numbers need to come from somewhere, so the easiest way is to
use a Cartesian product on a Counting table.

It will be easiest if you can change your existing table so it has 2 fields
of type number for the range. Let's call them ZipMin and ZipMax. (If you
cannot you will have to use Instr() to parse the 2 numbers at the dash, along
with Left() and Mid() to get the 2 numbers, and Val() to convert them to
true numbers. It would be *much* better to use 2 fields.)

1. Create a table with just one field name (say) CountID, of type Number.
Mark it as primary key.
Save the table.

2. Enter numbers from 0 to the highest number you could need. If that could
be high, you could use the code at the bottom of this webpage to enter the
records for you:
http://allenbrowne.com/ser-39.html

3. Create a query using both this counting table and your original table. In
the upper pane of table design, there must be no line joining the 2 tables.
(This gives you every possible combination, called a Cartesian product.)

4. Drag CountID from the counting table into the query design grid.
In the Criteria row under this field, enter:
Between [ZipMin] And [ZipMax]
This will limit the numbers to the correct range, giving you the output you
desire.

On Tuesday, August 10, 2010 3:56 PM Dr GUI wrote:
Not sure that you can accomplish this in a single query but here is a possible solution you might try. Array aRange will contain the values you need.



Dim strSQL As String

Dim aRange() As Integer

Dim rs As New ADODB.Recordset

Dim aTemp() As String

Dim i As Integer

Dim j As Integer

Dim strRange As String



' table tbl_Zips has the following values in field [zip range]

' 1-2

' 2-5

' 3-8



strSQL = "select [zip range] from tbl_ranges"

rs.Open strSQL, CurrentProject.Connection



i = 1

If Not (rs.BOF And rs.EOF) Then

Do Until rs.EOF

aTemp = Split(rs("range"), "-")

For j = Int(aTemp(0)) To Int(aTemp(1))

ReDim Preserve aRange(i) As Integer

aRange(i) = j

strRange = strRange & aRange(i) & vbCrLf

i = i + 1

Next

rs.MoveNext

Loop

End If

rs.Close

Set rs = Nothing



' the messagebox will display the results you seek

MsgBox strRange

Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorial...w-excel-2010--the-missing-manual-oreilly.aspx