Solved

Create an ascending numberic value.

Posted on 2008-10-10
13
400 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
  • 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 119

Expert Comment

by:Rey Obrero
ID: 22691326
see this sample db


db1.mdb
0
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

11 Experts available now in Live!

Get 1:1 Help Now