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 Sandra2910,

der Ansatz mit der Hilfstabelle ist richtig. Ich gehe mal davon aus, dass die Auslastung (Prozentual) folgendermaßen ausgerechnet wird:

1. Resttage des Anfangsmonats / Anzahl der Tage des Monats
2. Buchungstage des Endemonats / Anzahl des Monats

wobei das auch der gleiche Monat sein könnte.

Beim Speichern des Datensatzes musst du eine Schleife über die Monate durchlaufen und die Auslastung entsprechend berechnen. Das Ganze benötigt ein wenig VBA-Code. Da kommst du nicht drum herum.

Noch ein Tipp. Wenn schon eine Zwischentabelle, dann würde ich direkt auf die Tage gehen, da du keine Aussage mehr treffen kannst ob die 0,5er Auslastung am Anfang, am Ende oder irgendwo zwischendrin war. Hängt natürlich ein wenig auch von der fachlichen Anforderung ab.
Bist du mit VBA vertraut?

Gruß
Ralf

Answer 2

Hallo Ralf,

danke für die schnelle Antwort. Mit VBA bin ich nur bedingt vertraut - "helloworld" sagt mir was, danach wird es aber eher düster... :)

Ob die Auslastung am Anfang, Ende oder Mitte eines Monats ist, ist nicht von Belang. Hauptsache, man kann pro Monat sehen, wie stark ein Fahrzeug ausgelastet ist.

Werde mal versuchen, mich dran zu setzen...

VLG

Answer 3

Hallo Sandra2910,

falls du Tipps brauchst oder nicht weiter kommst, melde dich einfach...

Gruß
Ralf

Answer 4

Die verstrichenen Tage kriegt man mit DatDiff raus:

TageVerstrichen: DatDiff("y";[Verleih]![von];[Verleih]![bis])

den Tag mit
TagVon: Tag([Verleih]![von])
den Monat mit
MonatVon: Monat([Verleih]![von]).

Eigentlich braucht man nur von den verstrichenen Tage die Tage der folgenden Monate abziehen, bis die verstrichenen aufgebraucht sind.

Aber das hast du auch sicher selbst gewusst.
Genauer weiß ich es im Moment auch nicht.

Gruß
lorf

Answer 5

Ich habe mal noch ein bischen geforscht und herausgekommen ist eine Mischung aus Abfrage und VBA.
Man kann nämlich auch eigene Funktionen in einer Abfrage unterbringen, die Werte aus dieser Abfrage verarbeitet.
Zuerst die Funktion:

Option Compare Database
Option Base 1

Function Monatprozent(AusleihTageGesamt As Integer, _
Startdatum As Date, Enddatum As Date, MonatAktuell As Integer)
Dim MonTage
MonTage = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

If (MonatAktuell < Month(Startdatum)) Then Exit Function
If (MonatAktuell > Month(Enddatum)) Then Exit Function
If Month(Enddatum) < Month(Startdatum) Then Exit Function 'Fehler
If AusleihTageGesamt = 0 Then Exit Function

If (Month(Startdatum) = Month(Enddatum)) Then 'Start der Ausleihe
AusleihtageImMonat = (Day(Enddatum) - Day(Startdatum))
Else
If (Month(Startdatum) = MonatAktuell) Then 'Ende der Ausleihe
AusleihtageImMonat = MonTage(MonatAktuell) - Day(Startdatum)
Else
If (Month(Enddatum) = MonatAktuell) Then 'Ende der Ausleihe
AusleihtageImMonat = Day(Enddatum)
Else
AusleihtageImMonat = MonTage(MonatAktuell)
End If
End If
End If

Monatprozent = AusleihtageImMonat / MonTage(MonatAktuell)
End Function

Jetzt die Abfrage mit den Funktionsaufrufen:

SELECT Verleih.von, Verleih.bis,
DateDiff("y",[Verleih]![von],[Verleih]![bis]) AS TageVerstrichen,
Monatprozent([TageVerstrichen],[von],[bis],1) AS Januar,
Monatprozent([TageVerstrichen],[von],[bis],2) AS Februar,
Monatprozent([TageVerstrichen],[von],[bis],3) AS März,
Monatprozent([TageVerstrichen],[von],[bis],4) AS April,
Monatprozent([TageVerstrichen],[von],[bis],5) AS Mai,
Monatprozent([TageVerstrichen],[von],[bis],6) AS Juni,
Monatprozent([TageVerstrichen],[von],[bis],7) AS Juli,
Monatprozent([TageVerstrichen],[von],[bis],8) AS August,
Monatprozent([TageVerstrichen],[von],[bis],9) AS September,
Monatprozent([TageVerstrichen],[von],[bis],10) AS Oktober,
Monatprozent([TageVerstrichen],[von],[bis],11) AS November,
Monatprozent([TageVerstrichen],[von],[bis],12) AS Dezember
FROM Verleih;

Man erhält die prozentuale Auslastung als Realzahl.

Wer Fehler findet, darf sich freuen und verbessern.

Gruß
lorf

Answer 6

Wow Lorf,

das ist ja super! Ich bin bisher daran verzweifelt! Werde mir Deinen Ansatz mal anschauen und versuchen, umzusetzen! Ich melde mich dann wieder!! Vielen, vielen Dank!!

LG

Answer 7

Hallo Sandra2910,

hier noch'n Lösungsansatz, damit schön bunt wird. Ich gehe hier über eine Zwischentabelle, die die Monatsauslasung für jedes Fahrzeug aufnimmt. Du brauchst zwei Tabellen

Tabelle Buchungen

ID autowert
FarzeugID longint
BuchungVon Datum
BuchungBis Datum

Tabelle Monatsauslastungen

ID autowert
BuchungID longint
Monat longint
Auslastung single

Die Tabelle Buchungen füllst du mit sinnvollen Werten (eine Prüfung auf Korrektheit der Daten nehme ich nicht vor). Danach startest du die Prozedure. Danach sollest du eine richtig gefüllte Zwischentabelle mit den Monatsauslastungen erhalten.
Ich hoffe du hast kein ACCESS 97 oder so, da ich mit ADODB arbeite. Einfach den gesamten Code in ein neues Modul kopieren.

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 Buchungen", 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!buchungvon, "mm.yyyy")), CDate("01." & Format(DateAdd("m", 1, rec1!buchungvon), "mm.yyyy")))
dVonAnz = DateDiff("d", CDate("01." & Format(rec1!buchungvon, "mm.yyyy")), rec1!buchungvon) + 1
dBisAnz = DateDiff("d", CDate("01." & Format(rec1!buchungbis, "mm.yyyy")), rec1!buchungbis) + 1
mVon = Format(rec1!buchungvon, "yyyymm")
mBis = Format(rec1!buchungbis, "yyyymm")
dVon = Format(rec1!buchungvon, "dd")
dBis = Format(rec1!buchungbis, "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!buchungvon, "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!buchungbis, "mm.yyyy")), CDate("01." & Format(DateAdd("m", 1, rec1!buchungbis), "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

Auch hier sind Korrekturen gern gesehen...

Viel Spass beim Ausprobieren.

Gruß
Ralf

Answer 8

Hallo ihr beiden,

ich habe mir das ganze noch mal angeschaut, habe aber noch eine Frage: wie schaffe ich es, einen Bezug zu meinen Spalten "Übergabe" (Datum der Fahrzeugübergabe im Format tt.mm.jjjj hh:MM:ss) und "Rückgabe" (Datum der Fahrzeugrückgabe, gleiches Format) zu erstellen? Diese Spalten befinden sich in der Tabelle "Disposition der Fahrzeuge".

Gehe ich richtig in der Annahme, dass eine Funktion ein Modul ist in Access??

Tausend Dank noch einmal im Voraus!!

VLG

Answer 9

Hallo Sandra2910,

ja das ist ein Modul in Access. Einfach neues Modul öffnen und den Inhalt durch den Code ersetzen. Danach am besten ber den Menüpunkt "Debuggen-->kompilieren von..." prüfen, ob einer da sind. Es könnte sein, dass z.B. noch Verweise fehlen.

Wenn du deine Tabelle verwenden willt, musst du im Code die Zeichenketten

Buchungen
durch
[Disposition der Fahrzeuge]

buchungvon
durch
Übergabe

und

buchungbis
durch
Rückgabe

ersetzen. Dann benötigst du noch ein Feld ID vom Typ autowert in deiner Tabelle oder du ersetzt rec1!ID durch deinen Referenzwert.

ZUm Schluss noch ein Tipp. Vermeide Leerzeichen und Umlaute in deinen Tabellen und Feldbezeichnungen. Das erspart die die [...]-Notation und weniger Probleme bei einer Portierung auf eine "richtige" Datenbank.

Viel Spass beim Ausprobieren.

Gruß
Ralf

Answer 10

Hallo Sandra,
den Bezug zu den Spalten deiner Tabelle stellst du in meiner Variante mit der Abfrage her, wobei du von durch Übergabe und bis durch Rückgabe ersetzt .
Abfragen - Neu - Entwurfsansicht
Disposition der Fahrzeuge anklicken - hinzufügen - schließen,
Übergabe und Rückgabe in je eine Spalte der Zeile Feld ziehen,
in eine Spalte daneben:

TageVerstrichen: DatDiff("y";[Übergabe];[Rückgabe])

einfügen,
in die Spalte daneben:

Januar: Monatprozent([TageVerstrichen];[Übergabe];[Rückgabe];1)

in die Spalte daneben:

Februar: Monatprozent([TageVerstrichen];[Übergabe];[Rückgabe];2)

usw. bis Dezember:

Dezember: Monatprozent([TageVerstrichen];[Übergabe];[Rückgabe];12)

Insgesamt sieht das denn so aus:

SELECT Übergabe, Rückgabe,
DateDiff("y",[Übergabe],[Rückgabe]) AS TageVerstrichen,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],1) AS Januar,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],2) AS Februar,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],3) AS März,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],4) AS April,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],5) AS Mai,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],6) AS Juni,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],7) AS Juli,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],8) AS August,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],9) AS September,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],10) AS Oktober,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],11) AS November,
Monatprozent([TageVerstrichen],[Übergabe],[Rückgabe],12) AS Dezember
FROM [Disposition der Fahrzeuge];

... dass eine Funktion ein Modul ist in Access??

Das kann sein, muss aber nicht. Funktionen, die irgendwie zusammen gehören, wird man möglichst in ein Modul tun, um alles beisammen zu haben.

Na denn viel Spass damit.

Gruß
lorf