?
Solved

Create an ascending numberic value.

Posted on 2008-10-10
13
Medium Priority
?
423 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:kdart301
[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
  • 6
  • 4
  • 3
13 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 22690698
>and have the code generate and attach the numbers to the vouchers.<  meaning?
0
 
LVL 2

Author Comment

by:kdart301
ID: 22690793
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22691326
see this sample db


db1.mdb
0
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.

 
LVL 44

Expert Comment

by:GRayL
ID: 22691873
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22691917
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
 
LVL 2

Author Comment

by:kdart301
ID: 22693958
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22693970
what did you do to have 500 records?
0
 
LVL 2

Author Comment

by:kdart301
ID: 22693994
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22694024
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
 
LVL 2

Author Comment

by:kdart301
ID: 22694078
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 22694227

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

Author Comment

by:kdart301
ID: 22694259
capricorn1

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

Ken

0
 
LVL 2

Author Closing Comment

by:kdart301
ID: 31505183
This will save alot of time, especially since I am reprogramming the whole project. Thanks Again.
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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 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