INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to auto-generate a 3 character value...

How to auto-generate a 3 character value...

(OP)
Okay here's my task:

I have a table consisting of 5 columns: FacilityID, , VendorPartNo, VendorCode, UnitOfMeasure, CommodityCode and Suffix

Assuming that all records have values for all of the first five columns and the last column, Suffix is NULL.  I want to create an update query that incorporates a custom function that generates an auto-sequential 3-char (not 3-digit) value as the Suffix.  The autonumber generation must follow this sequence: A01-A99, then AA1-AA9, then AB1-AB9 and so on.  

Now here's the tricky part: some records in the table may have the same CommodityCode but can be purchased from a different vendor, that have different part numbers and different unit of measure.  So given that, here's an example of the data.

CODE

Record1: Fac1 VPartXYZ Vendor1 EA CommABC
Record2: Fac2 VPartXYZ Vendor1 EA CommABC
Record3: Fac3 VPart123 Vendor2 BX CommABC
Record4: Fac3 VPart123 Vendor2 EA CommABC
Record5: Fac1 VPart001 Vendor1 CS Comm789
To generate a a unique key, one would have to concatenate all 5 columns. What want to do is develop a procedure that would evaluate each record that contains the same commodity code (in the example above, that would be the first 4 record) and auto-generate a suffix for those records with same commodity code.  Example, Record1 would have a suffix of A01, Record2 of A02, Record3 of A03 and so on.  The next different CommodityCode would start the suffix again at A01.  For example, because Record5 has a different CommodityCode (Comm789), the suffix should start at A01 and generate sequentially up to the number of records that contains this CommodityCode.

Here's how the final records after the Update Query is executed should look like:

CODE

Record1: Fac1 VPartXYZ Vendor1 EA CommABC A01
Record2: Fac2 VPartXYZ Vendor1 EA CommABC A02
Record3: Fac3 VPart123 Vendor2 BX CommABC A03
Record4: Fac3 VPart123 Vendor2 EA CommABC A04
Record5: Fac1 VPart001 Vendor1 CS Comm789 A01

I was thinking about building a Foor Each...Next statement inside a Sub procedure or Function that I can call in the query but I am not well verse with For Each... Next statements.  Can somebody please show me how to do this or if there's an easier way, direct me in the right direction on how to handle this.

Thanks in advance for those who answers.

Can't means won't try.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close