eine Tabelle mit Kalenderdaten erstellen

[teamdj]

Ich bin am Verzweifeln .......
ich lade hier einmal die Datenbank Test Stunden.mdb hoch
vielleicht mag sich das ja jemand anschauen.
ich möchte gerne das in der Abfrage ** ABFStundenMitarbeiter **
in der Spalte ** IstStdZahl ** die Stunden mit laufender Summe angezeigt werden.

quasi wenn:

IstStd     IstStdZahl   <<<< (IstStdZahl) sollen die Summen untereinander zusammengerechnet werden
10,00        10,00           
8,00         18,00
8,00         26,00
8,45         34,45

[MaggieMay]

Hallo,

mit der Meldung: Unzulässige Verwendung von Null

bei der Behandlung von Null-Werten empfiehlt sich der Einsatz der NZ-Funktion.

[teamdj]

Hallo,

mit der Meldung: Unzulässige Verwendung von Null

bei der Behandlung von Null-Werten empfiehlt sich der Einsatz der NZ-Funktion.

wie mache ich das mit der NZ-Funktion ?

[Lachtaube]

Ersetze Deine Abfrage hierdurch:

Code [Auswählen] Erweitern

SELECT DatePart("ww",sm.[TDatum],2,2) AS KW,
       sm.TDatum                      AS Tag,
       sm.TDatum,
       sm.Sondertage,
       sm.von,
       sm.bis,
       sm.[bis]-sm.[von]                                                                                                                                                                                                        AS Std,
       Switch(sm.[bis]-sm.[von]>=#12/30/1899 12:0:0#,#12/30/1899 1:0:0#,sm.[bis]-sm.[von]>=#12/30/1899 9:0:0#,#12/30/1899 0:45:0#,sm.[bis]-sm.[von]>=#12/30/1899 6:0:0#,#12/30/1899 0:30:0#,sm.[bis]-sm.[von]>=#12/30/1899 3:0:0#,#12/30/1899 0:15:0#,sm.[bis]-sm.[von]<#12/30/1899 3:0:0#,#12/30/1899#) AS Pause,
       [Std]-[Pause]                                                                                                                                                                                                        AS StdPause,
       IIF(
              sm.[WT]=7,[Std]/[Std]*#12/30/1899 0:4:0#,#12/30/1899#) AS Zuschlag,
       IIF((
                     sm.[WT] IN (6,7)
              AND    sm.[Sondertage]="Feiertag")
       OR     sm.[Sondertage] IN ("unb.Krank",
                                  "Freizeit",
                                  "Frei",
                                  "Überstunden"),#12/30/1899#,
              IIF(
                     sm.[Sondertage] IN ("Krank",
                                         "Urlaub",
                                         "Feiertag"),#12/30/1899 8:0:0#,[Std]-[Pause]+[Zuschlag])) AS IstStd,
       ABS(Nz(sm.[Sondertage])="Urlaub")                                                           AS Urlaub,
       ABS(Nz(sm.Sondertage) IN ("Krank",
                                 "unb.Krank")) AS Krank
FROM   StundenMitarbeiter                      AS sm;und erstelle eine neue Abfrage:

Code [Auswählen] Erweitern

SELECT q.KW,
       q.Tag,
       q.TDatum,
       q.Sondertage,
       q.von,
       q.bis,
       q.Std,
       q.Pause,
       q.StdPause,
       q.Zuschlag,
       q.IstStd,
       HHNNString((SELECT Sum(IstStd)
                     FROM ABFStundenMitarbeiter
                    WHERE TDatum <= q.TDatum)) AS IstStdSum,
       q.Urlaub,
       q.Krank
  FROM ABFStundenMitarbeiter AS q;Den Code der beiden Funktionen kannst Du noch etwas verfeinern.

Code [Auswählen] Erweitern

Option Compare Database
Option Explicit

'"hh:nn" in Datumswert umwandeln:
Function HHNNValue(ByVal Text)

   Dim PosTrenner As Long
   Dim Stunden As Variant
   Dim Minuten As Variant

   If IsNull(Text) Then Exit Function

   On Error GoTo e

   'Stundenanteil:
   Stunden = CDec(Val(Text))

   'Ggf. Minutenanteil:
   PosTrenner = InStr(Text, ":")
   If PosTrenner Then
      Text = Mid$(Text, PosTrenner + 1)
      Minuten = CDec(Val(Text))
   End If

   'Konvertieren in Tagesanteil:
   HHNNValue = (Stunden + Minuten / 60) / 24
   
e:
End Function

'Datumswert in "hh:nn" umwandeln:
Function HHNNString(ByVal Zeit)

   Dim Stunden As Long
   Dim Minuten As Long

   If IsNull(Zeit) Then Exit Function

   On Error GoTo e

   'In (gerundete) Gesamtminuten konvertieren:
   Zeit = Int(Zeit * 24 * 60 + 0.5)

   'Stunden- und Minutenanteil bestimmen:
   Stunden = Zeit \ 60
   Minuten = CLng(Zeit - Stunden * 60)

   'Formatierung:
   HHNNString = Format$(Stunden, "00") _
                & ":" & Format$(Minuten, "00")

e:
End FunctionDie ersten Zeilen des Ergebnisses schauen dann so aus:

Code [Auswählen] Erweitern

KW Tag TDatum Sondertage von bis Std Pause StdPause Zuschlag IstStd IstStdSum Urlaub Krank
53 Fr Fr, 01.01.2016 Frei 0:00 0:00 00:00 0 0
53 Sa Sa, 02.01.2016 Frei 0:00 0:00 00:00 0 0
53 So So, 03.01.2016 09:45 16:15 6:30 00:30:00 6:00 0:04 6:04 06:04 0 0
1 Mo Mo, 04.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 14:04 0 0
1 Di Di, 05.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 22:04 0 0
1 Mi Mi, 06.01.2016 09:00 18:30 9:30 00:45:00 8:45 0:00 8:45 30:49 0 0
1 Do Do, 07.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 38:49 0 0
1 Fr Fr, 08.01.2016 07:30 18:30 11:00 00:45:00 10:15 0:00 10:15 49:04 0 0
1 Sa Sa, 09.01.2016 12:00 17:00 5:00 00:15:00 4:45 0:00 4:45 53:49 0 0
1 So So, 10.01.2016 09:45 16:15 6:30 00:30:00 6:00 0:04 6:04 59:53 0 0
2 Mo Mo, 11.01.2016 08:00 14:00 6:00 00:30:00 5:30 0:00 5:30 65:23 0 0
2 Di Di, 12.01.2016 07:45 14:00 6:15 00:30:00 5:45 0:00 5:45 71:08 0 0
2 Mi Mi, 13.01.2016 07:45 16:30 8:45 00:30:00 8:15 0:00 8:15 79:23 0 0
2 Do Do, 14.01.2016 07:45 16:30 8:45 00:30:00 8:15 0:00 8:15 87:38 0 0
2 Fr Fr, 15.01.2016 07:45 14:30 6:45 00:30:00 6:15 0:00 6:15 93:53 0 0
2 Sa Sa, 16.01.2016 Frei 0:00 0:00 93:53 0 0
2 So So, 17.01.2016 Frei 0:00 93:53 0 0
3 Mo Mo, 18.01.2016 Frei 0:00 0:00 93:53 0 0
3 Di Di, 19.01.2016 09:30 17:30 8:00 00:30:00 7:30 0:00 7:30 101:23 0 0
3 Mi Mi, 20.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 109:23 0 0
3 Do Do, 21.01.2016 09:30 18:15 8:45 00:30:00 8:15 0:00 8:15 117:38 0 0
3 Fr Fr, 22.01.2016 09:30 18:30 9:00 00:45:00 8:15 0:00 8:15 125:53 0 0
3 Sa Sa, 23.01.2016 07:30 17:30 10:00 00:45:00 9:15 0:00 9:15 135:08 0 0
3 So So, 24.01.2016 09:45 16:15 6:30 00:30:00 6:00 0:04 6:04 141:12 0 0
4 Mo Mo, 25.01.2016 09:30 18:15 8:45 00:30:00 8:15 0:00 8:15 149:27 0 0
4 Di Di, 26.01.2016 07:30 17:30 10:00 00:45:00 9:15 0:00 9:15 158:42 0 0
4 Mi Mi, 27.01.2016 07:30 16:30 9:00 00:45:00 8:15 0:00 8:15 166:57 0 0
4 Do Do, 28.01.2016 07:30 17:30 10:00 00:45:00 9:15 0:00 9:15 176:12 0 0
4 Fr Fr, 29.01.2016 07:30 16:30 9:00 00:45:00 8:15 0:00 8:15 184:27 0 0
4 Sa Sa, 30.01.2016 Frei 0:00 0:00 184:27 0 0
4 So So, 31.01.2016 Frei 0:00 184:27 0 0

[teamdj]

Ersetze Deine Abfrage hierdurch:

Code [Auswählen] Erweitern

SELECT DatePart("ww",sm.[TDatum],2,2) AS KW,
       sm.TDatum                      AS Tag,
       sm.TDatum,
       sm.Sondertage,
       sm.von,
       sm.bis,
       sm.[bis]-sm.[von]                                                                                                                                                                                                        AS Std,
       Switch(sm.[bis]-sm.[von]>=#12/30/1899 12:0:0#,#12/30/1899 1:0:0#,sm.[bis]-sm.[von]>=#12/30/1899 9:0:0#,#12/30/1899 0:45:0#,sm.[bis]-sm.[von]>=#12/30/1899 6:0:0#,#12/30/1899 0:30:0#,sm.[bis]-sm.[von]>=#12/30/1899 3:0:0#,#12/30/1899 0:15:0#,sm.[bis]-sm.[von]<#12/30/1899 3:0:0#,#12/30/1899#) AS Pause,
       [Std]-[Pause]                                                                                                                                                                                                        AS StdPause,
       IIF(
              sm.[WT]=7,[Std]/[Std]*#12/30/1899 0:4:0#,#12/30/1899#) AS Zuschlag,
       IIF((
                     sm.[WT] IN (6,7)
              AND    sm.[Sondertage]="Feiertag")
       OR     sm.[Sondertage] IN ("unb.Krank",
                                  "Freizeit",
                                  "Frei",
                                  "Überstunden"),#12/30/1899#,
              IIF(
                     sm.[Sondertage] IN ("Krank",
                                         "Urlaub",
                                         "Feiertag"),#12/30/1899 8:0:0#,[Std]-[Pause]+[Zuschlag])) AS IstStd,
       ABS(Nz(sm.[Sondertage])="Urlaub")                                                           AS Urlaub,
       ABS(Nz(sm.Sondertage) IN ("Krank",
                                 "unb.Krank")) AS Krank
FROM   StundenMitarbeiter                      AS sm;
Hallo,
Wenn ich meine Abfrage durch deinen Code ersetzte sieht das ganz gut aus jedoch wird der Zuschlag nicht korrekt berechnet !
Für den Sonntag werden nur 4 Minuten berechnet wodurch dann letzten Endes 06:04 für den Sonntag herauskommen.
Es sollen aber für den Sonntag bei einer Arbeitszeit von 06:30 ** 4 Stunden zuzüglich ** berechnet werden.
Quasi:
So   von       bis        Std.          Pause      StdPasue             Zuschlag        IstStunden
     09:45   16:15    06:30           00:30       06:00                  04:00             10:00
                         ( Anwesende)             ( - 30min. Pause)  (4 std. Plus)  (bezahlte Stunden)   
und erstelle eine neue Abfrage:

Code [Auswählen] Erweitern

SELECT q.KW,
       q.Tag,
       q.TDatum,
       q.Sondertage,
       q.von,
       q.bis,
       q.Std,
       q.Pause,
       q.StdPause,
       q.Zuschlag,
       q.IstStd,
       HHNNString((SELECT Sum(IstStd)
                     FROM ABFStundenMitarbeiter
                    WHERE TDatum <= q.TDatum)) AS IstStdSum,
       q.Urlaub,
       q.Krank
  FROM ABFStundenMitarbeiter AS q;Den Code der beiden Funktionen kannst Du noch etwas verfeinern.

Code [Auswählen] Erweitern

Option Compare Database
Option Explicit

'"hh:nn" in Datumswert umwandeln:
Function HHNNValue(ByVal Text)

   Dim PosTrenner As Long
   Dim Stunden As Variant
   Dim Minuten As Variant

   If IsNull(Text) Then Exit Function

   On Error GoTo e

   'Stundenanteil:
   Stunden = CDec(Val(Text))

   'Ggf. Minutenanteil:
   PosTrenner = InStr(Text, ":")
   If PosTrenner Then
      Text = Mid$(Text, PosTrenner + 1)
      Minuten = CDec(Val(Text))
   End If

   'Konvertieren in Tagesanteil:
   HHNNValue = (Stunden + Minuten / 60) / 24
   
e:
End Function

'Datumswert in "hh:nn" umwandeln:
Function HHNNString(ByVal Zeit)

   Dim Stunden As Long
   Dim Minuten As Long

   If IsNull(Zeit) Then Exit Function

   On Error GoTo e

   'In (gerundete) Gesamtminuten konvertieren:
   Zeit = Int(Zeit * 24 * 60 + 0.5)

   'Stunden- und Minutenanteil bestimmen:
   Stunden = Zeit \ 60
   Minuten = CLng(Zeit - Stunden * 60)

   'Formatierung:
   HHNNString = Format$(Stunden, "00") _
                & ":" & Format$(Minuten, "00")

e:
End FunctionDie ersten Zeilen des Ergebnisses schauen dann so aus:

Code [Auswählen] Erweitern

KW Tag TDatum Sondertage von bis Std Pause StdPause Zuschlag IstStd IstStdSum Urlaub Krank
53 Fr Fr, 01.01.2016 Frei 0:00 0:00 00:00 0 0
53 Sa Sa, 02.01.2016 Frei 0:00 0:00 00:00 0 0
53 So So, 03.01.2016 09:45 16:15 6:30 00:30:00 6:00 0:04 6:04 06:04 0 0
1 Mo Mo, 04.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 14:04 0 0
1 Di Di, 05.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 22:04 0 0
1 Mi Mi, 06.01.2016 09:00 18:30 9:30 00:45:00 8:45 0:00 8:45 30:49 0 0
1 Do Do, 07.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 38:49 0 0
1 Fr Fr, 08.01.2016 07:30 18:30 11:00 00:45:00 10:15 0:00 10:15 49:04 0 0
1 Sa Sa, 09.01.2016 12:00 17:00 5:00 00:15:00 4:45 0:00 4:45 53:49 0 0
1 So So, 10.01.2016 09:45 16:15 6:30 00:30:00 6:00 0:04 6:04 59:53 0 0
2 Mo Mo, 11.01.2016 08:00 14:00 6:00 00:30:00 5:30 0:00 5:30 65:23 0 0
2 Di Di, 12.01.2016 07:45 14:00 6:15 00:30:00 5:45 0:00 5:45 71:08 0 0
2 Mi Mi, 13.01.2016 07:45 16:30 8:45 00:30:00 8:15 0:00 8:15 79:23 0 0
2 Do Do, 14.01.2016 07:45 16:30 8:45 00:30:00 8:15 0:00 8:15 87:38 0 0
2 Fr Fr, 15.01.2016 07:45 14:30 6:45 00:30:00 6:15 0:00 6:15 93:53 0 0
2 Sa Sa, 16.01.2016 Frei 0:00 0:00 93:53 0 0
2 So So, 17.01.2016 Frei 0:00 93:53 0 0
3 Mo Mo, 18.01.2016 Frei 0:00 0:00 93:53 0 0
3 Di Di, 19.01.2016 09:30 17:30 8:00 00:30:00 7:30 0:00 7:30 101:23 0 0
3 Mi Mi, 20.01.2016 09:30 18:00 8:30 00:30:00 8:00 0:00 8:00 109:23 0 0
3 Do Do, 21.01.2016 09:30 18:15 8:45 00:30:00 8:15 0:00 8:15 117:38 0 0
3 Fr Fr, 22.01.2016 09:30 18:30 9:00 00:45:00 8:15 0:00 8:15 125:53 0 0
3 Sa Sa, 23.01.2016 07:30 17:30 10:00 00:45:00 9:15 0:00 9:15 135:08 0 0
3 So So, 24.01.2016 09:45 16:15 6:30 00:30:00 6:00 0:04 6:04 141:12 0 0
4 Mo Mo, 25.01.2016 09:30 18:15 8:45 00:30:00 8:15 0:00 8:15 149:27 0 0
4 Di Di, 26.01.2016 07:30 17:30 10:00 00:45:00 9:15 0:00 9:15 158:42 0 0
4 Mi Mi, 27.01.2016 07:30 16:30 9:00 00:45:00 8:15 0:00 8:15 166:57 0 0
4 Do Do, 28.01.2016 07:30 17:30 10:00 00:45:00 9:15 0:00 9:15 176:12 0 0
4 Fr Fr, 29.01.2016 07:30 16:30 9:00 00:45:00 8:15 0:00 8:15 184:27 0 0
4 Sa Sa, 30.01.2016 Frei 0:00 0:00 184:27 0 0
4 So So, 31.01.2016 Frei 0:00 184:27 0 0

[MaggieMay]

Hi,

Es sollen aber für den Sonntag bei einer Arbeitszeit von 06:30 ** 4 Stunden zuzüglich ** berechnet werden.

der Zuschlag wird mit dieser Anweisung berechnet:

Code [Auswählen] Erweitern

IIF(sm.[WT]=7,[Std]/[Std]*#12/30/1899 0:4:0#,#12/30/1899#) AS Zuschlag,

Was meinst du wohl an welcher Stelle da etwas geändert werden muss?!

PS:
Das Zitieren vollständiger Beiträge ist völlig überflüssig.

[Lachtaube]

Mag sein, dass ich die Formel etwas verfratzt habe - die richtige Logik kannst Du aber bestimmt selbst einpflegen.

[teamdj]

ich denke
0:4:0 ändern auf 4:0:0
ich versuche das einmal.
Vielen dank für die Hilfe !!!!👍
Ich werde mich bestimmt noch mal melden. Ich probiere erst einmal mit deiner super Vorarbeit zu tüfteln
DANKE

[teamdj]

Hallo Lachtaube,

wenn ich eine neue Abfrage mit deinem Code erstelle
*************************************************
SELECT q.KW,
       q.Tag,
       q.TDatum,
       q.Sondertage,
       q.von,
       q.bis,
       q.Std,
       q.Pause,
       q.StdPause,
       q.Zuschlag,
       q.IstStd,
       HHNNString((SELECT Sum(IstStd)
                     FROM ABFStundenMitarbeiter
                    WHERE TDatum <= q.TDatum)) AS IstStdSum,
       q.Urlaub,
       q.Krank
  FROM ABFStundenMitarbeiter AS q;

****************************************
dann werden in der Spalte [IstStdSum] die Stunden auch so wie ich es wollte berechnet.
Nur leider kann ich in der Abfrage jetzt keine neuen Daten mehr einfügen oder ändern ?

[MaggieMay]

Hi,

es dürfte bereits die zugrunde liegende Abfrage "ABFStundenMitarbeiter" nicht änderbar sein.

[Lachtaube]

Zur Dateneingabe müsste man die Unterabfrage (SELECT Sum()...) in eine Funktion auslagern und mit dem TDatum als Argument versorgen. Lade mal eine abgespeckte Version der DB (als Access 2002/2003-Version) mit Spieldaten im Anhang hoch - dann bastelt Dir bestimmt jemand diese Funktion.

[Lachtaube]

@MaggieMay,

wieso?

[MaggieMay]

@Lachtaube:
Wegen der zahlreichen berechneten Felder, vermute ich.

[teamdj]

Hllo MaggieMay,

doch die ABFStundenMitarbeiter ist veränderbar dort kann ich Daten eintragen.
ich lade eine abgespeckte Version hoch
moment bitte

[teamdj]

Hier mal die Datenbank.

in der Abfrage [ABFStundenMitarbeiter] kann ich noch Daten eingeben welche dann auch in
der Abfrage [ABFStdMaGesamtstunden] übergeben werden und die Gesamtsumme der Stunden auch berechnet werden.
schön wäre es aber, wenn ich in der Abfrage [ABFStdMaGesamtstunden] die Daten eingeben könnte.
Ich wollte dann von der Abfrage [ABFStdMaGesamtstunden] ein Formular zur Eingabe der Daten kreieren.