Solved

Create a list from 2 numbers

Posted on 2013-11-11
13
225 Views
Last Modified: 2013-11-11
I need to create a list based on 2 numbers

1st Number = 10301(BatchID)
2nd Number = 206001 (BadgeNo)

I want to create a table with the above 2 columns,

10301 will be added 20 times with 206001 incrementing until it reaches 206020

It will then start again with 10302 added 20 times with 206021 incrementing until it reaches 206040 and so on...

10303 - 206041
10303 - ..........
10303 -206060

Below is an example how the 1st two would look like.


BatchID  BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020

10302      206021
10302      206022
10302      206023
10302      206024
10302      206025
10302      206026
10302      206027
10302      206028
10302      206029
10302      206030
10302      206031
10302      206032
10302      206033
10302      206034
10302      206035
10302      206036
10302      206037
10302      206038
10302      206039
10302      206040
0
Comment
Question by:Brogrim
  • 8
  • 4
13 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39638397
You haven't specified how high you want to increment the first number (you have shown two increments), but this is the general approach.  Change the "For j = 2" to go as many times as you need the first number incremented.

Dim lng1, lng2 as long
Dim i, j as integer

lng1 = 10301 
lng2 = 206001

For j = i to 2

For i = i to 20 
    currentDB.execute "INSERT INTO YourTable (Field1, Field2) VALUES (" & lng1 & ", " & lng2 & ")", dbfailonerror
     lng2 = lng2 + 1
Next

lng1 = lng1 + 1

Next

Open in new window

0
 

Author Comment

by:Brogrim
ID: 39638407
Ist number is incremented by 1

how do I execute this, do i create a form and add this code to a command button
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39638413
Miriam,

don't you need to reset the value of  the variable 'i' after exiting the inner loop?
0
 

Author Comment

by:Brogrim
ID: 39638419
I have created the form and your suggestion is working but needs some editing

When i execute I am getting the following:

BatchID      BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020
10301      206021

I want it to stop at :
10301      206020

and then continue on
BatchID      BadgeNo
10302      206021

I need 13,000 records like above
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638437
Ok - then try this (I mistakenly used i as my starting increment instead of 1)

Dim lng1, lng2 as long
Dim i, j as integer

lng1 = 10301 
lng2 = 206001

For j = 1 to 2

For i = 1 to 19 
    currentDB.execute "INSERT INTO YourTable (Field1, Field2) VALUES (" & lng1 & ", " & lng2 & ")", dbfailonerror
     lng2 = lng2 + 1
Next

lng1 = lng1 + 1

Next

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638445
<< I need 13,000 records like above >>

Still just using two groups of the first number for testing.  You can change the 2 in "For j= 1 to 2" to however many groups you need after verifying that the groups are what you need.

Also, delete all the records before testing this a second time so that you're starting with an empty table.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 61

Expert Comment

by:mbizup
ID: 39638450
<<don't you need to reset the value of  the variable 'i' after exiting the inner loop?>>

Dale, no.. he needs to continue numbering where he left off for each subsequent group of numbers (they dont get restarted)

EDIT
Ahh... I see what you mean.  That i = i to 20 was a typo.  should have been i = 1 to 20 (I corrected that above)
0
 

Author Comment

by:Brogrim
ID: 39638580
Getting there, that is now working and creating the correct sequence

BatchID      BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020

You can change the 2 in "For j= 1 to 2" to however many groups you need after verifying that the groups are what you need.

the above is making 2 lists but both with the same BatchID and BadgeNo's

BatchID      BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638650
Did you delete all the records in the table before testing it again?

I'm getting the correct results...

 10301         206001 
 10301         206002 
 10301         206003 
 10301         206004 
 10301         206005 
 10301         206006 
 10301         206007 
 10301         206008 
 10301         206009 
 10301         206010 
 10301         206011 
 10301         206012 
 10301         206013 
 10301         206014 
 10301         206015 
 10301         206016 
 10301         206017 
 10301         206018 
 10301         206019 
 10301         206020 

 10302         206021 
 10302         206022 
 10302         206023 
 10302         206024 
 10302         206025 
 10302         206026 
 10302         206027 
 10302         206028 
 10302         206029 
 10302         206030 
 10302         206031 
 10302         206032 
 10302         206033 
 10302         206034 
 10302         206035 
 10302         206036 
 10302         206037 
 10302         206038 

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39638656
Try this - it deletes the records in the table before creating a new list:


Dim lng1, lng2 as long
Dim i, j as integer

CurrentDB.Execute "DELETE * FROM YourTable", dbFailOnError

lng1 = 10301 
lng2 = 206001

For j = 1 to 2

For i = 1 to 19 
    currentDB.execute "INSERT INTO YourTable (Field1, Field2) VALUES (" & lng1 & ", " & lng2 & ")", dbfailonerror
     lng2 = lng2 + 1
Next

lng1 = lng1 + 1

Next

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638674
If that doesn't help, post the code exactly as you have implemented it in your database.
0
 

Author Closing Comment

by:Brogrim
ID: 39638744
Dont know what you changed here (if anything). I removed the auto number this side and all worked fine.

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638765
Glad to help out.  The only thing I added was the Delete to clear the table before adding the records.  I'm just guessing that you had duplicates from separate trials of the code.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now