?
Solved

UpDate Field

Posted on 2013-11-19
6
Medium Priority
?
187 Views
Last Modified: 2014-03-20
I have a table named "InspectionsInfo" in this table there are two Fields:
"Inspection Number" and " Inspection Type Code"

I like to have a script that I can run to update the Inspection number is the following criteria is found:

If the "Inspection Number" and "Inspection Type Code" are the same  then update any subsquent records where these two fields are the same by adding an "A","B","C",etc, to the end of the "Inspection Number" see example below.

Can someone please assist me with this script???


                      CURRENT DATA                                       AFTER UPDATE
Inspection Number  Inspection Type Code              Inspection Number
1998-00000130               PL05                                     1998-00000130
1998-00000130               PL01                                             1998-00000130
1998-00000130               PL01                                            1998-00000130A
1998-00000130               PL04                                            1998-00000130
1998-00000130               PL04                                            1998-00000130A
1998-00000130               PL04                                            1998-00000130B
1998-00000130               PL04                                            1998-00000130C
1998-00000130               PL04                                            1998-00000130D
1998-00000130               BD03                                          1998-00000130
1998-00000130               BD03                                          1998-00000130A
0
Comment
Question by:TonyEF
[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
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39660829
The first thing you need to do is define "subsequent".  What orders the records?
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39660838
try this...if the output reflects what you're looking for it's a simple matter to convert to an update.

WITH cteInfo (Number, TypeCode, RowNumber)
AS
(
      SELECT [Inspection Number],
            [Inspection Type Code],
            ROW_NUMBER() OVER(PARTITION BY [Inspection Number], [Inspection Type Code] ORDER BY [Inspection Number]) AS RowNumber
      FROM InspectionsInfo
)
SELECT Number, TypeCode,
      CASE WHEN RowNumber = 1 THEN Number ELSE Number + CHAR(RownUmber + 63) END
FROM cteInfo
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39663208
why don't you add a sequence number column instead?
where  does the inspection number come from in the first place ? (is the column a foreign key to another table?)
why do you have duplicates?
what distingusihes the rows in the first place...?
what is the correct order for the rows...?
what happens when you run out of letters ...? (A..Z or miss out vowels , or O, Q, I..)
       (you have more than 23... 26 duplicates)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TonyEF
ID: 39663382
BriCrowe

It is what I need but noticed that some of the records have more than 26 duplicates, in this case once it reaches the letter Z start with AA and then AB and so on, otherwise it is exactly what I need and if you can help me make that change as well Creating a new field with the new Inspection Number I would greatly appreciate it..
0
 

Author Comment

by:TonyEF
ID: 39663396
Lowfatsprea

The data is stored like this from a very old database, the reason I need this done is because the new system that I am converting the data too does not except duplicates. The new system considers duplicates where the Inspection Number and the Inspection Type Code are the same. These records are not actually duplicates it is just the way the data is being stored by this old system.
 Once after the Z it needs to start with AA, AB, AC and so on....
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39666201
try this...

WITH cteInfo (Number, TypeCode, RowNumber)
AS
(
      SELECT [Inspection Number],
            [Inspection Type Code],
            ROW_NUMBER() OVER(PARTITION BY [Inspection Number], [Inspection Type Code] ORDER BY [Inspection Number]) AS RowNumber
      FROM InspectionsInfo
)
SELECT Number, TypeCode,
      Number + CASE WHEN RowNumber / 26 > 0 THEN CHAR(((RowNumber) / 26) + 64) ELSE '' END
            + CASE WHEN RowNumber > 1 THEN CHAR(CASE (RowNumber - 1) % 26 WHEN 0 THEN 26 ELSE (RowNumber - 1) % 26 END + 64) ELSE '' END
FROM cteInfo
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 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