Juli 14, 2020, 21:55:11

Neuigkeiten:

Ist euer Problem gelöst, dann bitte den Knopf "Thema gelöst" drücken!


Fortlaufende Nummerierung

Begonnen von nurmikss, Juni 24, 2020, 17:59:58

⏪ vorheriges - nächstes ⏩

nurmikss

Ich habe eine Tabelle bekommen mit einer unglücklichen diskontinuierlichen Nummerierung, die durch eine neue, fortlaufende Nummerierung ersetzt werden soll:
Nummer_altNummer_neu
XY Nr. 001001XY-0001
XY Nr. 010007XY-0002
XY Nr. 127005XY-0003
Ich habe die Tabelle nach "Nummer_alt" sortiert und in dieser Reihenfolge soll die Neunummerierung stattfinden.
Vorgabe ist der führende Textteil ("XY") vor der laufenden Nummer - es muss also ein Textfeld sein.

Die Lösung http://www.donkarl.com/?FAQ3.11 habe ich schon gefunden, aber vielleicht ist sie mir zu knapp formuliert - ich komme damit nicht zurecht.

Wie kann ich bei einer sortierten Tabelle ein leeres Textfeld mit fortlaufenden Nummern füllen ?

Wenn ich das hätte, käme ich mit dem Anfügen des Präfixes und den führenden Nullen schon zurecht.
Ich verwende Microsoft Office Professional plus 2010

crystal

Hallo,
ich könnte mir sowas vorstellen:
* Endlos-Formular mit sortierter Abfrage der Tabelle als Quelle,
* darin ein gebundenes Textfeld mit Namen txtNummer, Datenquelle = neues Nummernfeld (als Text) in der Tabelle
* im Form-Modul ein public lngNummer as long
* im Form-Load-Event ein lngNummer = 0
* im Form-Current-Event
lngNummer = lngNummer + 1
txtNummer = lngNummer (ggf. umgewandelt mit führendem Text und aufgefüllten Nullen)
* dann einfach das Formular laden und schlicht durch alle Datensätze scrollen (Mausrad; aber nicht zu schnell...)
* kann so für geringe Datensatz-Zahlen klappen

Eine andere Lösung wäre es, in einem Modul(!) eine Public-Funktion zu basteln, die einen auch public gehaltenen Wert hochzählt, z. B.
Public lngNummer as Long
Public Function IncCounter() as Long
lngNummer = lngNummer + 1
IncCounter = lngNummer
End Function

oder entsprechend
Public Function MeinCounter() as String
(hier dann den fertigen String zusammenbauen)

Diese Funktion dann in der Abfrage (Aktualisierungs-Abfrage!) in einer Extra-Spalte aufrufen und dem entsprechenden (neuen) Tabellenfeld zuweisen (Tipp: im Abfrage-Editor "Aufbauen" mit rechter Maustaste wählen).

Und noch eine mögliche Lösung:
* die Ursprungs-Tabelle komplett sortiert nach Excel exportieren (z. B. auch als CSV)
* diesem Export in Excel eine neue Spalte verpassen und den Schlüssel-Wert neu mit einer Formel berechnen
* die Ursprungs-Tabelle ohne Daten kopieren oder kopieren und danach leeren und ein neues Feld einfügen
* die erweiterte Excel-Datei in diese neue Tabelle importieren
* Tabellen umbenennen, Referenzen prüfen/anpassen
oder
* nur Schlüssel alt in eine Excel-Datei exportieren
* neuen Schlüssel in Excel basteln
* in eine neue Tabelle importieren
* alte und neue Tabelle temporär joinen
* update TabelleAlt set TabelleAlt.NeuerSchlüssel = TabelleNeu.NeuerSchlüssel inner join on (TabelleAlt.Hauptschlüssel = TabelleAlt.Hauptschlüssel)

Ich gehe davon aus, dass du eine solche Aktion nur einmal machen möchtest, deshalb meine primitiv-pragmatischen Vorschläge. Vielleicht gibt es weitere Lösungen mit kompliziert verschachtelten SQL-Abfragen - fraglich ist, ob es sich lohnt, sowas zu entwickeln und dann nur einmal zu verwenden.

Ansonsten noch folgende Anmerkungen:
* Für neue Datensätze ist damit natürlich noch kein neuer Schlüssel mit fortlaufender Nummer erstellt.
* Solch "sprechende Schlüssel" mit Text-Anteilen machen prinzipiell Probleme, z. B. wenn man den zuletzt verwendeten (höchsten) Wert sucht, um ihn dann hochzuzählen; da muss auf jeden Fall programmiert werden, denn einfache SQL-Aggregat-Funktionen wie "Max" können da irritierende Ergebnisse liefern (Bsp: Schlüssel A-0020 und B-0011 - welcher ist jetzt "Max" und welcher hat den maximalen numerischen Anteil???)
* Sollen Bestandteile des Schlüssels separat hochgezählt werden oder gilt die laufende Nummer für alle "Schlüssel-Arten" einheitlich (A-01, B01, A-02... oder A-01, B-02, A-03, A-04, Z-05...)?
* Was passiert bei Überlauf der Nummer, wenn diese z. B. nur zweistellig vorgesehen ist (A-99, A-00???)
* Etwas einfacher kann es werden, wenn man den rein numerischen Teil separat speichert und erst in der Anwendungs-Oberfläche mit Text "dekoriert".
* Fraglich ist, ob so ein zusammengesetzter Schlüssel tatsächlich IMMER eine eindeutig fortlaufende Nummer enthalten MUSS oder ob auch Lücken akzeptiert werden können.
* Die Anzahl der für einen Schlüsseltyp bereits benutzen Nummern kann auch anders ermittelt werden, z. B.
select count(Primärschlüssel) from Tabelle where Primärschlüssel like 'ABC-*', wenn dies z. B. für eine Übersicht erforderlich wäre.
* Wie entsteht der zusammengesetzte Schlüssel? Stammt er von einem Fremd-System? Soll er zur Laufzeit aus anderen Eingaben zusammengebastelt werden? Wer verwaltet den numerischen Anteil?
* Verlasse dich DB-intern lieber auf einen Autowert als Primärschlüssel (und arbeite damit für Referenzen/Joins usw.), dann wäre es auch einfacher, einen evtl. falsch zusammengebauten Sekundär-Schlüssel zu korrigieren.
* Erstelle ggf. einen Index über mehrere Felder der Tabelle, mit dem die Eindeutigkeit über mehrere Felder sichergestellt werden kann.
* Was soll passieren, wenn die Struktur erweitert wird (bisher A-nnn, jetzt auch AAA-nnn oder A-nnn/nn?
* "Sprechende" Schlüssel sind oft schön, DB-intern aber nicht einfach zu verwalten und zu pflegen...
* Soll der numerische Anteil eines solchen Schlüssels irgendwann auch wieder auf 1 zurückgesetzt werden (z. B. wenn der Schlüssel eine Jahreszahl enthält wie in 2020-00001? Wer macht diesen Reset?
* Wie wird sichergestellt, dass IMMER ALLE Bestandteile des zusammengesetzten Schlüssels vorhanden sind/eingegeben werden?

Ich hoffe, damit deutlich zur Verwirrung beigetragen zu haben. :)
Tipp: wie wurde der Gordische Knoten gelöst? Anders als erwartet jedenfalls.

Grüße,
crystal

PS: Bitte um Entschuldigung, wenn das oben Geschriebene z. T. etwas polemisch klingen mag. Es ist nicht böse gemeint. Ich hatte nur schon vor vielen, vielen Jahren heftige Diskussionen zu diesem Thema...als es Access noch in der Version 2.0 gab und Windows 3.0 oder 3.11...
Wer Fehler in meinen Antworten findet, darf sie behalten, muss sie aber kommentieren. ;-)
Dies ist keineswegs arrogant gemeint, sondern soll nur unterstreichen, dass meine Antworten - natürlich - nicht immer fehlerfrei sind und sein können.
Devise: bitte immer erst selbst probieren!

nurmikss

Hallo crystal,
danke für Deine Mühe - das kann ich frühestens morgen durcharbeiten.
Vorab:
Zitat von: crystal am Juni 24, 2020, 21:11:42... dass du eine solche Aktion nur einmal machen möchtest, ... Vielleicht gibt es weitere Lösungen mit kompliziert verschachtelten SQL-Abfragen
...Ich hoffe, damit deutlich zur Verwirrung beigetragen zu haben. :)
Ja, die Neunummerierung ist ein einmaliger Vorgang.
Ich hatte gehofft, Access / SQL hat eine einfache Lösung, etwa: AuswahlFortlaufendDurchnummerieren(Feldname, Startwert, Schrittweite). Ich habe noch ein bisschen Resthoffnung ;)

Zitat* Solch "sprechende Schlüssel" mit Text-Anteilen machen prinzipiell Probleme, ...
* Etwas einfacher kann es werden, wenn man den rein numerischen Teil separat speichert und erst in der Anwendungs-Oberfläche mit Text "dekoriert".
* "Sprechende" Schlüssel sind oft schön, DB-intern aber nicht einfach zu verwalten und zu pflegen...
* Verlasse dich DB-intern lieber auf einen Autowert als Primärschlüssel (und arbeite damit für Referenzen/Joins usw.), dann wäre es auch einfacher, einen evtl. falsch zusammengebauten Sekundär-Schlüssel zu korrigieren.
Das sehe ich auch so, aber die Struktur von "Nummer_neu" (Text-Präfix mit laufender Nummer in diesem Feld) ist so vorgegeben (immerhin wird das überflüssige "Nr. " aufgegeben).

Zitat* Für neue Datensätze ist damit natürlich noch kein neuer Schlüssel mit fortlaufender Nummer erstellt.
* Wie entsteht der zusammengesetzte Schlüssel? Stammt er von einem Fremd-System? Soll er zur Laufzeit aus anderen Eingaben zusammengebastelt werden? Wer verwaltet den numerischen Anteil?
Neue Schlüssel werden auf einem Fremdsystem manuell eingegeben (mit allen Risiken wie Tippfehler, überzählige Leerzeichen, ...)

Zitat* Sollen Bestandteile des Schlüssels separat hochgezählt werden oder gilt die laufende Nummer für alle "Schlüssel-Arten" einheitlich (A-01, B01, A-02... oder A-01, B-02, A-03, A-04, Z-05...)?
In dieser Tabelle gibt es ausschließlich Feldinhalte mit dem Präfix "XY-"

Zitat* Was passiert bei Überlauf der Nummer, wenn diese z. B. nur zweistellig vorgesehen ist (A-99, A-00???)
Die Auffüllung mit führenden Nummern auf vier Stellen war meine Idee, damit man das Textfeld wenigstens ordentlich sortieren kann. Vier Stellen reichen für den Verwendungszweck aus.

Zitat* Was soll passieren, wenn die Struktur erweitert wird (bisher A-nnn, jetzt auch AAA-nnn oder A-nnn/nn?
* Wie wird sichergestellt, dass IMMER ALLE Bestandteile des zusammengesetzten Schlüssels vorhanden sind/eingegeben werden?
Die Struktur soll so bleiben, bis ich in Rente gehe...
Wenn bestimmt würde, dass dem Präfix "XY" noch ein "Y" folgen soll, könnte ich das mit Regulären Ausdrücken einflicken.
Wenn jemand manuell einfach eine falsche Syntax eintippt ? Das ist eine Schwachstelle unseres Fremdystems - vielleicht hat eine kommende Version mal eine Syntaxprüfung. Bis dahin mache ich das von Zeit zu Zeit mit RegEx.

Zitat* Soll der numerische Anteil eines solchen Schlüssels irgendwann auch wieder auf 1 zurückgesetzt werden (z. B. wenn der Schlüssel eine Jahreszahl enthält wie in 2020-00001?
Nein, "XY" steht für eine feste Zeichenfolge.

Ich verwende Microsoft Office Professional plus 2010

DF6GL

Hallo,

langer Rede kurzer Sinn:

In einem Standardmodul:


Public Function calcLfdnr(fld As String) As String
    Static i As Long, strPrefix As String
    strPrefix = Left(fld, 7)
    calcLfdnr = strPrefix & Right(String(4, "0") & i, 4)
    i = i + 1
End Function


und Aufruf in einer Abfrage:


UPDATE Tabelle1 SET Tabelle1.Nummer_neu = calcLfdnr([Nummer_alt]);

nurmikss

Juni 25, 2020, 11:49:52 #4 Letzte Bearbeitung: Juni 25, 2020, 16:10:10 von nurmikss
Hallo Franz,

dein Vorschlag kommt der von mir erträumten Funktion schon sehr nahe.
Weil ich nur die ersten zwei Zeichen "XY" plus "-" als Präfix übernehmen will, habe ich eine kleine Änderung bei der Präfixlänge vorgenommen (nachträglich korrigiert von "3" auf "2").
Außerdem müssten bei "String" drei (statt 4) führende Nullen ausreichen, weil mindestens noch eine Ziffer hinzukommt, was zusammen mindestens auf die vorgegebenen 4 Stellen kommt:
Public Function calcLfdnr(fld As String) As String
    Static i As Long, strPrefix As String
    strPrefix = Left(fld, 2)
    calcLfdnr = strPrefix & "-" & Right(String(3, "0") & i, 4)
    i = i + 1
End Function
Ergebnis: XY-0000, XY-0001, ...

In folgenden Punkten passt die Funktion noch nicht:
- die Variable i beginnt mit dem Anfangswert von 0 statt mit 1. Wie kann ich einen Startwert vorgeben ?
- wenn ich den Aufruf aus irgend einem Grund erneut mache, zählt i weiter statt erneut von vorne, außer wenn ich die Access-Datei vorher schließe und erneut öffne (Das ist also eher ein Schönheitsfehler, schränkt aber eine spätere Wiederverwendbarkeit der Funktion ein).
- die Werte in Nummer_alt wurden ursprünglich nicht in aufsteigender Reihenfolge eingegeben, sondern durcheinander. In meiner Eingangstabelle hatte ich sie schon aufsteigend sortiert und in dieser Reihenfolge sollen auch die aufsteigenden Werte in Nummer_neu vergeben werden. Tatsächlich tut die Funktion das nicht, sondern vermutlich vergibt sie sie Werte entsprechend der ursprüngichen Eingabereihenfolge ! Wie können die neuen Werte unter Berücksichtigung der aktuellen Sortierfolge vergeben werden ?
Ich verwende Microsoft Office Professional plus 2010

crystal

Hallo,
mit STATIC bleibt der Wert der Variablen erhalten, wenn die Funktion beendet wird. (Erst) bei Access-Neustart wird sie initialisiert, also auf 0 (Null) gesetzt. Genau deshalb hatte ich vorgeschlagen, eine Public-Variable zu benutzen, die im Form_Load_Event initialisiert werden kann (oder direkt im Code oder im Direktfenster oder, oder).

Im Code wird der Wert erst am Ende hochgezählt. Würde man dies bereits am Anfang machen, beginnt die Zählung bei 1.
Eine andere Lösung wäre es eben, eine PUBLIC-Variable g_lCount im Modul-Kopf zu definieren und eine Funktion zu schreiben
public function SetStart(lngStart as long) as boolean
g_lCount = lngStart 'vorher vllt. noch prüfen
SetStart = true
end function

und dann in
Public Function calcLfdnr() As String

    g_lCount = g_lCount + 1
    calcLfdnr = "XY-" & Right(String(3, "0") & g_lCount, 4)
End Function

Anm.: die Funktion braucht eigentlich keinen Wert übergeben zu bekommen (macht nur Sinn, wenn der Prefix sich ändern würde)...

Aufruf z. B. im Direktfenster:
? SetStart(200)  -> gibt True zurück
? calcLfdnr      -> gibt "XY-0201" zurück

zur Sortierung:
du musst in deiner Abfrage so sortieren, wie du es wünschst. Die Funktion zählt die Nummer in der Reihenfolge hoch, wie sie Datensätze bekommt, also: in der Abfrage selbst sortieren.

Grüße,
crystal
Wer Fehler in meinen Antworten findet, darf sie behalten, muss sie aber kommentieren. ;-)
Dies ist keineswegs arrogant gemeint, sondern soll nur unterstreichen, dass meine Antworten - natürlich - nicht immer fehlerfrei sind und sein können.
Devise: bitte immer erst selbst probieren!

DF6GL

Hallo,

1) ich gehe davon aus, dass die Funktion nur einmalig gebraucht wird. Eine laufende Nr. im Normalbetrieb muss eh anders behandelt werden.

2) "die Funktion braucht eigentlich keinen Wert übergeben zu bekommen "   tatsächlich? Das stelle ich in Frage.



nurmikss

Ich habe Eure Vorschläge wie folgt umgesetzt:

Ausgangspunkt waren Werte in Nummer_alt wie XY Nr. 010007,  XY Nr. 001001, ...

Modulweit geltende Variable für die laufende Nummer definieren (im Modulkopf, für den Anfangswert)
Dim i As Long
Funktion zum Setzen des Anfangswerts (Aufruf z.B. im VBA-Direktfenster, hier z.B. 1: ? setstart(1)  )
Public Function SetStart(lngStart As Long) As Boolean
i = lngStart 'hier könnte eine Prüfung der Eingabe ergänzt werden
SetStart = True
End Function

In der bereits beschriebenen Funktion habe ich die Addition am Ende belassen, weil oben der Anfangswert ja explizit gesetzt wurde.
Wenn man (wie ich für meine aktuelle Aufgabe) genau weiß, wie der Präfix aussieht, könnte man sich die Übergabe eines Feldes und das Extrahieren des Präfixes ersparen. Ich habe es trotzdem so gelassen, weil die Funktion mit der Übergabe universeller einsetzbar ist.
Public Function calcLfdnr(fld As String) As String
    Static i As Long, strPrefix As String
    strPrefix = Left(fld, 2)
    calcLfdnr = strPrefix & "-" & Right(String(3, "0") & i, 4)
    i = i + 1
End Function

Die Tabelle muss in der Änderungsabfrage sortiert an das UPDATE übergeben werden, sonst erfolgt die Nummerierung entlang der ursprünglichen Eingabereihenfolge:
UPDATE
(select Tabelle1.Nummer_alt, Tabelle1.Nummer_neu
FROM Tabelle1 order by Nummer_alt)  AS sortierte_Tabelle
SET Tabelle1.Nummer_neu = calcLfdnr([Nummer_alt]);
Ergebnis: XY-0001, XY-0002, ...

Weiß einer von Euch, wo der "Thema gelöst" Button versteckt ist ?

Ich verwende Microsoft Office Professional plus 2010

DF6GL

Hallo,


Zitatkönnte man sich die Übergabe eines Feldes und das Extrahieren des Präfixes ersparen


Nein, das könnte man nicht.  Wenn kein Argument angegeben ist, vermutet der SQL-Expression-Service, dass ein einmaliger Aufruf der Funktion am Anfang der Abarbeitung der Abfrage genügt und damit für alle weiteren DS überflüssig ist. 


Es stünde in allen DS  die erste(!) lfdNr drin.

Zudem müsste der Code angepasst werden.


ebs17

ZitatWie kann ich bei einer sortierten Tabelle ein leeres Textfeld mit fortlaufenden Nummern füllen ?
Auf eine Tabellensortierung sollte man sich nicht verlassen.
Ansonsten ist es einfach und effizient, die Tabelle geeignet sortiert in ein Recordset zu laden mit nur den benötigten Feldern (schlankes Recordset) und in einem einfachen Durchlauf die Nummerierung zu setzen.
Mit freundlichem Glück Auf!

Eberhard