Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 5/2013.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Schichtplaner

Wenn ein Arbeitsplatz nicht nur zu gewöhnlichen Arbeitszeiten besetzt sein muss, sondern im Extremfall den kompletten Tag, müssen mehrere Mitarbeiter bereitstehen. Diese werden dann in mehreren Schichten über den Tag verteilt. Dabei ist es gar nicht so einfach, nicht den Überblick zu verlieren – immerhin sollen alle Mitarbeiter im Laufe eines Zeitraums möglichst die angestrebte Anzahl Schichten durchführen. Gleichzeitig sollen zu jeder Zeit ausreichend Mitarbeiter arbeiten. Was liegt da näher, als dieses Problem mit einer geeigneten Access-Datenbank anzugehen?

Die in diesem Beitrag beschriebene Schichtplanung soll einige Ein- und Ausgabemöglichkeiten für die Erstellung von Schichtplänen berücksichtigen:

  • Eingabe/Markierung der Schichten in einer Übersicht mit allen Mitarbeitern und für einen bestimmten Zeitraum wie etwa 28 Tage
  • Berücksichtigung verschiedener Schichten, in der Regel Früh-, Mittag- und Spätschicht
  • Übersicht für einen bestimmten Zeitraum wie etwa einen Monat als Bericht
  • Ausgabe des Schichtplans für einzelne Mitarbeiter per Bericht, dort unterschiedliche Markierung der verschiedenen Schichten

Tabellen der Lösung

Die Lösung benötigt zunächst eine Mitarbeitertabelle. Diese heißt tblMitarbeiter und soll so einfach wie möglich ausgelegt werden. Das heißt, dass es zunächst nur ein Feld für die Bezeichnung des Mitarbeiters (Mitarbeiter) sowie das Primärschlüsselfeld MitarbeiterID gibt.

Schichtarten

Die Anwendung soll bezüglich der verschiedenen Schichten möglichst flexibel sein. Das heißt, dass es nicht nur die üblichen Schichten wie Früh-, Spät- und Nachtschicht geben soll. Der Benutzer kann den Tag nach Gutdünken in die gewünschten Schichten einteilen.

Gegebenenfalls werden nur zwei Schichten pro Tag benötigt – etwa von 8 Uhr bis 14 Uhr und von 14 Uhr bis 22 Uhr.

Oder zwei Schichten überlappen sich, weil es während der Überlappung besonders viel zu tun gibt (etwa von 8 Uhr bis 16 Uhr und von 10 Uhr bis 20 Uhr).

Deshalb sehen wir eine eigene Tabelle namens tblSchichtarten zur Erfassung der verschiedenen Schichten vor (s. Bild 1). Diese Tabelle enthält die folgenden Felder:

Verwaltung von Arbeitstagen per Tabelle

Bild 1: Verwaltung von Arbeitstagen per Tabelle

  • SchichtartID: Primärschlüsselfeld der Tabelle
  • Schichtart: Bezeichnung der Schichtart, etwa Frühschicht oder Spätschicht
  • Schichtbeginn: Uhrzeit, zu der die Schicht beginnt
  • Schichtende: Uhrzeit, zu der die Schicht endet
  • Farbe: Legt eine Farbe für die Schichtart fest.
  • Kuerzel: Legt einen Buchstaben zur einfachen Anzeige in Übersichten fest.

Berücksichtigung von Fehltagen

Die Mitarbeiter stehen nicht immer zur Verfügung, da Sie wegen Urlaub, Krankheit oder Weiterbildung fehlen. Man neigt dazu, schnell eine eigene Tabelle zur Erfassung der Fehlzeiten zu erstellen.

In der Tat ist es aber viel sinnvoller, einfach einen oder mehrere weitere Einträge in der Tabelle tblSchichtarten zu erfassen – beispielsweise mit den Werten Krankheit, Urlaub oder Fortbildung im Feld Schichtart.

Auf diese Weise ist es sogar möglich, die Fehlzeit auf bestimmte Schichten einzuschränken. Wenn ein Mitarbeiter beispielsweise mittags einen wichtigen Termin hat und dort keine Schicht wahrnehmen kann, trägt man für diese Schicht einfach als Schichtart die entsprechende Fehlzeitschichtart ein.

Damit diese Schichtarten deutlich von den übrigen, regulären Schichtarten unterschieden werden können, erhält die Tabelle tblSchichtarten ein weiteres Feld namens Fehlschicht. Dieses verwendet den Felddatentyp Ja/Nein.

Arbeitstage

Für einige Zwecke ist es sehr hilfreich, wenn es eine Tabelle mit allen benötigten Datumsangaben gibt. Den genauen Zweck erläutern wir später, aktuell reicht Folgendes: Die Tabelle heißt tblArbeitstage, enthält die beiden Felder ArbeitstagID und Arbeitstag (Datum/Uhrzeit) und wird mit der Prozedur aus Listing 1 gefüllt.

Public Sub ArbeitstageEintragen(Optional datStart As Date, Optional datEnde As Date)
     Dim datAktuell As Date
     Dim db As DAO.Database
     Set db = CurrentDb
     If datStart = 0 Then
         datStart = Date
     End If
     If datEnde = 0 Then
         datEnde = datStart + 1000
     End If
     On Error Resume Next
     For datAktuell = datStart To datEnde
         db.Execute "INSERT INTO tblArbeitstage(Arbeitstag) VALUES(" & ISODatum(datAktuell) & ")", dbFailOnError
     Next datAktuell
End Sub

Listing 1: Füllen der Tabelle tblArbeitstage

Die Prozedur erwartet den ersten und den letzten anzulegenden Tag als optionale Parameter. Wird der Starttag nicht angegeben, stellt die Prozedur diesen auf das aktuelle Datum ein.

Fehlt das Enddatum, erhält die Variable datEnde den Inhalt von datStart plus 1.000 Tage. Anschließend durchläuft die Prozedur in einer Schleife den angegebenen Datumsbereich und trägt die entsprechenden Datumsangaben in das Feld Arbeitstag der Tabelle tblArbeitstage ein.

Schichten speichern

Schließlich benötigen wir noch eine Tabelle, in der wir die jeweiligen Schichten für die Arbeitstage und Mitarbeiter speichern. Dies heißt tblSchichten und ist wie in Bild 2 aufgebaut.

Die Tabelle tblSchichten enthält einen zusammengesetzten, eindeutigen Index für die beiden Felder MitarbeiterID und ArbeitstagID, damit für jeden Mitarbeiter nur eine Schicht je Arbeitstag festgelegt werden kann.

Bild 2: Die Tabelle tblSchichten enthält einen zusammengesetzten, eindeutigen Index für die beiden Felder MitarbeiterID und ArbeitstagID, damit für jeden Mitarbeiter nur eine Schicht je Arbeitstag festgelegt werden kann.

Schichtplan-Tabelle füllen

Die Tabelle tblSchichten muss nun noch vorausgefüllt werden. Warum das – können wir die Kombinationen aus Mitarbeiter, Arbeitstag und Schichtart nicht zusammenstellen, wenn die Schicht geplant wird? Grundsätzlich ginge dies schon – aber wir möchten ja bereits für das Festlegen der Schichten eine komfortable und übersichtliche Benutzeroberfläche liefern. Dafür ist es nötig, dass die entsprechenden Datensätze der Tabelle tblSchichten bereits existieren – auch wenn die Schichtarten selbst dort noch nicht zugewiesen sind. Die gefüllte Tabelle soll schließlich wie in Bild 3 aussehen.

Die Tabelle tblSchichten mit einigen vorab angelegten Datensätzen

Bild 3: Die Tabelle tblSchichten mit einigen vorab angelegten Datensätzen

Das Füllen dieser Tabelle übernimmt die Prozedur SchichtenEintragen aus Listing 2. Die Prozedur erzeugt zwei Recordset-Objekte auf Basis der Tabellen tblMitarbeiter und tblArbeitstage. In einer äußeren Do While-Schleife durchläuft die Prozedur die Datensätze der Tabelle tblMitarbeiter, in einer inneren Schleife die Datensätze aus tblArbeitstage.

Public Sub SchichtenEintragen()
     Dim db As DAO.Database
     Dim rstMitarbeiter As DAO.Recordset
     Dim rstArbeitstage As DAO.Recordset
     Set db = CurrentDb
     Set rstMitarbeiter = db.OpenRecordset("SELECT * FROM tblMitarbeiter", dbOpenDynaset)
     Set rstArbeitstage = db.OpenRecordset("SELECT * FROM tblArbeitstage", dbOpenDynaset)
     Do While Not rstMitarbeiter.EOF
         Do While Not rstArbeitstage.EOF
             On Error Resume Next
             db.Execute "INSERT INTO tblSchichten(MitarbeiterID, ArbeitstagID) VALUES(" & rstMitarbeiter!MitarbeiterID _
                 & ", " & rstArbeitstage!ArbeitstagID & ")", dbFailOnError
             On Error GoTo 0
             rstArbeitstage.MoveNext
         Loop
         rstArbeitstage.MoveFirst
         rstMitarbeiter.MoveNext
     Loop
End Sub

Listing 2: Eintragen von Datensätzen in die Tabelle tblSchichten

Nach dem Durchlaufen aller Datensätze der inneren Schleife wird der Datensatzmarkierer für das Recordset rstArbeitstage mit der MoveFirst-Methode wieder auf den ersten Datensatz zurückgesetzt.

Innerhalb der beiden Schleifen befindet sich eine Anweisung, die der Tabelle tblSchichten jeweils einen Datensatz hinzufügt. Diese stellt die Werte der Fremdschlüsselfelder MitarbeiterID und ArbeitstagID der Tabelle auf den jeweils aktuellen Wert des Primärschlüsselfelds der beiden Recordset-Objekte ein.

Wenn ein neuer Mitarbeiter zur Tabelle tblMitarbeiterID hinzugefügt wird oder wenn die Liste der Arbeitstage erweitert werden soll, braucht die Prozedur SchichtenEintragen einfach nur erneut aufgerufen zu werden. Durch die deaktivierte Fehlerbehandlung werden bereits vorhandene Kombinationen aus Mitarbeiter und Arbeitstag weder überschrieben noch wird ein Fehler ausgelöst, wenn die Prozedur versucht, einen bereits vorhandenen und mit einem eindeutigen Index versehenen Datensatz erneut hinzuzufügen.

Schichtplan anzeigen

Nachdem wir die grundlegenden Daten für den Schichtplan in der Tabelle tblSchichten vorliegen haben, benötigen wir noch ein Formular, mit dem wir diese Daten komfortabel bearbeiten können. Dazu gibt es eine Reihe Möglichkeiten: Wir können beispielsweise alle Arbeitstage für einen einzigen Mitarbeiter untereinander anzeigen und anbieten, die Schichten für jeden Arbeitstag auszuwählen.

Oder wir zeigen alle Mitarbeiter und ihre Schichten für einen ausgewählten Arbeitstag untereinander an und erlauben dem Benutzer, die Schichten für den aktuellen Arbeitstag zu bearbeiten.

Dies erlaubt natürlich nicht die Übersicht, die wir uns erhoffen – nämlich die Darstellung in einer Matrix, bei der die Spaltenköpfe die Arbeitstage anzeigen und die Zeilenköpfe die Mitarbeiter.

Die Zellen dazwischen sollen das Eintragen der jeweiligen Schichtart für die Kombination aus Arbeitstag und Mitarbeiter erlauben.

Schichten per Kreuztabelle

Diese Ansicht lässt sich mit Access-Bordmitteln nur auf einem Wege erreichen – nämlich mithilfe einer Kreuztabellenabfrage. Diese Kreuztabelle soll ein Ergebnis ähnlich wie in Bild 4 liefern.

Ergebnis der Kreuztabellenabfrage, welche die Daten für die Anzeige der Schichtübersicht im Formular liefern soll

Bild 4: Ergebnis der Kreuztabellenabfrage, welche die Daten für die Anzeige der Schichtübersicht im Formular liefern soll

Wie entwerfen wir die Kreuztabellenabfrage, die ein solches Ergebnis liefert? Dazu müssen wir zunächst einmal ermitteln, welche Daten wir dazu benötigen:

  • Mitarbeitername für die Anzeige in den Zeilenüberschriften
  • Datum der Arbeitstage für die Anzeige in den Spaltenüberschriften
  • Schichtart oder Schichtartkürzel für die Anzeige als Wert der Kreuztabelle

Legen Sie also eine neue Abfrage an und stellen Sie den Abfragetyp dann etwa über den entsprechenden Ribbon-Eintrag auf Kreuztabelle ein. Fügen Sie die vier Tabellen tblSchichten, tblSchichtarten, tblMitarbeiter und tblArbeitstage hinzu.

Die Tabelle tblSchichten sorgt für die Verknüpfung zwischen den drei übrigen Tabellen, welche die Daten für die Anzeige in der Kreuztabelle liefern.

Das heißt, dass Sie das Feld Mitarbeiter als Zeilenüberschrift, das Feld Arbeitstag als Spaltenüberschrift und das Feld Schichtart als Wert zum Abfrageentwurf hinzufügen.

Außerdem benötigen wir noch ein Kriterium, da eine Kreuztabelle nur eine begrenzte Anzahl von Spalten anzeigen kann. In diesem Fall fügen wir das Feld Arbeitstag zum Entwurf hinzu und legen in der Zeile Funktion den Wert Bedingung fest. Dann stellen wir für Testzwecke zunächst das folgende Filterkriterium ein (s. Bild 5):

Entwurf der Abfrage qryCTSchichten

Bild 5: Entwurf der Abfrage qryCTSchichten

>=#01.08.2013# Und <#01.09.2013#

Ein Wechsel zur Datenblattansicht liefert relative wenige Daten – zumindest, wenn Sie noch keine Werte im Feld SchichtartID der Tabelle tblSchichten eingetragen haben.

Der Grund ist, dass die Abfrage nur solche Datensätze liefert, für die alle Fremdschlüsselfelder der Tabelle tblSchichten gefüllt sind. Dies ist natürlich beim Feld Schichtart­ID nicht der Fall, da wir ja die Schichtarten für die einzelnen Arbeitstage erst noch festlegen wollen.

Also stellen Sie die Verknüpfungseigenschaften für die Beziehung zwischen den Tabellen tblSchichten und tblSchichtarten so ein, dass alle Datensätze der Tabelle tblSchichten angezeigt werden – unabhängig davon, ob es bereits eine Verknüpfung zu einem Datensatz der Tabelle tblSchichtarten gibt (s. Bild 6).

Einstellen der Verknüpfungseigenschaften so, dass alle Datensätze der Tabelle tblSchichten angezeigt werden

Bild 6: Einstellen der Verknüpfungseigenschaften so, dass alle Datensätze der Tabelle tblSchichten angezeigt werden

Die Kreuztabellenabfrage speichern wir nun noch unter dem Namen qryCTSchichten.

Schichten im Formular anzeigen

Das Anzeigen der Kreuztabelle im Formular ist prinzipiell ganz einfach: Sie müssen einfach nur ein Hauptformular namens frmSchichten erstellen und diesem ein Unterformular namens sfmSchichten in der Datenblattansicht hinzufügen, das die Abfrage qryCTSchichten als Datenherkunft verwendet.

Wenn Sie dann die Feldliste einblenden, erkennen Sie allerdings das erste Problem: Die Felder der Datenherkunft sind natürlich alle nach den Spaltenüberschriften der Kreuztabellenabfrage benannt, die ja dynamisch aus der Tabelle tblArbeitstage ermittelt werden – und das auch noch in einem Format, in dem die Punkte im Datum durch Unterstriche ersetzt wurden.

Dennoch ziehen Sie zunächst alle Felder der Datenherkunft aus der Feldliste in den Detailbereich – irgendwie müssen wir ja beginnen. Das Ergebnis sieht dann wie in Bild 7 aus.

Der erste Entwurf des Unterformulars zur Anzeige des Ergebnisses der Kreuztabellenabfrage

Bild 7: Der erste Entwurf des Unterformulars zur Anzeige des Ergebnisses der Kreuztabellenabfrage

Nun wurden die Felder nicht nur nach dem Wert der Spaltenüberschriften für die aktuellen Kriterien (also den gewählten Zeitraum) benannt, sondern auch die Eigenschaft Steuerelementinhalt enthält den entsprechenden Feldnamen der Abfrage als Wert.

Nun möchten wir aber nicht immer den gleichen Datumsbereich anzeigen, sondern diesen dynamisch einstellen – beispielsweise über ein Textfeld, das den ersten Tag eines Datumsbereichs etwa von 28 Tagen entgegennimmt. Dazu sind einige Schritte nötig:

  • Wir fügen ein Textfeld namens txtStartdatum zum Formular hinzu, das zur Eingabe des ersten angezeigten Tages dient.
  • Wir werfen ein paar der Textfelder aus dem Formularentwurf heraus, sodass neben dem Feld Mitarbeiter noch 28 Bezeichnungs- und Textfelder verbleiben – die Darstellung eines Zeitraums von vier Wochen sollte für normale Anwendungsfälle ausreichen.
  • Die Bezeichnungsfelder, die aktuell Namen wie Bezeichnungsfeld1, Bezeichnungsfeld2 et cetera enthalten, werden durchlaufend umbenannt in lbl01, lbl02 und so weiter. Wichtig ist, dass die Zahlen zweistellig angegeben werden, also im Zweifel mit führender 0.
  • Die Textfelder, deren Namen aktuell noch 01_08_2013, 02_08_2013 et cetera lauten, benennen wir in txt01, txt02 und so weiter um. Auch hier gilt: Zweistellige Zahlen angeben!
  • Die Eigenschaft Steuerelementinhalt der Textfelder enthält die gleichen Werte wie zuvor die Eigenschaft Name. Wenn wir die Kreuztabelle später dynamisch gestalten, wird diese natürlich Felder mit anderen Namen liefern, die als Steuerelementinhalt verwendet werden sollen. Das ist aktuell aber kein Problem; den Steuerelementinhalt stellen wir später per VBA ein.

Kreuztabelle dynamisch

Wie erwähnt, soll die Kreuztabelle Ihre Daten in Abhängigkeit von einem bestimmten Startdatum liefern. Dazu muss diese mit einem Parameter ausgestattet werden. Bei herkömmlichen Abfragen geben Sie Parameter im Entwurf der Abfrage einfach als Bezeichnung des Parameters in eckigen Klammern an. Bei Kreuztabellenabfragen gelingt dies jedoch nicht – wir haben verschiedenste Varianten ausprobiert. Das soll uns jedoch nicht davon abhalten, die Kreuztabelle dynamisch zu gestalten. Wir statten diese dennoch einfach mit den benötigten Parametern aus:

>=[Startdatum] Und <[Enddatum]

Nun benötigen wir eine Prozedur, welche das Startdatum entgegennimmt und dieses in die Platzhalter der Kreuztabellenabfrage einbaut. Diese Prozedur heißt KreuztabelleFuellen und ist in Listing 3 zu finden.

Private Sub KreuztabelleFuellen(dat As Date)
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim rst As DAO.Recordset
     Dim strSQL As String
     Dim i As Integer
     Set db = CurrentDb
     strSQL = db.QueryDefs("qryCTSchichten").SQL
     strSQL = Replace(strSQL, "[Startdatum]", ISODatum(dat))
     strSQL = Replace(strSQL, "[Enddatum]", ISODatum(DateAdd("d", 28, dat)))
     Me!sfmSchichten.Form.RecordSource = strSQL
     For i = 0 To 27
         Me!sfmSchichten.Form("lbl" & Format(i + 1, "00")).Caption = Format(DateAdd("d", i, dat), "d.m")
         Me!sfmSchichten.Form("txt" & Format(i + 1, "00")).ControlSource = Format(DateAdd("d", i, dat), "dd_mm_yyyy")
         Me!sfmSchichten.Form("txt" & Format(i + 1, "00")).Tag = DateAdd("d", i, dat)
     Next i
     SpaltenbreitenAnpassen
End Sub

Listing 3: Kreuztabellenabfrage mit Parametern ausstatten

Die Prozedur liest zunächst den in der Abfrage qryCTSchichten enthaltenen SQL-Code aus und schreibt diesen in die Variable strSQL. Theoretisch könnte man diesen SQL-Code auch gleich fest im Code verdrahten, aber in einer gespeicherten Abfrage lassen sich Änderungen doch leichter durchführen.

Für den in der Variablen strSQL gespeicherten Ausdruck ersetzt die Abfrage zunächst den Platzhalter [Startdatum] durch das mit dem Parameter dat übergebene Datum. Der Platzhalter [Enddatum] wird mit einem Datum versehen, das sich 28 Tage hinter dem Datum aus der Variablen dat befindet.

Nach dem Ersetzen der Parameter der Kreuztabellenabfrage weist die Prozedur diese der Eigenschaft RecordSource des im Unterformularsteuerelement enthaltenen Formulars zu.

Dann passt die Prozedur die Bezeichnungsfelder und Steuerelemente an, und zwar in einer For...Next-Schleife über die Werte von 0 bis 27. Innerhalb der Schleife geschehen die folgenden Schritte:

  • Die Beschriftung der Bezeichnungsfelder wird auf das in dat gespeicherte Datum eingestellt, wobei dat erstens mit jedem Schleifendurchlauf um eins erhöht wird und zweitens das Datum im Format d.m abgebildet wird. Das zu ändernde Bezeichnungsfeld wird schließlich mit "lbl" & Format(i + 1, "00") referenziert.
  • Dann ändert die Prozedur die Eigenschaft ControlSource, also Steuerelementinhalt, des Textfeldes. Die Abfrageüberschriften werden ja automatisch auf die Werte des Feldes Arbeitstag für den angegebenen Zeitraum eingestellt, also etwa 01_08_2013. Genau auf diesen Wert muss auch die Eigenschaft Steu­erelementinhalt eingestellt werden, damit es den Wert der Kreuztabellenabfrage für den entsprechenden Arbeitstag anzeigt.
  • Schließlich stellt die Prozedur noch die Tag-Eigenschaft des jeweiligen Textfeldes auf das Datum für die aktuelle Spalte ein – wofür dies benötigt wird, erfahren Sie später.

Das waren die notwendigen Anpassungen – anschließend ruft die Prozedur noch die Routine SpaltenbreitenAnpassen auf, welche die optimale Breite für die Spalten einstellt.

Spaltenbreiten anpassen

Das Anpassen der Spaltenbreiten erledigt die kleine Routine SpaltenbreitenAnpassen (s. Listing 4).

Private Sub SpaltenbreitenAnpassen()
     Dim ctl As Control
     For Each ctl In Me!sfmSchichten.Form.Controls
         Select Case ctl.ControlType
         Case acTextBox, acComboBox, acCheckBox
         If Len(ctl.ControlSource) > 0 Then
             ctl.ColumnWidth = -2
         End If
         Case Else
         Debug.Print ctl.ControlType, ctl.Name
         End Select
     Next ctl
End Sub

Listing 4: Diese Prozedur bringt die Spalten in die optimale Breite zum Anzeigen ihres Inhalts.

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

© 2003-2015 André Minhorst Alle Rechte vorbehalten.