Access-o-Mania

Hallo zusammen,

ich habe folgendes Problem, vor dem ich stehe.
In meinem Bericht werden verschiedene Arbeitszeiten aufgelistet..
Ich habe hierzu zwei Bilder angehangen.

Wenn nun zwischen der Endzeit vom Vorherigen Auftrag und der Startzeit vom nächsten Auftrag eine Differenz von mindestens 10 Minuten vorliegt, soll im Bericht für diese Zeit eine neue Zeile mit dem Eintrag Pause erstellt werden. Diese soll zwischen den beiden Datensätzen erscheinen.

Leider habe ich keinen Ansatz, wie ich dieses Problem lösen soll. Eventuell schon in der Abfrage davor?
Kann mir jemand näher bringen, wie man dieses angeht?

Ich danke euch und wünsche euch ein schönes Wochenende.

Viele Grüße,

John

Gegeben sei

data start ende 18:54:00 18:59:00 19:15:00 19:24:00 20:33:00 20:40:00 20:47:00 20:53:00 23:19:00 23:28:00

SELECT DateAdd( 'n',  1, prev_ende ) AS start,
       p_start                       AS ende,
       'Pause'                       AS Info
FROM   data AS d
       INNER JOIN ( SELECT ( SELECT TOP 1 ende
                             FROM   data
                             WHERE  ende < d0.start
                             ORDER  BY 1 DESC) AS prev_ende,
                             DateAdd( 'n', -1, d0.start ) AS p_start
                    FROM   data AS d0
                    WHERE  CVDate( start - ( SELECT TOP 1 ende
                                             FROM   data
                                             WHERE  ende < d0.start
                                             ORDER BY 1 DESC ) ) > #12/30/1899 0:10:0#
                  ) AS v
               ON d.ende = v.prev_ende
UNION  ALL
SELECT start,
       ende,
       'Arbeit'
FROM   data
ORDER  BY 1

Egebnis start ende Info 18:54:00 18:59:00 Arbeit 19:00:00 19:14:00 Pause 19:15:00 19:24:00 Arbeit 19:25:00 20:32:00 Pause 20:33:00 20:40:00 Arbeit 20:47:00 20:53:00 Arbeit 20:54:00 23:18:00 Pause 23:19:00 23:28:00 Arbeit

Hallo und vielen Dank für deine Antwort,

leider kann ich mit der Antwort nicht viel anfangen.
Durch die Länge des Codes bin ich eher verwirrt, als dass ich hier was dazu lernen würde.

Könntest du eventuell erklären, was wofür gut ist?
Somit lerne ich etwas dazu und schreibe nicht nur ab. Vielleicht gehöre ich dann eventuell auch mal zu den Antwortenden :)

Vielen Dank und einen schönen Abend :)

Auf oberster Ebene liegt eine Vereinigungsabfrage (https://dbwiki.net/wiki/Vereinigungsabfrage) vor, die nach der ersten Spalte sortiert wird. Der interessante Abschnitt ist der erste, in dem die Pausenzeiten ermittelt werden.

Betrachte zunächst die Unterabfrage nach INNER JOIN. Es soll zunächst die das vorherige ende in prev_ende ermittelt werden. Dazu wird eine weitere Unterabfrage benötigt, weil der SQL-Dialekt von Access schon etwas angestaubt ist. In moderneren SQL-Dialekten kann man mittels CROSS APPLY oder LATERAL diesen Wert ermitteln. Was Du diesem DBFiddle (https://dbfiddle.uk/?rdbms=postgres_10&fiddle=0d2f89a43562253fa4d0ee14bf13e19a) entnehmen kannst.

Es soll zuerst das ende gefunden werden, für das gilt, dass es kleiner dem start der übergeordneten Abfrage d0 ist. Das Prädikat TOP 1 sorgt dafür dass nur 1 Datensatz (vorausgesetzt, die Werte sind einmalig) ausgegeben wird. Im Access-Abfrageentwurf kann man das unter der Eigenschaft Spitzenwerte wiederfinden. Damit wir auch den richtigen Wert erhalten, wird absteigend nach der ersten und einzigen Spalte sortiert: ORDER BY 1 DESC gleichwertig wäre ORDER BY ende DESC. Das wird eine Ebene höher für die Verknüpfung benötigt.

Danach wird der Beginn der Pause als p_start berechnet, indem 1 Minute subtrahiert wird. Das wird später unser ende sein.

Jetzt wird noch die Bedingung aufgestellt, dass die Differenz zwischen den beiden Zeiten mindestens 10 Minuten betragen muss. Hierbei wird die Unterabfrage von zuvor nur noch einmal wiederholt.

Über ende von d0 (Ursprungstabelle) und über prev_ende der Unterabfrage v können wir nun den INNER JOIN verknüpfen.

Zu prev_ende (ich hätte enso gut auch ende aus d0 nehmen können) wird 1 Minute hinzuaddiert und in start umbenannt, das schon vorher berechnete p_start wird in ende umbenannt und es wird in einer Spalte info Pause als Text ausgegeben.

Der zweite Abschnitt hinter UNION ALL sollte ohne weitere Erklärung zu verstehen sein.

Hallo und vielen Dank erstmal.

So ich habe jetzt seit 16 Uhr versucht, deine Zeilen zu verwerten.
Leider ist es mir bisher nicht gelungen.

Ich habe folgende Abfrage, welche ich ausführe.
Hier müsste ich den Code integrieren, sodass er mir die neuen Datensätze anlegt und diese im Bericht ausführt, nehme ich mal an. Da aus dieser Abfrage auch der Bericht generiert wird.


Wenn ich es richtig verstanden habe, muss ich den Code doch wie folgt umsetzen:


SELECT DateAdd( 'n',  1, prev_ende ) AS start,
       p_start                       AS ende,
       'Pause'                       AS Info
FROM   [b]Daten[/b] AS d
       INNER JOIN ( SELECT ( SELECT TOP 1 [b]Endzeit[/b]
                             FROM   [b]Daten[/b]
                             WHERE  [b]Endzeit[/b] < d0.[b]Start_Uhrzeit[/b]
                             ORDER  BY 1 DESC) AS prev_ende,
                             DateAdd( 'n', -1, d0.[b]Start_Uhrzeit[/b] ) AS p_start
                    FROM   [b]Daten[/b] AS d0
                    WHERE  CVDate( [b]Start_Uhrzeit[/b] - ( SELECT TOP 1 [b]Endzeit[/b]
                                             FROM   [b]Daten[/b]
                                             WHERE  [b][b]Endzeit[/b][/b] < d0.[b]Start_Uhrzeit[/b]
                                             ORDER BY 1 DESC ) ) > #12/30/1899 0:10:0#
                  ) AS v
               ON d.[b]Endzeit[/b]= v.prev_ende
UNION  ALL
SELECT start,
       ende,
       'Arbeit'
ORDER  BY 1
FROM   [b]Daten[/b]


Leider funktioniert das nicht so richtig. Ich habe die DB mal hochgeladen, eventuell hab ich es ja auch falsch erklärt.
Vielleicht gibt Sie mehr Einblick.

https://www.dropbox.com/s/h016bws2lrilrka/Schichtzettel.accdb?dl=0 (https://www.dropbox.com/s/h016bws2lrilrka/Schichtzettel.accdb?dl=0)

Die Abfrage habe ich mal mit der jetzigen oder in eine neue Abfrage gepackt. Beides funktioniert leider nicht.
Ich bekomme immer wieder eine Paramterabfrage. Oder wäre es eventuell sinnvoll, diese Abfrage mit in die Abfrage_Schichtzettel zu packen?

Ich bedanke mich erstmal stets für deine Hilfe.
Es tut mit leid, wenn es sich als schwierig gestaltet, mir Hohlkopf das erklären zu müssen.

Deine Datenbank ist mit einem Passwort geschützt. :(

Entschuldigung, das Passwort lautet Schichtzettel.

Tschuldigung, daran hab ich natürlich nicht gedacht.

Ich habe als Ausgangsbasis diese Abfrage q1 erstellt:
SELECT Taxi_Nr,
       Auftragsnummer,
       Datum + Start_Uhrzeit AS Start,
       Datum + Endzeit + Abs( Start_Uhrzeit > Endzeit ) AS Ende
FROM   Daten
WHERE  Fahrer Is Not Null AND
       Auftragsnummer <> 'Pause' AND
       Datum Is Not Null AND
       Start_Uhrzeit Is Not Null AND
       Endzeit Is Not Null;

Die folgende Abfrage sollte dann je Taxi_Nr die Pause ausgeben.
SELECT d.Taxi_Nr,
       DateAdd( 'n', 1, prev_ende ) AS Start,
       v.prev_start AS Ende,
       'Pause' AS Info
FROM   q1 AS d
       INNER JOIN ( SELECT Taxi_Nr,
                           ( SELECT DISTINCT top 1 Ende
                             FROM   q1
                             WHERE  Ende < d0.Start AND
                                    Taxi_Nr = d0.Taxi_Nr
                             ORDER  BY 1 DESC ) AS prev_ende,
                           DateAdd( 'n', -1, Start) AS prev_start
                    FROM   q1 AS d0
                    WHERE  cvdate( Start - ( SELECT DISTINCT top 1 Ende
                                             FROM   q1
                                             WHERE  Ende < d0.Start AND
                                                    Taxi_Nr = d0.Taxi_Nr
                                             ORDER  BY 1 DESC ) ) >= #00:10:00# ) AS v
               ON ( d.Ende    = v.prev_ende ) AND
                  ( d.Taxi_Nr = v.Taxi_Nr )
UNION ALL
SELECT Taxi_Nr,
       Start,
       Ende,
       Auftragsnummer
FROM   q1
ORDER  BY 1, 2;

Hallo Lachtaube,

ich habe nun mal versucht, deine Abfrage in meine Abfrage einzubauen.
Hierzu habe ich eine Abfrage "Pausenzeiten" erstellt, welche von meiner Abfrage_Schichtzettel gespeist wird.

Die Abfrage an sich funktioniert auch.

Leider habe ich nun folgende Probleme:

Wenn ich den Bericht ausführe, der nun statt über Abfrage_Schichtzettel von Pausenzeit gespeist wird, werde ich nach den Felder der Abfrage_Schichtzettel gefragt.
Des Weiteren sind dann im Bericht alle Datensätze doppelt gelistet.

Ich habe die Differenz von 10 Minuten mal auf 15 Minuten hoch gesetzt.
Habe ich das so richtig gemacht? Hab ich auch die Implementierung deiner Abfrage in meine richtig gemacht?

Ich habe hier mal eine weitere Version der DB hochgeladen. Eventuell habe ich ja auch einen dummen Fehler gemacht.
https://www.dropbox.com/sh/62nywgo7q27e5w3/AACAD0X6UioWw6tnLlbzrRPba?dl=0

Vielen Dank für deine Geduld und Mühe mit mir Holzkopf.
Ich wünsche dir eine schöne restliche Woche.

Das klingt jetzt hart - aber Du hast ein Daten-Chaos vorliegen. Mehrere Fahrer in einem Taxi zu demselben Zeitpunkt, fehlende Endzeiten, fehlende Fahrer, Mitternachtproblematik.

Ich würde zuerst einmal die Daten so strukturieren, dass aus einer Tabelle hervorgeht, wann ein Fahrer in einem Taxi einen Dienst antritt und wann er ihn beendet. Dazu gibt es dann als Detaildatensätze Informationen zu jeder Fahrt mit Startzeitpunkt und Endzeitpunkt (jeweils als komplettes Datum inkl. Uhrzeit ausgelegt und indiziert) für eine solche Schicht. Und bei Intervall-Angaben, muss klar definiert sein, ob der Endzeitpunkt inkludiert ist oder nicht.

Danach lohnt es sich erst, überhaupt an ordentliche Abfragen, Formulare und Berichte zu denken.