Numbering in Access between two numbers

[Ariel]

I have a question that I'm hoping someone here can answer. Let's say I
have two fields which have a beginning number and an ending number.
What I'd like to do is have Access generate a list of numbers between
the beginning number and the ending number (inclusive) for each row
that exists in my start table. For example, my start table might look
like:

Begin End
513525 513528
513530 513538

I would want my end table to look like:

NPA-NXX
513525
513526
513527
513528
513530
513531
513532
513533
513534
513535
513536
513537
513538

We are trying to build a list of NPA-NXXs as defined between the begin
and end bounds. Any help in doing so would be greatly appreciated.

Cheers,

Ariel

[pietlinden]

I did this not that long ago. Create a sub that does it.

Open this table as a recordset, and then use a for/next loop to create
all the instances.

Something like....
NOT TESTED...
Public Sub CreateSequence(byval lngStartValue as Long, lngStopValue as
long)
dim db as dao.database
dim rs as dao.recordset
dim lngCounter as Long

set db=dbengine(0)(0)
set rs=db.openrecordset("DestTable",dbopentable)

for lngCounter = lngStartValue To lngStopValue
rs.Addnew
rs.Fields("DestinationField")=lngCounter
'---add any other values here if you want.
rs.Update
next lngCounter

rs.close
set rs=nothing
End Sub

[Ariel]

Thank you for your reply. Now I have to admit that I've never done VB
in Access and only done a little bit in Excel. Can I ask the dumb
question of what some of these things mean? I think I can understand
some of it - specifically:

(1) dbengine(0)(0) - what does this mean?
(2) rs - is this the name of the new table in Access? Or do you have
to create the new table first and is this just a record set held in
VBA?
(3) What does "dao" mean?
(4) Is "nothing" at the end to reset rs to NULL?

I really appreciate the help and apologize for my ignorance.

Thanks - Ariel

[jimfortune]

Ariel wrote:

I have a question that I'm hoping someone here can answer. Let's say I have two fields which have a beginning number and an ending number.
What I'd like to do is have Access generate a list of numbers between
the beginning number and the ending number (inclusive) for each row
that exists in my start table. For example, my start table might look like:

Begin End
513525 513528
513530 513538

I would want my end table to look like:

NPA-NXX
513525
513526
...

I found a way to do this using only SQL but it's a bit of a kludge.

I created (by copying any small table, deleting all the fields, adding
ID as AutoNumber, then saving:

tblNatural
ID AutoNumber
1
2
....
26

tblStart
Start Long
End Long

513525 513528
513530 513538

qryRange:
SELECT (SELECT Min(Start) FROM tblStart) + (SELECT Count(*) FROM
tblNatural AS A WHERE A.ID < tblNatural.ID) AS theRange FROM
tblNatural;

gave:
513525
....
513538

qryNPANXX:
SELECT qryRange.theRange AS NPANXX FROM qryRange, tblStart WHERE
(qryRange.theRange) Between [tblStart].[Start] And [tblStart].[End];

gave:
NPANXX
513525
....
513528
513530
....
513538

The kludgy part is that tblNatural must have at least Max(End) -
Min(Start) + 1 values to get the entire range that's possible. If I
think of a better SQL solution I'll post back.

James A. Fortune

[jimfortune]

Ariel wrote:

Thank you for your reply. Now I have to admit that I've never done VB in Access and only done a little bit in Excel. Can I ask the dumb
question of what some of these things mean? I think I can understand
some of it - specifically:

(1) dbengine(0)(0) - what does this mean?
(2) rs - is this the name of the new table in Access? Or do you have
to create the new table first and is this just a record set held in
VBA?
(3) What does "dao" mean?
(4) Is "nothing" at the end to reset rs to NULL?

I really appreciate the help and apologize for my ignorance.

Thanks - Ariel

Ariel,

I'll try to keep this simple (perhaps too simple). The code that Piet
posted is a standard technique for adding new records (rows) to a
table. This subroutine would be inside of a loop that grabs the Begin
and End numbers for each record in your start table. For each grab of
Begin and End numbers, the subroutine is called and the appropriate
number of new values are added to the destination table (what you call
your end table). His DestinationField corresponds to the field I call
NPANXX inside of the end table. I forgot to mention that my qryNPANXX
can be changed into a MakeTable query to create an end table if
necessary.

dbEngine(0)(0) starts up the Jet Engine (really) allowing VBA (Visual
Basic for Applications - the VB behind Access) to communicate with the
data. The database object contains code called OpenRecordset that
opens up a recordset object that is used by VBA to communicate with
data in the output (end) table. Note that another recordset object
would need to be opened in the outside loop to communicate with the
start table. DAO stands for Data Access Objects. They're Objects that
can be used to communicate with the data (i.e., Access the Data) such
as the database object or the recordset object you asked about. The
rs.AddNew calls code in the recordset object that creates (but doesn't
save) a new record of Null values. The rs.Update calls code in the
recordset object that saves the changes to the new record (see AddNew
in the Help file). We often specify DAO explicitly since Microsoft
comes up with other ways of communicating with the data and the Access
program sometimes gets confused. The Set rs = nothing is used to
ensure that the RAM memory Access uses to maintain communication with
the data is released. In programming languages such as Visual C++,
failure to release memory can cause memory "leaks" that can hang on to
chunks of RAM making them unusable and slowing down performance until
the machine is rebooted. Access might be able to release the memory
automatically when the subroutine finishes but many Access programmers
have learned not to take too much for granted. I hope this answers
your questions. If not, keep asking questions. Hopefully, before long
you'll be answering our programming questions in this NG.

James A. Fortune

Ariel (n): a prankish spirit in Shakespeare's The Tempest --
www.m-w.com

Fortune (n): the prankish spirit in Shakespeare's Hamlet

[Ariel]

Funny that we're both prankish per Shakespeare.

Thanks a lot for the help so far . Now I am going to ask more
questions, if that is OK.

First, let me paste the code as I have modified it:

Public Sub CreateSequence(ByVal lngStartValue As Long, lngStopValue As
Long)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Long

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("NPA_NXX_Output", dbOpenTable)

For lngCounter = lngStartValue To lngStopValue
rs.AddNew
rs.Fields("NPA_NXX") = lngCounter
'---add any other values here if you want.
rs.Update
Next lngCounter

rs.Close
Set rs = Nothing
End Sub

My only changes have been to set the destination table name
NPA_NXX_Output and the destination field name NPA_NXX. The people to
whom I am handing this over are even less Access proficient than I am,
so what I would like to do is set this up to run within a Macro so that
the entire work product happens in an automated, no-brainer kind of
way. I saved this Module as Module1 (for now).

(1) How can I call the Sub in a Macro? I saw a Macro step called
"RunCode". Within that, I could see "Module1" in the Folder NPA_NXX
(my database name) under Functions. However, it did not have
associated functions on the right side, as it did if I looked at the
standard Access functions. The two data elements I am trying to pass
to the Sub are NPA_NXX_Only.Begin_NN and NPA_NXX_Only.End_NN. So I
tried writing Module1(NPA_NXX_Only.Begin_NN, NPA_NXX_Only.End_NN). I
also tried CreateSequence(NPA_NXX_Only.Begin_NN, NPA_NXX_Only.End_NN).
Neither one worked. How should I do this?
(2) Looking at the code, how does the code the number of times to
repeat the process? My NPA_NXX_Only table will be imported from a
series of Excel spreadsheets and have any number of rows.
(3) If Begin_NN = End_NN, will it still put in the appropriate value?
Or do I need to separate the values where they are equal first?
(4) In my NPA_NXX_Only Table, I have an additional text field called
"Division". Is it possible to pass the contents of the "Division"
field to the new table? e.g. if Division1 is Begin-End: 513525 513528
and Division2 is 513530 513538, I would like it to have 513525 in the
NPA_NXX field and Division1 in the Division field; 513526, Division1;
513527, Division1; 513528, Division1; 513530, Division2; etc...

[jimfortune]

Ariel wrote:

Funny that we're both prankish per Shakespeare.

Thanks a lot for the help so far . Now I am going to ask more
questions, if that is OK.
I O.K.'d your OK in advance so that's O.K.

First, let me paste the code as I have modified it:

Public Sub CreateSequence(ByVal lngStartValue As Long, lngStopValue As Long)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Long

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("NPA_NXX_Output", dbOpenTable)

For lngCounter = lngStartValue To lngStopValue
rs.AddNew
rs.Fields("NPA_NXX") = lngCounter
'---add any other values here if you want.
rs.Update
Next lngCounter

rs.Close
Set rs = Nothing
End Sub

My only changes have been to set the destination table name
NPA_NXX_Output and the destination field name NPA_NXX. The people to
whom I am handing this over are even less Access proficient than I am, so what I would like to do is set this up to run within a Macro so that the entire work product happens in an automated, no-brainer kind of
way. I saved this Module as Module1 (for now).
I'm glad to see that you jumped right in to VBA programming. For the
easiest no-brainer solution (I realize I'm leaving myself open here
:^)) use my SQL only solution by running the SQL in a query directly or
from a macro. I can give you further help if you want to set up
running Piet's code from a macro.

(1) How can I call the Sub in a Macro? I saw a Macro step called
"RunCode". Within that, I could see "Module1" in the Folder NPA_NXX
(my database name) under Functions. However, it did not have
associated functions on the right side, as it did if I looked at the
standard Access functions. The two data elements I am trying to pass
to the Sub are NPA_NXX_Only.Begin_NN and NPA_NXX_Only.End_NN. So I
tried writing Module1(NPA_NXX_Only.Begin_NN, NPA_NXX_Only.End_NN). I
also tried CreateSequence(NPA_NXX_Only.Begin_NN, NPA_NXX_Only.End_NN). Neither one worked. How should I do this?
In order for the macro to see your Sub you will need to use the Public
keyword at the beginning of the Sub so that Access knows you want to be
able to call the subroutine from outside the module.
(2) Looking at the code, how does the code the number of times to
repeat the process? My NPA_NXX_Only table will be imported from a
series of Excel spreadsheets and have any number of rows.
That's because you never put in the outside loop that grabs those pairs
of numbers.
(3) If Begin_NN = End_NN, will it still put in the appropriate value?
Or do I need to separate the values where they are equal first?
Both solutions will work when both the numbers are equal (we
anticipated your needs).
(4) In my NPA_NXX_Only Table, I have an additional text field called
"Division". Is it possible to pass the contents of the "Division"
field to the new table? e.g. if Division1 is Begin-End: 513525 513528 and Division2 is 513530 513538, I would like it to have 513525 in the NPA_NXX field and Division1 in the Division field; 513526, Division1;
513527, Division1; 513528, Division1; 513530, Division2; etc...

Yes. (I'm only slightly prankish so I'll continue) Piet anticipated
that but he assumed you would pull in Division values as part of the
outer loop code. I'll show you how to modify my queries to include
Division if you decide to use them.

James A. Fortune

[corey lawson]

"Ariel" <ar******@yahoo.com> wrote in news:1111442559.415234.268090
@l41g2000cwc.googlegroups.com:

I have a question that I'm hoping someone here can answer. Let's say I
have two fields which have a beginning number and an ending number.
What I'd like to do is have Access generate a list of numbers between
the beginning number and the ending number (inclusive) for each row
that exists in my start table. For example, my start table might look
like:

Begin End
513525 513528
513530 513538

I would want my end table to look like:

NPA-NXX
513525
513526
513527
513528

Another way is to create a table that just has a bunch of numbers in it,
say, from 0-99999 (that's 100K records). I'll call this NumberTable, for
lack of imagination.

I'll assume your table name is NPAs. Then you can do this:

select Part, begin+TheNumber as [npa-nxx]
from NPAs, NumberTable
where NumberTable.TheNumber+NPAs.Begin <= NPAs.End

Yes, it's a cartesian join, but at least it's supported by the Query
Designer.

You might be able to do it with a "Theta Join", which is NOT supported by
the Query Designer (but you can type in the SQL in the SQL Window):

select NPAS.Part, NPAS.begin+NT.TheNumber as [npa-nxx]
from NumberTable as NT RIGHT OUTER JOIN NPAs ON NT.TheNumber between
(NPAS.Begin - NPAS.Begin) and (NPAS.End - NPAS.End)