Fuhrpark --> Auslastung von Fahrzeugen

Question

Hallo Leute,

ich habe eine Accessdatenbank, mit der ich eine Art Fuhrparkmanagement betreibe. Hierbei werden verschiedene Fahrzeuge hinterlegt, deren Einsatz dokumentiert, etc. Unter anderem gibt es eine Tabelle „Fahrzeugdisposition“, in der die Verleihdauer der Fahrzeuge eingetragen wird (Spalte „Übergabe“ und „Rückgabe“ im Format TT.MM.JJJJ hh:mm:ss).

Es besteht jetzt die Anforderung, eine Abfrage zu erstellen, um die Auslastung der einzelnen Fahrzeuge darzustellen. Ich habe dafür ein wenig gebastelt und folgendes Format für eine Abfrage hingekriegt:

Jahr | Kennzeichen | Januar | Februar | März | …
2008 | M – AB 123 | 100% | 80% | …
2007 | M – AB 123 | 99% | 100% | …
2008 | M – CD 456 | 80% | …

Die Berechnung der prozentualen Auslastung erfolgt auf Basis der ausgeliehenen Zeit. Und hier liegt genau das Problem: wie schaffe ich es, z.B. bei einer Verleihung vom 06.01.2009 bis zum 25.04.2009 das Auto für die Monate Februar und März als komplett ausgelastet darzustellen? Vielleicht mit einer Art Hilfstabelle, wo pro Fahrzeug und Monat ein Wert eingetragen wird, der als Grundlage für die prozentuale Berechnung dient? Also z.B. „1“ wenn ein Monat komplett gebucht wurde, „0,5“ wenn das Fahrzeug nur einen halben Monat lang gebucht ist, etc.? Nur wie lässt sich das hinkriegen??

Vielen Dank im Voraus für eure Hilfe, ich bin hier echt am Verzweifeln!

LG

Answer 1

Hallo lorf55,

ein interessanter Ansatz. Du solltest dir aber zu folgenden Punkten Gedanken machen. Insbesondere bringt der Jahreswechsel den Algorithmus in arge Schwierigkeiten.

Zwei Beispiele für Buchungen, die fehlerhafte Informationen oder Fehler liefern bei Angaben über den Jahreswechsel hinaus:

01.12.2008 - 15.01.2009 liefert gar kein Ergebnis
01.05.2008 - 01.06.2009 liefert ein falsches Ergebnis

Weiterhin würde ich die zwei Zeilen in der Funktion:

AusleihtageImMonat = (Day(Enddatum) - Day(Startdatum)) + 1
AusleihtageImMonat = MonTage(MonatAktuell) - Day(Startdatum) + 1

wie hier notiert mit "+ 1" versehen, da eine Buchung vom 1.1.09 (morgens) bis 1.1.09 (abends) 0 ergibt und eine Buchung vom 1.1.09 bis zum 31.1.09 nicht 1 ergibt.

Der 29.2. eines Schaltjahres liefert auch eine fehlerhafte Berechnung.

Gruß
Ralf

Answer 2

Ja stimmt. Prima, dass du alle Fehler gefunden hast.

Gruß
lorf

Answer 3

Hallo Ralf,

ich habe mir Deine Lösung noch einmal angeschaut und versucht, bei mir einzubinden. Hier erstmal der Code, den ich auf meine Datenbank (Access 2003) angepasst habe:

Option Compare Database
Option Explicit

Public Sub calcMonatsauslastung()

Dim rec1 As New ADODB.Recordset
Dim rec2 As New ADODB.Recordset

Dim mVon As Long
Dim mBis As Long
Dim mAkt As Long
Dim mAnz As Long

Dim dVon As Long
Dim dBis As Long
Dim dVonAnz As Long
Dim dBisAnz As Long

Dim d As Date
'
' Hier im Bsp wird alles neu berechnet! Das kann man optimieren...
'
DoCmd.RunSQL "DELETE * FROM Monatsauslastungen"
'
' Öffnen der Tabellen
'
rec1.Open "SELECT * from [Disposition der Fahrzeuge]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rec2.Open "SELECT * FROM Monatsauslastungen", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

While Not rec1.EOF
'
' ein paar Variablen initialisieren (eventuell ein paar zu viele ;-)
'
mAnz = DateDiff("d", CDate("01." & Format(rec1!Übergabe, "mm.yyyy")), CDate("01." & Format(DateAdd("m", 1, rec1!Übergabe), "mm.yyyy")))
dVonAnz = DateDiff("d", CDate("01." & Format(rec1!Übergabe, "mm.yyyy")), rec1!Übergabe) + 1
dBisAnz = DateDiff("d", CDate("01." & Format(rec1!Rückgabe, "mm.yyyy")), rec1!Rückgabe) + 1
mVon = Format(rec1!Übergabe, "yyyymm")
mBis = Format(rec1!Rückgabe, "yyyymm")
dVon = Format(rec1!Übergabe, "dd")
dBis = Format(rec1!Rückgabe, "dd")
'
' Buchung innerhalb eines Monats
'
If mVon = mBis Then
rec2.AddNew
rec2!buchungID = rec1!id
rec2!Monat = mVon
rec2!Auslastung = (dBis - dVon + 1) / mAnz
rec2.Update
'
' Buchung über Monatsgrenze
'
Else
'
' erster Buchungsmonat
'
rec2.AddNew
rec2!buchungID = rec1!id
rec2!Monat = mVon
rec2!Auslastung = dVon / mAnz
rec2.Update
'
' Monate dazwischen sind immer zu 100% ausgelastet
'
d = DateAdd("m", 1, CDate("01." & Format(rec1!Übergabe, "mm.yyyy")))
While mBis > Format(d, "yyyymm")
rec2.AddNew
rec2!buchungID = rec1!id
rec2!Monat = Format(d, "yyyymm")
rec2!Auslastung = 1
rec2.Update
d = DateAdd("m", 1, d)
Wend
'
' letzter Buchungsmonat
'
rec2.AddNew
rec2!buchungID = rec1!id
rec2!Monat = mBis
mAnz = DateDiff("d", CDate("01." & Format(rec1!Rückgabe, "mm.yyyy")), CDate("01." & Format(DateAdd("m", 1, rec1!Rückgabe), "mm.yyyy")))
rec2!Auslastung = dBis / mAnz
rec2.Update

End If
rec1.MoveNext
Wend
'
' schließen der Tabellen (nicht vergessen!)
'
rec1.Close
rec2.Close
End Sub


Jetzt zu meinen Fragen:

1) Oben schreibst Du, dass in der Tabelle "Buchungen" (Disposition der Fahrzeuge) folgender Wert gesetzt ist:

FarzeugID longint

In meiner Tabelle "Disposition der Fahrzeuge" ist die ID für da jeweilige Fahrzeug allerdings ein Textfeld, da das Feld "Kennzeichen" im Format "A-BC 123" gefüllt ist. Kommt es da zu irgendwelchen Problemen? Die Tabelle "Monatsauslastungen wird gefüllt, allerdings wird im Feld "BuchungID" nicht das Kennzeichen aufgeführt - da sollte es doch hingehören, oder?

2) Wenn ich das Modul ausführe, bleibt es immer mit einer Fehlermeldung bei den Einträgen mit "Rückgabe" hängen. Also z.B. hier:

dBisAnz = DateDiff("d", CDate("01." & Format(rec1!Rückgabe, "mm.yyyy")), rec1!Rückgabe) + 1

Der Fehler lautet: Laufzeitfehler 94: Unzulässige Verwendung von Null

Was hat es hiermit auf sich?

Vielen Dank noch einmal für Deine Hilfe! Die Auslastungsberechnung sieht schon mal super aus! Ich bin sicher, dass Du mich bald ins Ziel "geschleppt" hast :)

VLG

Answer 4

Hallo Sandra2910,

schön, dass die Umsetzung in deine Umgebung zunächst geklappt hat. Nun zu deinen Fragen:

1.Ich habe bei meinem Test für die Fahrzeuge eine eigene Tabelle definiert, die eine ID (autowert) als eindeutige Referenz besitzt. Somit könnte man weitere Informationen zu einem Fahrzeug in dieser Tabelle aufnehmen. Du kannst natürlich FahrzeugID durch jedes andere Feld ersetzen. Doch denke über meinen Vorschlag nach. Das so zu machen,lohnt sich später bestimmt.

2.Die Fehlermeldung sagt fast schon alles. In einem Datensatz der Dispositionstabelle ist das Feld Rückgabe nicht gefüllt. Das solltest du prüfen. Da ist die fachliche Anforderung nicht kenne, hier drei Vorschläge:

a) du verbietest Datensätze mit leeren Datumsangaben (in der Tabellendefinition oder bei der Formulareingabe)

b) du verarbeitest keine Datensätze, in denen ein Datum auf "null" (also leer) steht. Dafür änderst du das Select von "SELECT * from [Disposition der Fahrzeuge]" nach "SELECT * from [Disposition der Fahrzeuge] WHERE Rückgabe is not null and Übergane is not null",

c) du ersetzt null-Werte bei der berechnung durch ein gültiges Datum (z.B. das aktuelle datum. Dafür ersetzt du "rec1!Rückgabe" durch "nz(rec1!Rückgabe,date())" und das Ganze auch für die Übergage, falls notwendig. Dies sieht nicht sehr schön aus. Idealer Weise würde ich hier eine Variable definieren und diese dann verwenden.

Falls du das Ganze irgendwie produktiv einsetzen willst, würde ich dir dringend raten, deinen Code von jemand gegen checken zu lassen, der die fachlichen Anforderungen genau kennt. Verkenne nicht, dass das Testen (nicht durch den Entwickler!) immer ein elementarer Bestandteil einer Softwareentwicklung sein sollte. Vor allem dann, wenn durch solch eine Berechnung finanzielle Forderungen verbunden sein könnten. Dies ist nur ein gut gemeinter Rat.

Viel Spass beim Ausprobieren...

Gruß
Ralf

Answer 5

Hallo noch mal Ralf,

es tut mir leid, wenn ich noch einmal fragen muss aber ich komme einfach nicht weiter. Was genau muss ich ersetzen, um in die Spalte "Buchungen" das Kennzeichen einfügen zu lassen? Ich habe rec1!ID durch [Disposition der Fahrzeuge]!Kennzeichen bzw. rec1!Kennzeichen ersetzt, was aber leider nicht geklappt hat.

Sorry für die dumme Fragerei, bei VBA bin ich wirklich eine hohle Nuss und bin für Deine Hilfe sehr dankbar!!

VLG

Answer 6

Hallo Sandra2910,

erstens, dumme Fragen gibt es nicht. So, nun zur möglichen Umsetzung. Ich gehe davon aus, dass das Kennzeichen in der Tabelle [Disposition der Fahrzeuge] eindeutig ist UND eindeutig bleibt. In diesem Fall brauchst du nur folgendes zu tun:

1. In der Tabelle Buchungen den Typ von FahrzeugID ändern

ID autowert
FarzeugID Text
BuchungVon Datum
BuchungBis Datum

denn hier kommt in Zukunft das Kennzeichen rein. Die Länge des Textfeldes sollte mit der Größe des Feldes Kennzeichen übereinstimmen (12 Zeichen sollten reichen, oder?).

2. Ersetze alle Vorkommen von

rec2!buchungID = rec1!id
durch
rec2!buchungID = rec1!kennzeichen

denn das Feld ID gibt es in deiner Dispositionstabelle ja nicht.
Probiere es aus. Viel Erfolg.
Falls du willst, kannst du mir die DB auch mal zumailen (temporäre E-Mailadresse: sandra2910.hinki@spamgourmet.com), dann schaue ich mal drüber.

Viele Grüße
Ralf

Answer 7

Hallo Ralf,

bitte entschuldige die verspätete Antwort. Neben Urlaub kam dann noch Krankheit dazu... =/

Es funktioniert jetzt alles bestens. Die Auswertungen werden gemacht. Jetzt werde ich nur noch versuchen, das Makro irgendwie durch Aufrufen einer Abfrage zu starten und ich sitze gerade daran, das Format wie oben beschrieben hinzubiegen. Es soll ja im Endeffekt so aussehen:

Jahr | Kennzeichen | Januar | Februar | März | …
2008 | M – AB 123 | 100% | 80% | …
2007 | M – AB 123 | 99% | 100% | …
2008 | M – CD 456 | 80% | …


Vielen Dank noch mal an euch beide, insbesondere an Dich Ralf, für eure Geduld mit mir :)

VLG

Answer 8

Hallo Sandra2910,

im Code ist noch ein kleiner Fehler drin. Die Zeile mit der Zuweisung der Auslastung für den ersten Monat muss folgendermaßen lauten:

rec2!Auslastung = (mAnz - dVon + 1) / mAnz

Für deine Abfrage füge mal folgendes SQL-Statement in eine Abfrage ein:

TRANSFORM Sum(Monatsauslastungen.Auslastung) AS A
SELECT Monatsauslastungen.BuchungID, Left([Monat],4) AS Y
FROM Monatsauslastungen
GROUP BY Monatsauslastungen.BuchungID, Left([Monat],4)
ORDER BY Monatsauslastungen.BuchungID, Left([Monat],4)
PIVOT Mid([Monat],5);

eventuell musst du das noch ein Wenig auf deine Felder anpassen. Aber ich glaube, das kommt einem Wunsch schon recht nahe. Gehst danach in die Entwurfsansicht der Abfrage, muss du noch das Format von A auf Prozentzahl setzen, dann sieht es fast so aus wie in deinem Vorschlag

Gruß
Ralf

Answer 9

Hallo Ralf,

vielen Dank für Deinen Nachtrag! Kann es sein, dass beim letzten Buchungsmonat auch noch etwas angepasst werden muss?

Answer 10

Hallo Sandra2910,

die Berechnung des letzten Monats sieht gut aus. Es geht doch um die Zeile

rec2!Auslastung = dBis / mAnz

dBis enthält den Tag des letzten Monats und mAnz ist die Anzahl der Tage des Monats. Somit würde beim 14.2.09 genau 0,5 heraus kommen, also 14/28. mAnz wird in der Zeile davor für den letzten Monat berechnet.
Hat die View (Kreuztabelle) funktioniert?

Gruß
Ralf