rodrunner79
IS-IT--Management
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.
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:
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.
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 Comm789Here'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 A01I 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.
