Office: (Office 2016) Zusammenhängende Zeitspannen ermitteln

Guido S · 8. August 2018

\@ebs17

Ich habe gerade mal deinen neuen Vorschlag ausprobiert. Leider funktioniert er nicht. Statt zusammenhängende Zeiträume zusammen zu fassen "Von" - "Bis", ist das Ergebnis:
Pers_Nr BaustNr Von Bis
113 2667 08.01.2018 08.01.2018
113 2667 09.01.2018 09.01.2018
113 2667 10.01.2018 10.01.2018
113 2667 11.01.2018 11.01.2018

Gruß
Guido

Guido S · 8. August 2018

Ich habe aber noch ein weiteres Problem.
Wie gesagt versuche ich eine Baustellenplanung zu basteln. (Als Gantt-Balken)
Dies versuche ich über eine Dateline (also alle Daten mit Separator nebeneinander).
Das klappt auch soweit ganz gut.
Wenn ich aber die (mit eurer Hilfe) erzeugten Daten umwandeln will hängt sich Access auf.

Hier der Code zum erzeugen der Line:

Code:

Public Function ADOConcatColumn_VB(ByVal X As Integer, Optional ByVal strSeparator As String = "| ") As Variant
Dim sQl As String

sQl = "SELECT tbl_Kalender.kTag, tbl_StdVonBis.Lieferantennummer From tbl_StdVonBis, tbl_Kalender WHERE (((tbl_StdVonBis.[Lieferantennummer]) = " & X & ")" & " AND ((tbl_Kalender.kTag) Between [tbl_StdVonBis]![Von] And [tbl_StdVonBis]![Bis]));"
Debug.Print X
Const adOpenForwardOnly = 0
Const adCmdText = 1
Const adLockReadOnly = 1
Const adUseClient = 3

Static rs As Object 'ADODB.Recordset nur 1x erstellen

On Error GoTo X

If rs Is Nothing Then
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseClient
End If

rs.Open sQl, , adOpenForwardOnly, adLockReadOnly, adCmdText

If Not (rs.EOF And rs.BOF) Then
ADOConcatColumn_VB = rs.GetString(, , ":", strSeparator)
ADOConcatColumn_VB = Left$(ADOConcatColumn_VB, Len(ADOConcatColumn_VB) - Len(strSeparator))
End If

rs.Close
Exit Function

X:
ADOConcatColumn_VB = CVErr(6001)
End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Dann die dazugehörige Abfrage:

Code:

SELECT X.Pers_Nr, X.Lieferantennummer, ADOConcatColumn_VB([Lieferantennummer]) AS line
FROM (SELECT DISTINCT [Pers_Nr], [Lieferantennummer] FROM tbl_StdVonBis WHERE Von > #12/31/2017#) AS X;
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Bei kleinen Datenmenge funktioniert es aber dauert.
Gibt es da Verbesserungsmöglichkeiten?

Gruß
Guido

ebs17 · 8. August 2018

Kannst Du eine DB mit einem Teilauschnitt der Tabelle mit Realdaten hochladen? Eventuell wirkt da eine ungenannte Zusatzkomponente, z.B. Zeitanteile beim Datum.

Ansonsten zeigt das anhängende Bild das Funktionieren der Bildung von Gruppen, die jeweis einen Zeitraum darstellen.
Du könntest auch die obige Abfrage verkürzen (z.B. auf die Unterabfrage alleine), um herauszufinden, warum pro Tag eine Gruppe gebildet wird.

Aber auch Danke für den Test. Ein Vergleich der Laufzeiten wird dann sicher interessante Aufschlüsse liefern.

Guido S · 8. August 2018

Das ist die Ausgangstabelle
https://www.ms-office-forum.net/foru...1&d=1533802662

Das ist das Ergebnis der Abfrage

https://www.ms-office-forum.net/foru...1&d=1533802833

ebs17 · 8. August 2018

Diese Bilder verraten weder konkrete Ausgangsbedingungen noch Deine konkrete Umsetzung der Abfrage.

Guido S · 8. August 2018

So hab ich die Abfrage umgesetzt:

Code:

SELECT Pers_Nr, Lieferantennummer, Min(Datum) AS Von, Max(Datum) AS Bis
FROM (SELECT Pers_Nr, Lieferantennummer, Datum, Datum - QueryIncrement(Datum, 1, Lieferantennummer,Pers_Nr,Datum) AS Diff
FROM tbl_Baustellen
ORDER BY Pers_Nr, Lieferantennummer, Datum ) AS X
GROUP BY Pers_Nr, Lieferantennummer, Diff;
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

tbl_Baustellen ist auf dem ersten Bild

ebs17 · 8. August 2018

Wie angedeutet: Zeitanteile beim Datum - die durchaus über Anzeigeformate ausgeblendet sein können - können ihre Wirkungen erzielen.

Zur Beurteilung eines Weines schaut man nicht nur auf das Etikett (könnte ja ein falsches sein), sondern man muss schon in die Flasche hineinschauen, schnüffeln, schmecken, ...

Guido S · 8. August 2018

Es können eigentlich keine Zeitanteile da sein.
Der Datumswert wird in einer anderen Funktion zusammengebaut.

Code:

Function StdVerschr()
Dim db As Database
Set db = CurrentDb
Set std = db.OpenRecordset("qry_Stundenerfassung", dbOpenDynaset)
Do While Not std.EOF
interv = Day(DateSerial(Right(std![Monat/Jahr], 2), Left(std![Monat/Jahr], 2) + 1, 1 - 1))
Jahr = Right(std![Monat/Jahr], 2)
Monat = Left(std![Monat/Jahr], 2)
For i = 1 To interv
Tag = Format(i, "00")
tag1 = Tag & " Text"
If std(Tag).Name = Tag Then
With CurrentDb().OpenRecordset("tbl_StdBuchung", dbOpenDynaset, dbAppendOnly)
.AddNew
!Pers_Nr = std!Pers_Nr
!Lieferantennummer = std!Lieferantennummer
!Datum = Tag & "/" & Left(std![Monat/Jahr], 2) & "/" & Right(std![Monat/Jahr], 2)
!Stunden = std(Tag)
!Eintrag = std(tag1)
.Update
End With
End If
Next i
std.MoveNext
Loop
End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

und aus dieser Tabelle hole ich mir über eine Abfrage die Werte raus die ich brauche und schreibe sie in die tbl_Baustellen
https://www.ms-office-forum.net/foru...1&d=1533805490

ebs17 · 8. August 2018

Der Datumswert wird in einer anderen Funktion zusammengebaut.
Code:
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

!Datum = Tag & "/" & Left(std![Monat/Jahr], 2) & "/" & Right(std![Monat/Jahr], 2)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Zusammensetzung von Elementen per & erzeugt Text, der zwar womöglich aussieht wie ein Datum, aber ein anderes Verhalten zeigt als eine Zahl. Bei Datentyp Date wird intern Double, also eine Zahl, verwendet.
Ergo: Das Konvertieren wirkt etwas wild.

... und schreibe sie in die tbl_Baustellen
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Der Vorgang ist noch unbekannt.

Es können eigentlich keine Zeitanteile da sein.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Realität ist desweilen anders als eigene Logik und Erwartung. Also würde man richtig nachschauen (Eigenschaften des Datumsfeldes) und/oder prüfen
Code:

SELECT Datum, Datum * 1 AS Zahlenwert
FROM tbl_Baustellen
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Ich bin nicht weiter interessiert, hin- und herzudiskutieren mit Bröckcheninformationen. Mit der oben nachgefragten Tabelle (in DB, nicht als Bild) hätte ich alle nötigen Informationen im Stück und könnte unmittelbar probieren. Bilder male ich mir nur in Tabellen ab, wenn ich ein sehr hohes Eigeninteresse habe.

Guido S · 9. August 2018

Ich hab dir mal die Tabelle und die Abfrage angehangen.

Die erste Version die ich von euch bekommen habe funktioniert einwandfrei, ich hatte deinen neuen Vorschlag nur Interessehalber mal ausprobiert.

Interessanter wäre für mich, warum sich Access bei erzeugen der Dateline aufhängt.

ebs17 · 9. August 2018

Interessanter wäre für mich, warum sich Access bei erzeugen der Dateline aufhängt.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Die beiden globalen Variablen müssen schon auch vorhanden sein. Mit gesetztem Option Explicit wäre Dir das selber aufgefallen.

Zum obigen Fehler:
Code:

... , Datum-QueryIncrement(Datum,1,Lieferantennummer,Pers_Nr,Datum) AS Diff ..
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Das Datum als Argument war zuviel. Im oben erwähnten ParamArray erfasst man die Felder, in deren Kombination nummeriert wird. Ist da das Datum drin, kann nur immer (bzw. meist, siehe unten) bis 1 gezählt werden. Das macht dann eine Zusammenfassung unmöglich und war mein Fehler.

Deine Tabelle enthält übrigens Duplikate. Damit kann meine letzte Variante nicht umgehen wegen der durchgehenden laufenden Nummerierung.
Wenn die Duplikate nicht gewünscht sind, sollte man sie aus der Tabelle entfernen und zukünftig in der Form abwehren, dass man den gesetzten Mehrfelderindex eindeutig macht.

Übrigens ist es schön, dass Du den Hinweis auf den Mehrfelderindex wahrgenommen und umgesetzt hast. Das kann nicht jeder.

Office: (Office 2016) Zusammenhängende Zeitspannen ermitteln

Zusammenhängende Zeitspannen ermitteln

Zusammenhängende Zeitspannen ermitteln

Zusammenhängende Zeitspannen ermitteln

Zusammenhängende Zeitspannen ermitteln

Zusammenhängende Zeitspannen ermitteln - Similar Threads - Zusammenhängende Zeitspannen ermitteln

Bereich von Excel nach Word mit zusammenhängenden Passagen übergeben

Zusammenhängende Informationen aus einer Zeile in mehrere Spalten

3Grösste Werte aus nicht zusammenhängenden Bereich addieren

Zusammenhängende Zeiträume in einer Spalte erkennen und Start- sowie Enddatum ermitteln

zusammenhängende leere Zellen zählen

Text zusammenhängend, ohne Leerzeichen

zusammenhängende Daten kennzeichnen