Ereignisse in Abfragen nebeneinander darstellen

Q

Quarterback

Neuer Benutzer
Beiträge
3
Hallo liebe Gemeinde,
ich habe eine Tabelle wo die Zeitpunkte und die dazugehörigen Ereignisse (Einfahrten und Ausfahrten) zu jedem Benutzer untereinander dargestellt sind:
Bewegungen.webp

Nun möchte ich das aber in einer Abfrage so darstellen das die Zeitpunkte und Ereignisse nebeneinander dargestellt werden:
Bewegungen_soll.webp

Das Problem ist das wie man anhand dieser Beispiele sehen kann, die Ein/Ausfahrtszeitpunkte nicht am selben Tag sein müssen oder aber auch mehrmals am selben Tag sein können. Ich hab schon vieles versucht, natürlich auch Abfragen nur zu den Einfahrten und Ausfahrten und diese dann über nur Datumsfelder zu verketten aber da fehlen dann natürlich die Datensätze welche nicht entsprechen.
Die BenutzerNr ist eindeutig. Ich hab zu dieser Thematik nichts gefunden (nur ein Beispiel mit Ein/Ausfahrt am selben Tag, das hilft mir aber leider nicht)
Leider bin ich nicht so tief in SQL daher ersuche ich Euch um Hilfestellung.
Ich verwende MS Access (Office 365)

Danke und Grüße
Quarterback
 
Werbung:
Also mit SQL ist das i.d.R. kein Problem. Das Problem ist hier Access, das nur sehr eingeschränkt SQL Funktionen bietet. Das macht das ganze umständlich und unperformant. Das Beste, was mir aktuell einfällt, wäre ein Subselect pro Datensatz, also etwa so:
Code: 
SELECT t.*,
( SELECT TOP 1 Ztpkt FROM tabelle WHERE BenutzerNr = t.BenutzerNr AND KartenNr = t.KartenNr AND Ztpkt >= t.Zpkt AND GerBez LIKE 'Ausfahrt%' ORDER BY Ztpkt ) AS AFZtpkt
FROM tabelle t
WHERE t.GerBez LIKE 'Einfahrt%'
Nachteil ist hier ganz klar ein Subselect pro Datensatz, der ausgegeben wird und pro Attribut, das du aus der Tabelle braucst. Also eine weitere Abfrage für z.B. GerBez, wenn das relevant ist, jetzt hast du nur AFZtpkt.
 
Die folgende VBA-Routine erzeugt eine Arbeitstabelle im gewünscheten Muster.
Einfach in ein allgemeines Modul kopieren und bei Bedarf mit "Call InOuteineZeile" aufrufen
Die Arbeitstabelle muss einmal manuell angelegt werden und wird dann immer bei Aufruf der Sub geleert und neu gefüllt.
Die Feldnamen habe ich soweit übernommen, aber in der Ausgabetabelle müssen sie natürlich für Ein- und Ausfahrt differenziert werden
Eine weitere Voraussetzung ist, dass der erste Satz der Eingabetabelle/Query eine "Einfahrt" ist und auf einer Einfahrt immer auch eine Ausfahrt folgt. Aber das muss ja logischerweise so sein, denn wer rausfährt muss ja auch mal reingefahren sein.

Code: 
Option Compare Database
Option Explicit

Public Sub InOuteineZeile()
Dim db As Database
Dim rsin As Recordset
Dim rsout As Recordset

Set db = CurrentDb
db.Execute "DELETE * FROM EinAusfahrten_eineZeile", dbFailOnError 'Arbeitstabelle leeren

Set rsin = db.OpenRecordset("qry_DPBewegungen")
Set rsout = db.OpenRecordset("EinAusfahrten_eineZeile")

rsin.MoveFirst

Do Until rsin.EOF

 If rsin!BezTxtCode = "Einfahrt" Then
   rsout.AddNew
   rsout!ZtPktE = rsin!ZtPkt
   rsout!BenutzerNr = rsin!BenutzerNr
   rsout!Nachname = rsin!Nachname
   rsout!GerNrE = rsin!GerNr
   rsout!GerBezE = rsin!GerBez
   rsout!BezTxtCodeE = rsin!BezTxtCode
  
   If Not rsin.EOF Then
      rsin.MoveNext
      GoTo weiter
   Else
      Exit Sub
   End If
 Else
   If rsin!BezTxtCode <> "Ausfahrt" Then
      GoTo Sequenzfehler
   Else
      rsout!ZtPktA = rsin!ZtPkt
      rsout!GerNrA = rsin!GerNr
      rsout!GerBezA = rsin!GerBez
      rsout!BezTxtCodeA = rsin!BezTxtCode
    rsout.Update

   End If
  
   rsin.MoveNext
 End If
 
weiter:
Loop

Exit Sub

Sequenzfehler:
  msgbox "Fehler in Eingabesequenz bei: " & rsin!ZtPkt, vbCritical, "Proc InoutZeile Fehler"
End Sub
 
Vielen Dannk für Eure prompten Rückmeldungen, ich haeb mir beide Varianten eben angesehen und möcht Euch gerne mein Feedback dazu geben.
Generell noch eine zusätzliche Info wozu das ganze überhaupt gut sein soll.
Im Prinzip geht es um eine Auswertung der Parkdauer. D.h. also immer von einem Einfahrtszeitpunkt zum nächsten Ausfahrtszeitpunkt wird die Parkdauer berechnet. Und dafür benötige ich eben die Darstellung der EIn/Ausfahrtsbewegungen in dieser Form. Die Ermittlung der Parkdauer weiß ich eh wie das geht, das ist hier nicht das Thema.
Über ein Formular wird für den betreffenden Parker der Zeitraum ausgewählt z.B. vom 01.04. bis zum 10.04. und dann sollen eben die EF/AF Zeitpunkte mit der Parkdauer dazu aufscheinen.
Nun gibt es folgenden Fälle die stattfinden können wo es keine Auswertung geben kann:
1. Die Einfahrt findet z.B. am 31.03 statt, die Ausfahrt am 01.04, somit kann es für diese Transaktion keine Auswertung geben
2. Die Einfahrt findet am 10.04. statt die Ausfahrt erst am 11.04, auch für diese Transaktion kann es keine Auswertung geben.
3. Es kann (leider) auch sein das es 2 Einfahrten oder aber auch 2 Ausfahrten hintereinander gibt. Das passiert dann wenn der Parker seine Parkkarte z.B. vergisst und eine manuelle Schrankenöffnung gemacht wird...


@ukulele - das Backend (also die Tabellen) läuft in einem SQL Server, daher wäre es grundsätzlich kein Problem da eine View zu erstellen, ich kenn mich mit Access halt einfach viel besser aus, deswegen die Fragestellung explizit zu Access Abfragen. In deinem Beispiel krieg ich immer eine Parameterabfrage für den Wert t.Ztpkt....
SQL: 
SELECT t.*,
 (SELECT TOP 1 Ztpkt FROM qry_DPBewegungen WHERE BenutzerNr = t.BenutzerNr  AND Ztpkt >= t.Zpkt AND GerBez LIKE 'Ausfahrt%' ORDER BY Ztpkt ) AS AFZtpkt
FROM qry_DPBewegungen AS t
WHERE t.GerBez LIKE 'Einfahrt%'

Aber wenn das in SQL Server besser oder einfacher gehen würd darf ich Dich da um ein Beispiel bitten?

@andyfau - dieser Ansatz würde grundsätzlich gut funktionieren, beim durchspielen der Daten ist mir dann aufgefallen das der erste Datensatz nicht immer zwangsläufig mit einer Einfahrt beginnen muß - siehe obige Beschreibung, ich werde drüber nachdenke ob es möglich ist das die zugrundeliegende Abfrage immer mit einer Einfahrt beginnt.

Wünsch Euch ein schönes Wochenende!
lg
Fritz
 
Zuletzt bearbeitet:
zu SQL Server:
Ja das ist gut, damit geht deutlich mehr. Ich liefere dir ein Beispiel nach, das auf jeden Fall als Abfrage in SQL läuft und zur Not als View angelegt und dann von Access abgefragt werden kann. Mir sind nur die Varianten noch nicht ganz klar, die noch abgebildet werden müssten.

zu 1.) und 2.):
Warum sollte das nicht gehen nur weil der Tag wechselt? Bei mir wäre keine solche Einschränkung vorgesehen, natürlich ließe sich eine einbauen, z.B. kein Aufenthalt über 24h aber dann eben nach Stunden gerechnet, nicht nach Tagen.

zu 3.):
Es bricht sich alles runter auf die Frage wie ich eine Transaktion als zusammen hängend erkennen kann. Ich kann das sehr eng fassen, dann gehe ich z.B. davon aus das am Anfang immer eine Einfahrt steht. Um Zwei Einfahrten als zusammenhängend zu erkennen, braucht es aber Anhaltspunkte. Ideal wäre eine gemeinsame ID für alles, was den selben Vorgang betrifft den du darstellen willst. Mir scheint, das gibt es nicht. Deine Beispieldatensätze tragen eine KartenNr, wird die manuelle Schrankenöffnung auch dieser KartenNr zugeordnet? Es wäre gut, wenn die Beispieldaten mehr als eine KartenNr und möglichst alle denkbaren Sonderfälle abbilden, nicht nur Standardfälle.
 
Da möchte ich auch noch gern was zu sagen:

SQL-Server ist, was professionelle Anwendungen und vor allem Massendaten angeht, absolut als Backend vorzuziehen.

Access als Frontend ist da, vor allem wenn man es kann, ein prima Tool.

zu 1.) und 2.) Da Access und, soweit ich weiß auch SQL-Server, Datum/Zeit-Felder als absolute Zahlenwerte (in Hundertstelsekunden) ausgehend vom 31.12.1899 speichert ist jede Differenzrechnung recht einfach (Vor und Nachkommastellen beachten). Also auch eine Abrechnung über mehrere Tage. Access bietet da viele Funktionen zur Berechnung.

zu 3.) Die VBA-Lösung geht von "sinnvollen" Daten aus, also Einfahrt wird immer gefolgt von einer Ausfahrt. Wird diese Logik durch die beschriebenen Fälle unterbrochen, ist in jedem Fall ein manuelles Eingreifen oder ein regelbasiertes Ergänzen der Daten notwendig, sodass die Logik, die zur Abrechnung notwendig ist, wieder hergestellt wird. Z.B. Wer seine Zahlkarte verliert zahlt immer den vollen Tagessatz. All das muss ja dann über einen entsprechenden Dialog geregelt werden. Was aber auch kein Hexenwerk ist.
 
Ich geb Euch natürlich recht, der Logik zufolge muß es immer eine Einfahrt und eine Ausfahrt geben um damit eien Berechnung stattfinden kann. Wie aber beschrieben kommt es aber vor das es zu Unterbrechungen in dieser Logik kommt. Es gibt keine ID zu den Transaktionen die einzige eindeutige Referenz ist die BenutzerID
Meine Idee dazu wäre, das immer die letzte Einfahrt vor einer Ausfahrt als Bezug genommen wird. Ebenso das nach einer Einfahrt die nächste darauffolgende Ausfahrt zur Berechnung genommen wird. Es geht hierbei übrigens nur um eine statistische Auswertung einer bestimmten Gruppe von Parkern, deswegen kann das ruhig sehr eng gerechnet werden. Wenn es also tatsächlich mehere Einfahrten ohne jeweiliger Ausfahrt oder umgekehrt kommt, dann steht das sowieso in einem eigenen Journal das man sich dann genauer ansehen wird.
Ich werde im Lauf der nächsten Woche Beispieldaten mit typischen Sonderfällen zusammenstellen.
lg Fritz
 
Ja bitte für jeden Fall einen Beispieldatensatz erstellen, das hilft. Natürlich nicht als Bild, das will ja keiner abtippen.

Wenn ich den Sachverhalt richtig verstehe kann es theoretisch mehr als eine Einfahrt geben aber immer nur eine Ausfahrt oder keine Ausfahrt, wenn die KartenNr noch eingeloggt ist. Bei mehr als einer Einfahrt gibt es eventuell einmal keine Zuordnung zur Karte, das ist dann schwierig. Aber entscheidend ist, das die Ausfahrt als maßgebliches Event herhalten kann.

Hier schonmal ein Ansatz:
Code: 
WITH DPBewegungen(KartenNr,Ztpkt,BezTxtCode) AS (
    SELECT    142,getdate(),'Einfahrt' UNION ALL
    SELECT    142,dateadd(minute,1,getdate()),'Einfahrt' UNION ALL
    SELECT    142,dateadd(minute,10,getdate()),'Ausfahrt' UNION ALL
    SELECT    142,dateadd(day,1,getdate()),'Einfahrt' UNION ALL
    SELECT    142,dateadd(day,1,dateadd(minute,10,getdate())),'Ausfahrt' UNION ALL
    SELECT    142,dateadd(day,2,getdate()),'Einfahrt'
    ), t1 AS (
    SELECT    *,
            (    CASE
                WHEN    lag(KartenNr) OVER (PARTITION BY KartenNr ORDER BY Ztpkt ASC) IS NULL
                OR        lag(BezTxtCode) OVER (PARTITION BY KartenNr ORDER BY Ztpkt ASC) = 'Ausfahrt'
                THEN    1
                ELSE    0
                END ) AS neu_transaktion
    FROM    DPBewegungen
    )
SELECT    t1.KartenNr,
        t1.Ztpkt,
        t1.BezTxtCode,
        sum(t1.neu_transaktion) OVER (PARTITION BY t1.KartenNr ORDER BY t1.Ztpkt ASC) AS transaktion
FROM    t1
Ich bilde eine Transatkionsnummer für alle Datensätze, die zusammen gehören. Dabei kann ich in dem CASE-Statement recht flexibel definieren, wann eine neue Transaktion starten soll. Aktuell bedeutet eine Ausfahrt immer, das im Anschluss eine neue Transaktion startet

Über die Transaktionsnummer kann man dann auch t1 mit sich selbst joinen, wenn es denn unbedingt nebeneinander stehen soll. Wobei dann, bei zwei Einfahrten, die selbe Ausfahrt stehen kann.
Code: 
WITH DPBewegungen(KartenNr,Ztpkt,BezTxtCode) AS (
    SELECT    142,getdate(),'Einfahrt' UNION ALL
    SELECT    142,dateadd(minute,1,getdate()),'Einfahrt' UNION ALL
    SELECT    142,dateadd(minute,10,getdate()),'Ausfahrt' UNION ALL
    SELECT    142,dateadd(day,1,getdate()),'Einfahrt' UNION ALL
    SELECT    142,dateadd(day,1,dateadd(minute,10,getdate())),'Ausfahrt' UNION ALL
    SELECT    142,dateadd(day,2,getdate()),'Einfahrt'
    ), t1 AS (
    SELECT    *,
            (    CASE
                WHEN    lag(KartenNr) OVER (PARTITION BY KartenNr ORDER BY Ztpkt ASC) IS NULL
                OR        lag(BezTxtCode) OVER (PARTITION BY KartenNr ORDER BY Ztpkt ASC) = 'Ausfahrt'
                THEN    1
                ELSE    0
                END ) AS neu_transaktion
    FROM    DPBewegungen
    ), t2 AS (
    SELECT    t1.KartenNr,
            t1.Ztpkt,
            t1.BezTxtCode,
            sum(t1.neu_transaktion) OVER (PARTITION BY t1.KartenNr ORDER BY t1.Ztpkt ASC) AS transaktion
    FROM    t1
    )
SELECT    t3.*,
        t4.Ztpkt AS Ztpkt_Ausfahrt
FROM    t2 t3
LEFT JOIN t2 t4
ON        t3.KartenNr = t4.KartenNr
AND        t3.transaktion = t4.transaktion
AND        t4.BezTxtCode = 'Ausfahrt'
WHERE    t3.BezTxtCode = 'Einfahrt'
 
PS: Du solltest das im SQL Management Studio gegen den SQL Server testen. Wenn du dann später mit dem Ergebnis zufrieden bist, musst du das eventuell als View anlegen oder nach Access migrieren.
 
Werbung:
andyfau schrieb:
zu 1.) und 2.) Da Access und, soweit ich weiß auch SQL-Server, Datum/Zeit-Felder als absolute Zahlenwerte (in Hundertstelsekunden) ausgehend vom 31.12.1899 speichert ist jede Differenzrechnung recht einfach (Vor und Nachkommastellen beachten). Also auch eine Abrechnung über mehrere Tage. Access bietet da viele Funktionen zur Berechnung.
Zum Vergrößern anklicken....
Ich würde dasetwas einschränken bzw.anders darstellen.
Wenn bereits ein richtiger SQL Server genutzt wird, würde ich jegliche Access Funktion im SQL vermeiden und es nur zur Eingabe und Ausgabe benutzen.
Damit habe ich viel weniger Aufwand und Abhängigkeiten, wenn ich mal migrieren will oder muss.
 


Schreibe deine Antwort....
Zurück
Oben