Solved

Create a list from 2 numbers

Posted on 2013-11-11
13
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Expert Comment

by:Dale Fye
ID: 39638413
Miriam,

don't you need to reset the value of  the variable 'i' after exiting the inner loop?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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
 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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