Create an ascending numberic value.

Hi,
I receive voucher data with around 200,000 - 1,000,000 records and need to build tables for processing. The only data I receive is a random ascending voucher or coupon Id Like so....RDP000333555. Based on how many of these vouchers will be placed in a sample kit...5 or 10 etc..I need to build a control number. The example is based on 55,000 records with 5 per kit with equals 11,000 kits. This example is the first kit.

Vouchers:            Voucher ID                    Kit Control Number
                             (Individual vouchers)    (numer on the outside of the kit)
RDP000000001     000001A                       000001
RDP000000009     000001B                       000001
RDP000000128     000001C                       000001
RDP000002223     000001D                       000001
RDP000003698     000001E                       000001

Some of these jobs run together and I need to start the numbers at a different point instead of always at 000001. Also I need these padded with "0" to 6 Positions.

What I would like to have is fill out a form with the amount of vouchers per kit and the starting number and have the code generate and attach the numbers to the vouchers.
Records to process: 200,000
Vouchers Per Kit: 5
Starting Number: 000001

Thanks in advance.

Ken
LVL 2
Kenneth MarksProgramming ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

replace the codes with this



Private Sub Command6_Click()
If IsNull(Me.txtRecords) Or IsNull(Me.txtVouchersPerKit) _
    Or IsNull(Me.txtStartingNumber) Then
    MsgBox "Fill out all the textboxes"
    Exit Sub
End If
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strVouchers, strKit, sql, j, x, i
Set rs1 = CurrentDb.OpenRecordset("tblVoucherKit")
x = Me.txtStartingNumber
sql = "select top " & CLng(Me.txtRecords) & " [vouchers] from tblvoucher order by vouchers"
Set rs = CurrentDb.OpenRecordset(sql)
j = 0
rs.MoveFirst
    For i = 1 To CLng(Me.txtRecords)
    j = j + 1
        rs1.AddNew
        rs1!vouchers = rs!vouchers
        rs1![Voucher ID] = Format(x, "000000") & Chr(64 + j)
        rs1![Kit Control Number] = Format(x, "000000")
        rs1.Update
        If j = CLng(Me.txtVouchersPerKit) Then
            j = 0: x = CLng(x) + 1
        End If
        If Not rs.EOF Then
            rs.MoveNext
            Else
            Exit For
        End If
    Next
 
rs.Close
rs1.Close
End Sub

Open in new window

0
 
GRayLCommented:
>and have the code generate and attach the numbers to the vouchers.<  meaning?
0
 
Kenneth MarksProgramming ManagerAuthor Commented:
Hi,
Thanks for the reply.

I need to somehow using vba to create the voucher id and kit control based on the parameters....5 vouchers for one kit starting with the number at wherever I need to start. The table unfortunately does not have the suffiect number to help in this process.

Vouchers:            Voucher ID                    Kit Control Number
                             (Individual vouchers)    (numer on the outside of the kit)
RDP000000001     000001A                       000001
RDP000000009     000001B                       000001
RDP000000128     000001C                       000001
RDP000002223     000001D                       000001
RDP000003698     000001E                       000001
RDP000004444     000002A                      000002
RDP000005555     000002B                      000002
ETC....

Thanks again

Ken
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
see this sample db


db1.mdb
0
 
GRayLCommented:
Saw nothing about creating VoucherID in the question.  Are you saying you receive a batch of Vouchers (RDPno's) and instructions to batch in groups of n (5 to  whatever), where the batch numbers from 100,000 to 1,000,000 records or so and you need to 'package' the groups with a VoucherID and a KitControlNo?  ie.

Vouchers
=======
VoucherNo

selects into:

BatchVouchers
==========
BVID - autonumber
VoucherNo  -  foreign key from Vouchers
VoucherID - generated value starting at "000001" & consecutive letter from A up to the kit size
KitControlNo - generated value starting at "000001" and incrementing for each batch.

??
0
 
GRayLCommented:
Nice work Rey.  I thought each kit contained 5, or whatever the number voucher numbers, and we had to supply the VoucherID and the KitNo?

kdart301:  please clarify.
0
 
Kenneth MarksProgramming ManagerAuthor Commented:
Sorry If I am being vague....

capricorn1, that is almost what I need.... nice job....The voucher table is what I start with, in your example 100 vouchers. I do not need the vouchers to repeat just add what you did for the voucher id and the kit control number.
Vouchers                    Voucher Id              Kit Control Number
RDP000000001            000001A                 000001
RDP000000002            000001B                 000001
RDP000000003            000001C                 000001
RDP000000004            000001D                 000001
RDP000000005            000001E                 000001
RDP000000006            000002A                000002
RDP000000007            000002B                000002
RDP000000008            000002C                000002
RDP000000009            000002D                000002
RDP000000010            000002E                000002

The voucher table began with 100 records the result table has 500 records, the result should only have the original 100 records with the other information laid in.

Thanks

Ken
0
 
Rey Obrero (Capricorn1)Commented:
what did you do to have 500 records?
0
 
Kenneth MarksProgramming ManagerAuthor Commented:
Hi,

When I ran the program:

Records to process 100 ( the voucher table had 100 records)
voucher per kit 5
starting number 1

the tblvoucherkit had 500 records and the vouchers were repeating for each kit. The vouchers should not change just add the voucher id and kit control to the existing vouchers for 100 records. Try the program using the spec I entered.

Thanks again

Ken
0
 
Rey Obrero (Capricorn1)Commented:
because you are using the same vouchers...

in real time processing of your vouchers (  around 200,000 - 1,000,000 records )
what do you do with the vouchers after you run a single process of

Records to process: 200,000
Vouchers Per Kit: 5
Starting Number: 000001

also, where do you store and retrieve the (  around 200,000 - 1,000,000 records ) ?
0
 
Kenneth MarksProgramming ManagerAuthor Commented:
Hi,
Thanks again.

In real time processing I received between 10,000 and 1,000,000 records each are individual voucher numbers. I then, depending on the project have to create manifest files based on our fulfillment.
Ok one example: I received 55,000 vouchers and the job consists of 5 individual vouchers per kit. The customer only wants to create 10,000 kits or use 50,000 vouchers. What I do is for every 5 different vouchers, because these are handed out to a doctor's 5 different patients, I create one voucher id and kit control number for tracking of the rep or physician. I do not need to create extra or duplicate vouchers I need to assign duplicate voucher id and kit control numbers to the existing voucher numbers. The second part works but the duplicating of the vouchers is not. Please see and example table.

Thanks again

Ken

ps. I need to explain this better sorry.
sample.JPG
0
 
Kenneth MarksProgramming ManagerAuthor Commented:
capricorn1

Everything works perfect...Thank-you so very much.....

Ken

0
 
Kenneth MarksProgramming ManagerAuthor Commented:
This will save alot of time, especially since I am reprogramming the whole project. Thanks Again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.