Problem bei Sortierreihenfolge 'Select ... into table'

[Hpseel]

Hallo,
Ich verwende Access 2016.

Bei einem 'Select ... Order by KST' sortiere ich die Datensätze nach der Kostenstellen-Nr. (Kst).
Diese ist historisch so gestaltet, dass sie eine Zahl im Textformat enthält mit nicht
einheitlicher Stellenzahl (mal 3, mal 4, mal 5 Stellen), siehe auch Bild).

Wenn ich nur das blanke Select verwende, kommt die "richtige" Reihenfolge (im Bild ganz links).

Mache ich jedoch ein 'Select ... into table ...order by Kst'
ist die Reihenfolge in der Tabelle immer wieder unterschiedlich (Bild Mitte und rechts).

Kennt jemand dieses Phänomen und weiss eine Abhilfe?

Womöglich kann man die "Regeln" beim Sortieren von Zahlen in Textform irg.wo einstellen?

Vielen Dank für Tipps! HPS

[MzKlMu]

Hallo,
erstelle mal eine Abfrage mit der richtigen Sortierung und verwende dann diese Abfrage für das 'Select ... into table ...' (ohne das Order By).

PS:
Wenn Du Zahlen als Text hast, werden diese auch nach Text sortiert.

[Knobbi38]

Womöglich kann man die "Regeln" beim Sortieren von Zahlen in Textform irg.wo einstellen?

Nein, eine solche Einstellung gibt es nicht und bei gleicher Sortierreihenfolge sollten die Ergebnisse gleich sein. Ohne Angabe einer Sortierreihenfolge ist das Ergebnis unbestimmt.

Knobbi38

[PhilS]

Womöglich kann man die "Regeln" beim Sortieren von Zahlen in Textform irg.wo einstellen?

Auf technischer Datenbankebene gibt es entweder Zahlen oder Text. "Zahlen in Textform" sind nur Text und werden wie Text sortiert.
Ich habe in dem Text Logical numerals sorting in Access with VBA eine Windows-API-Lösung vorgestellt, wie man  "Zahlen in Textform" als Zahlen sortieren kann.

Wenn du eine Abfrage erstellen kannst, die dir die gewünschte Reihenfolge liefert könntest du diese verwenden um die Daten in eine neue Tabelle einzufügen, die eine zusätzliche Auto-Wert Spalte hat. der Autowert sollte dann eigentlich deine Reihenfolge abbilden.

[Knobbi38]

@PhilS

Der OP vergleicht die Sortierung eines "SELECTS" ohne "ORDER BY" mit einem "SELECT INTO" mit einer "ORDER BY" Klausel. Das hatte ich zunächst auch überlesen.

Bei einem "SELECT" ohne "ORDER BY" ist aber eine "richtige" Sortierung für das Ergebnis eben gerade nicht gewährleistet und muss deshalb, wie der OP hier bemerkt hat, nicht übereinstimmen. 

Gruß Knobbi38

[Hpseel]

Vielen Dank schon mal für eure Antworten!

Ich vermute ein anderes Problem: Beim ersten Aufruf der Abfrage macht er die Sortierung richtig, bei den folgenden Aufrufen stimmt sie nicht mehr. Egal, ob ich die "Into-Tabelle" vorher lösche oder ob er die Löschung ankündigt und selbst durchführt.

Kann das ein Access-Problem sein?

VG HPS

P.S. Obiges passiert auch bei der vorgeschlagenen Schachtelung:

Select * into Test
from (Select * from KOST where Status = "aktiv" Order by KSt);

[Knobbi38]

Hallo HPS,

die Schachtelung wird nicht benötigt (getestet mit ACC2K10) und zwei aufeinanderfolgende Aufrufe sollten keine unterschiedlichen Ergebnisse liefern. Da stimmt etwas anderes nicht.

Knobbi38
 

[PhilS]

Beim ersten Aufruf der Abfrage macht er die Sortierung richtig, bei den folgenden Aufrufen stimmt sie nicht mehr.

Bei einer Anfügeabfrage ist doch nur der erste Aufruf relevant?
Die Zieltabelle sollte dann, wie geschrieben, eine Auto-Wert haben, nach dem du verlässlich sortieren kannst.

[Knobbi38]

@PhilS

Beachte den Unterschied zwischen "SELECT INTO ..." und "INSERT INTO SELECT ..."

Der generierte Autowert dürfte bei einer Erstellungsabfrage nicht hilfreich sein.
Btw. das Ergebnis einer Erstellungsabfrage mit einem Autowert im Zusammenhang mit einer Sortierung ist schon erstaunlich, zumindest dürfte es unerwartet sein.

Knobbi38

[PhilS]

Beachte den Unterschied zwischen "SELECT INTO ..." und "INSERT INTO SELECT ..."

Das hatte ich in dem Moment tatsächlich übersehen.
Wenn das ganze eine einmalige Aktion sein soll, wäre es ja kein Problem erstmal eine Tabellenerstellungsabfrage ohne Daten laufen zu lassen, dann in der erstellen Tabelle die Auto-Wert-Spalte manuell zu ergänzen, und zuletzt die Daten mittels Anfügeabfrage zu kopieren.

@Hpseel, wenn das ganze eine wiederkehrende Aktion ist, und evtl. auch generell, wäre es ein sinnvoller Weg eine neue Tabelle für die Kostenstellen und deren gewünschte Sortierreihenfolge zu erstellen. Mit einem Join auf diese Tabelle und einem entsprechenden ORDER BY kannst du in jeder Abfrage zuverlässig sortieren.

[Hpseel]

Der vorgeschlagene "Workaround" von PhilS funktioniert:

1 Erstellen einer Zieltabelle gleicher Struktur mit zusätzlichem ID-Feld (Autowert)
2 Anfügeabfrage erstellen und ausführen
3 Die Zieltabelle enthält die Daten in der richtigen Sortierreihenfolge !!!

Vor erneuter Ausführung muss man die Zieltabelle natürlich leeren (Delete * from Table)

Die erneute Ausführung bringt wieder die richtige Sortierreihenfolge!!!

Mein Resümee: Wir alle kennen das Problem, wenn Teile der Software nicht von der "Vorgeschichte" unabhängig sind.
Das scheint mir hier der Fall zu sein.
Ich denke, Access 2016 wird diesbezüglich kein Update mehr erhalten.
Immerhin kennen wir durch die Diskussion einen Workaround.

Vielen Dank an alle Diskussionsteilnehmer !!!
HPS

[PhilS]

Die Zieltabelle enthält die Daten in der richtigen Sortierreihenfolge !!!

Jein.
Eine Tabelle ist eine unsortierte Datenmenge! <- Das bitte unbedingt verinnerlichen!
Ein verlässliche Sortierung entsteht durch ein ORDER BY in einer Abfrage. Aber nur für die Abfrage selbst. Wenn du die Ergebnisse der Abfrage in eine Tabelle einfügst, gilt weiterhin dass eine Tabelle eine unsortierte Datenmenge ist.

Wenn du einen Auto-Wert als Primärschlüssel definierst (das hatte ich bei meinem Workaround vergessen), dann sind intern die Daten nach diesem Primärschlüssel sortiert und werden auch in dieser Reihenfolge ausgegeben solange es keinen Grund für Access gibt, eine andere Sortierung anzuwenden.
Deshalb gilt auch hier, dass eine verlässliche Sortierung nur durch ein ORDER BY bei der Abfrage der Daten erreicht wird.

[Bitsqueezer]

Hallo,

insbesondere, wenn Du mit SELECT..INTO arbeitest, ist die so entstandene neue Tabelle immer ein Heap (=Haufen). Mit jeder Ausführung von SELECT auf diese Tabelle kann die Reihenfolge anders sein, außer Du fügst ein ORDER BY hinzu. Das ORDER BY in SELECT..INTO kannst Du Dir schenken, das ist ohne Bedeutung. Denn die Daten werden beim Abfragen zwar in der gewünschten Reihenfolge geholt und dann geschrieben, aber da die neue Tabelle eben ein Heap ist, gibt es keine Garantie für die Ausgabereihenfolge bei einem SELECT darauf.

Wie Phil schon schrieb: Fügst Du danach einen Primary Key hinzu auf ein Feld, das eindeutig sein muß (eben i.d.R. eine AutoID), wird die Tabelle physikalisch in dieser Reihenfolge geschrieben und auch bei einem SELECT ohne ORDER BY i.d.R. in dieser Reihenfolge ausgegeben. Das ist aber keine Garantie. Eine korrekte Sortierung erhält man ausschließlich mit ORDER BY erst bei der Abfrage der Daten.

Bitte beachte, daß Access die physikalische Reihenfolge aufgrund des PKs nur bei "Compact & Repair" neu erstellt, da Access eine passive Datenbank ist und nichts ohne Anweisung macht. Wenn Du beispielsweise ein ID-Feld ohne AutoID hast, einen Datensatz in der Mitte (nach ID-Reihenfolge) löschst und einen neuen mit der gleichen ID schreibst, dann kann es sein, daß bei einem SELECT die ID am Ende ausgegeben wird, wenn Du nicht vorher "Compact & Repair" machst.
Der Grund ist simpel: Im Gegensatz zu älteren Datenbanken, die eine exakt gleiche Länge eines Datensatzes hatten, kann in modernen Datenbanken jeder Datensatz eine andere Länge haben. Daher "paßt" der neue u.U. nicht mehr dort, wo der alte mal war.

Das ist kein Access-Bug, das ist by design und nicht nur in Access so. Auch die "großen" Datenbanken wie SQL Server und Oracle usw. verhalten sich so.

Wenn Du textuelle Kostenstellen hast, solltest Du entweder immer eine gleiche Anzahl führender Nullen hinzufügen (also so, daß alle die gleiche Länge haben) oder generell die Kostenstellen in einen Integer umwandelst, sofern Du nicht welche mit Buchstaben darin hast. Bei einer Zahl ist einerseits die Performance besser, wenn es an's Suchen und Sortieren geht, andererseits spart es auch Platz im Datensatz.

Gruß

Christian