Office: (Office 2016) Zwischensummen

Hallo zusammen, ich möchte aus dieser Tabelle ermitteln, wie lange welche Person in welchem Land war. Dabei müssen eventuell mehrere Aufenthalte im gleichen Land zusammengefasst werden. Und es kann vorkommen, dass eine Person noch mal in ein Land zurückkehrt (z.B. Frank nach Deutschland). Dann sollen aber nicht alle Zeiten von Frank in Deutschland zusammengefasst werden. Ich habe mit DomSumme angefangen, bin aber nicht zum Ziel gekommen. Danke vorab für Eure Lösungen. Gruß, Frank

 

Moin Frank,
vielleicht solltest Du als Erstes die Zeiträume erfassen (bis-von+1) und dann in einer zweiten Abfrage Dein DomSumme anwenden.
Die Kriterien, wann eine Summe nicht erstellt werden soll, müsstest Du mit If/Then/Else ausschließen.
Gruß
Doming

 

Ich korrigiere: Das Summieren der Zeiträume geht locker in der ersten Abfrage

Code:

SELECT Tabelle1.PersNr, Tabelle1.VName, Sum([bis]-[von]+1) AS Zeitraum, Tabelle1.Land
FROM Tabelle1
GROUP BY Tabelle1.PersNr, Tabelle1.VName, Tabelle1.Land;

Um allerdings Deutschland auszuklammern, ist etwas mehr Aufwand von Nöten.

 

Hallo Doming,

vielen Dank für Deine schnelle Antwort. Die kumulierten Werte für die Dauern in Jahren bekomme ich schon ganz gut hin:

AufenthaltNr: (Select Count (*) from [DATEN] as Temp WHERE [Temp].[lfdNr] < [DATEN].[lfdNr] AND [Temp].[PersNr]=[DATEN].[PersNr])+1



Was mir nicht gelingt, ist die Gruppierung für die Länder mit der Besonderheit, dass später noch mal vorkommende Länder nicht mit gruppiert werden dürfen. Die Aufenthalte von Frank in Deutschland und die Aufenthalte von Michael in Spanien sollen also nicht alle zusammengefasst werden, sondern es sollen jeweils zwei Aufenthaltsgruppen dargestellt werden

 

Hallo Frank,
stell mal eine Datenbank mit den wichtigsten (anonymisierten) RohDaten als Beispiel hier rein und dann vielleicht eine Tabelle, wie das Ergebnis nachher aussehen soll.
Woher soll die Abfrage wissen, zu welcher Aufenthaltsgruppe der Datensatz gehören soll?

 

Hallo Doming,
hier die Ausgangstabelle:
AUSGANGSDATEN
lfdNr PersNr Name Land von bis
1 01 Frank Deutschland 03.07.2003 24.06.2004
2 01 Frank Deutschland 25.06.2004 17.01.2006
3 01 Frank USA 18.01.2006 22.03.2010
4 01 Frank Frankreich 23.03.2010 15.01.2012
5 01 Frank Frankreich 16.01.2012 30.06.2015
6 01 Frank Frankreich 01.07.2015 22.08.2018
7 01 Frank Deutschland 23.08.2018 05.11.2021
8 01 Frank Deutschland 06.11.2021 07.07.2023
9 02 Susanne Deutschland 17.09.2011 08.01.2016
10 02 Susanne Frankreich 09.01.2016 07.07.2023
11 03 Michael Portugal 01.01.2009 31.12.2011
12 03 Michael Spanien 01.01.2012 17.06.2014
13 03 Michael Spanien 18.06.2014 31.12.2018
14 03 Michael England 01.01.2019 13.11.2021
15 03 Michael Spanien 14.11.2021 22.04.2022
16 03 Michael Spanien 23.04.2022 07.07.2023

und hier die Ergebnistabelle:
GEWÜNSCHTES ERGEBNIS
lfdNr PersNr Name Land von bis
1 01 Frank Deutschland 03.07.2003 17.01.2006
3 01 Frank USA 18.01.2006 22.03.2010
4 01 Frank Frankreich 23.03.2010 22.08.2018
7 01 Frank Deutschland 23.08.2018 07.07.2023
9 02 Susanne Deutschland 17.09.2011 08.01.2016
10 02 Susanne Frankreich 09.01.2016 07.07.2023
11 03 Michael Portugal 01.01.2009 31.12.2011
12 03 Michael Spanien 01.01.2012 31.12.2018
14 03 Michael England 01.01.2019 13.11.2021
15 03 Michael Spanien 14.11.2021 07.07.2023

Mit VBA wäre das wahrscheinlich einfacher, aber ich brauche eine SQL-Lösung. Danke, dass Du dran bleibst.

 

anbei die Datenbank

 

Meine Original-Tabelle hat übrigens mehr als 45.000 Datensätze, deshalb der ganze Aufwand

 

Hallo,
da hast Du mir eine harte Nuss hingelegt, mit SQL-Abfragen habe ich mich bisher nur wenig beschäftigt, ich wäre da mit vba drangegangen.
Aber wenn Andyfau oder jemand anderes nicht schneller ist, werde ich sicherlich noch irgendwann eine Lösung präsentieren.
Gruß
Doming

 

Hallo,
mit SQL bin ich auch nicht soooo vertraut, aber das Thema lässt sich auch über Abfragen lösen.
Die Herausforderung ist, die Aufenthalte in gleichen Ländern, die aber durch Aufenthalte in anderen Ländern unterbrochen wurden, getrennt zu gruppieren, um dann die jeweilige Dauer auszugeben. D.h. es muss "künstlich" ein Gruppenkriterium in den einzelnen Datensätzen erzeugt werden. Den Weg dahin zeigt die erste Abfrage "Gruppenbildung". Der Trick dabei ist, jeweils den vorausgegangenen Satz auf Gleichheit von PersNr und Land zu prüfen und die lfdNr dann als Gruppennr zwischenzuspeichern und im Folgesatz dann wieder einzutragen wenn PersNr und Land gleich sind. Diese Abfrage ist dann wiederum die Grundlage der Abfrage "Ergebnis", die nur noch zusammenfasst und sortiert.

 

ich habe tatsächlich gestern und heute daran getüftelt, wie ich fehlende IDs umgehen kann. Wenn nämlich mittendrin Löcher in der ID-Reihenfolge sind, kann man lfdNr ±1 schon vergessen.
Also war meine erste Abfrage dergestalt, dass ich ein Feld mit dem Folgedatensatz generiere, auf das ich dann die Personen- und Landesgleichheit prüfe. Gehakt hat es da, wo es mehr als zwei Datensätze mit demselben Land gibt, wie weit will man schachteln?

 

Wenn die lfdNr Lücken hat, ist es ja keine laufende Nummer mehr. ;-) Also ist das in jedem Fall eine Voraussetzung, die ggf. ja auch leicht hergestellt werden kann. Zuerst hatte ich auch eine Schachtelung, was natürlich nicht funktioniert, weil man die Tiefe nicht kennt. Des Rätsels Lösung ist die sequentielle Abarbeitung mit der Fortschreibung der jeweils ersten lfdNr. Das Ganze wäre natürlich mit VBA wesentlich einfacher. Jetzt haben wir zwar eine Abfragelösung, aber immer noch keine reine SQL-Lösung. Wäre interessant, wenn sich noch ein SQL-Profi finden würde, der das nochmal umsetzen kann.

 

Hallo Doming & andyfau,
da habt ihr beide ja ganz schön gewirbelt und Euch mit dem Hauptproblem der Gruppenbildung bei wiederholten Einsätzen beschäftigt. Und ihr habt alles gut dokumentiert, so dass ich das gut nachvollziehen, nachprogrammieren und ausprobieren kann. Ich hatte einen Bandscheibenvorfall und werde erst in den nächsten Tagen zum Ausprobieren kommen. Dann melde ich mich natürlich noch mal bei Euch. VIELEN DANK aber schon mal

 

Doming & andyfau: WOWOHAAAHHHH!!!!!!!!!!
Diese beiden wilden Abfragen mit den doch nur sehr schwer nachvollziehbaren DomWert-Funktionen führen tatsächlich zu dem gewünschten Ergebnis. Wie könnt ihr auf solche verrückten Lösungen kommen? Kann man das lernen? Wie kann ich mich erkenntlich zeigen? Ich bin das erste Mal in so einem ACCESS-Forum aktiv und kenne die Spielregeln hier nicht. Aber auf alle Fälle nochmal vielen Dank an Euch.

 

Hast Du schon, mit der Rückmeldung, das alles funktioniert. (Wobei ich mich dieses Mal nicht angesprochen fühle, denn die Lösung kam nicht von mir.)
Gehe davon aus, dass niemand, der im Forum antwortet, eine materielle Entlohnung dafür bekommt. Für mich ist es die Herausforderung, ein bestehendes Problem zu lösen, denn nur so kann man lernen.
Sich selbst theoretische Fragen zu stellen ("lass auf dem Bildschirm <Hallo Welt> erscheinen"), ist da eher langweilig.