PDA

Vollständige Version anzeigen : SQL-String erweitern


horse79
15.04.2002, 07:53
Hi,

folgender String (Danke an Kurt aus K.)

SELECT *
FROM Einsatzstellen AS A
WHERE NOT EXISTS (SELECT *
FROM Einsatzplanung B
WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle
AND B.DatumVon <= #01/01/02#
AND B.DatumBis >= #31/01/02#);

Wie bekomme ich auc diejenigen DS angezeigt die zB. DatumVon 01.01.02 und DatumBis 05.01.02 oder DatumVon 06.01.02 und DatumBis 31.12.02 haben?

Funktionieren tut z.B. 06.01.02 bis 15.01.02...
(Das ist auch gut so... <img src="graemlins/lachen.gif" border="0" alt="[Lachen]" /> )

Vielen Dank euch <img src="graemlins/top.gif" border="0" alt="[Finger hoch]" />

erwin
15.04.2002, 09:31
generelle Idee zu deinem Problem, in etwas so:

1. Tab Hilfstage
- TagX (Integer, Werte 1..31)
2. Tab HilfsMonate
- MonatX (Integer, Werte 1..12)

3. Abfrage:
SELECT Datserial(TagX,MonatX,Year(Now())
FROM HilfsMonate, HilfsTage
WHERE IsDate(Year(Now()) & "-" & MonatX & "-" & TagX);

Mittels dieser Abfrage + Outer-Join auf "Einsatzplanung" bekommst du dann alle Tage an denen nichts geplant ist (jeweils für's aktuelle Jahr - lässt sich mittels weiterer Hilfstab. aber auch für beliebige Jahre erweitern).

so long erwin...

horse79
15.04.2002, 09:58
Merci,

die Berechnung der Tage ist kein Problem. Das habe ich alles in Globalen Variablen abgespeichert. GLOBALDatumVon und GlobalDatumBis.
Um was es mir geht ist nur den SQL-String zu erweitern, damit ich die gewünschten DS angezeigt bekomme... Und da brauche ich eben alle innerhalb eines Monates...

erwin
15.04.2002, 12:59
entweder hab' ich jetzt dein Problem nicht ganz verstanden, oder du meinen gedanklichen Ansatz. Ich bezog mich auch auf deinen alten Thread (mit dem Tipp von Kurt), wobei es doch darum geht innerhalb eines bestimmten Zeitraums was rauszufinden, wofür noch "nix" da ist. Und da die Planung Von-Bis erfolgt, glaube ich, du wirst mit einer einzigen NOT EXISTS - Subquery nicht ganz hinkommen - aber vielleicht schilderst du das Problem nochmals genauer.

so long erwin...

horse79
15.04.2002, 13:04
OK, sorry.

Dann probiers ich mal genauer:

Aktuell funktioniert das alles ja klasse. Machen wir ein Beispiel. Nehmen wir den Januar2002. Jetzt möchte ich da alle DS bei denen eben nix verteilt ist. Also alle Einsatzstellen die man noch mit Leuten belegen kann... Damit fülle ich ein Listenfeld mit dem der User die freie Einsatzstelle per doppelklick in ein endlosform zieht und dann das datum von bis eingibt. Gibt er 01.01.2002 und 31.01.2002 ein, dann verschwindet richtigerweise die Einsatzstelle aus meinem Listenfeld. Wenn er aber 05.01.2002 und 15.01.2002 eingibt soll sie eben nicht verschwinden, da ja noch Tage vom Monat übrig sind. Am besten wäre dann natürlich zusätzlich zur Anzeige im Listenfeld noch die verbelibenden Tage die die Einsatzstelle noch Freiraum hat. Ich hoffe das ich das halbwegs klar ausgedrückt habe...

Kurt aus Kienitz
15.04.2002, 14:40
<font face="Comic Sans MS">Hallo zusammen,

Sorry, das geht wohl auf meine Kappe.
Aus #31/01/01# wird 01.01.1931 und da passen die Daten nicht.
Ich habe ein wenig rumexperementiert und aus #01/01/31# wird auch der 01.01.1931 ?!?!

Folgendes funktioniert aber einwandfrei:

SELECT *
FROM tblEinsatzstelle AS A
WHERE NOT EXISTS(SELECT *
FROM tblPlanung B
WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle
AND B.DatumVon <= #2001-01-01# AND B.DatumBis >= #2001-01-31#);

horse79
15.04.2002, 15:12
Vielen Dank,

das klappt schon besser.

Jetzt folgendes:

DS1: PK_Einsatzstelle 7 01.01.2002 - 04.01.2002
DS2: PK_Einsatzstelle 7 05.01.2002 - 20.01.2002
DS3: PK_Einsatzstelle 7 21.01.2002 - 31.01.2002

In diesem Fall soll ja wieder keine Einsatzstelle kommen, im Moment erscheint aber die Einsatzstelle 7 noch zur Auswahl. Ist ja eigentlich logisch, da ja bei DS 1,2,3 noch Tage frei sind und mit not exists kommt aus den dreien halt nur noch einer...

Bei
DS1: PK_Einsatzstelle 7 01.01.2002 - 04.01.2002
DS2: PK_Einsatzstelle 7 05.01.2002 - 20.01.2002
solle aber wieder die 7 zur Auswahl kommen, da ja hier noch Tage zur Verfügung stehen...

Vielen Dank

erwin
15.04.2002, 15:29
dann habe ich dich schon einigermassen richtig verstanden + bleibe nach wie vor bei meiner Aussage, dass du das SO nicht hinbekommst (vielleicht hat Kurt ja noch einen "Gedankenblitz" *g*).

Ich würde vorschlagen du nimmst meine o.a. Hilfsquery + darauf deine "positive" Datumsselektion

= alle Tage im betrachteten Zeitraum

davon schliesst du nun per Query jene aus, welche (bzgl. einer belieb. Einsatzstelle) in deren Von-Bis-Datumsbereich liegen.

= je Tag ein Datensatz zur NICHT verplanten Einsatzstelle

und genau davon darf KEIN Datensatz existieren.

so long erwin...

Kurt aus Kienitz
15.04.2002, 15:42
<font face="Comic Sans MS">Hallo :)

Einen hab ich noch...einen hab ich noch.....

Erstelle mal folgende Abfrage:

Abfrage: tblPlanungAbfrage
'
SELECT tblPlanung.PK_Einsatzstelle, Min(tblPlanung.DatumVon) AS DatVon, Max(tblPlanung.DatumBis) AS DatBis, Format(tblPlanung.DatumVon,'yyyy.mm') AS Monat
FROM tblPlanung
GROUP BY tblPlanung.PK_Einsatzstelle, Format(tblPlanung.DatumVon,'yyyy.mm');

Und ändere die, die Du bereits hast so ab:

SELECT *
FROM tblEinsatzstelle AS A
WHERE NOT EXISTS(SELECT *
FROM tblPlanungAbfrage B
WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle
AND B.DatVon <= #2001-01-01#
AND B.DatBis >= #2001-01-31#);

erwin
15.04.2002, 15:55
hmmmmm...

01.01.2002 - 04.01.2002
20.01.2002 - 31.01.2002

führt dann aber zur Aussage "Monat_voll_verplant".

Ich denke nicht, dass man eine Positiv- und eine Negativ-Selektion in einem einzigen NOT EXIST abhandeln kann... (s.o.)

so long erwin...

horse79
15.04.2002, 16:01
Hi,

bekomme foglende Fehlermeldung im ersten String:

'Format(EinsatzPlanung.DatumVon,'yyyy.mmm')' ist nicht Teil einer Aggregatfunktion oder einer Gruppierung

???

Mein String:

SELECT EinsatzPlanung.PK_Einsatzstelle, Min(EinsatzPlanung.DatumVon) AS DatVon, Max(EinsatzPlanung.DatumBis) AS DatBis, Format(EinsatzPlanung.DatumVon,'yyyy.mm') AS Monat
FROM EinsatzPlanung
GROUP BY EinsatzPlanung.PK_Einsatzstelle, Format(Planung.DatumVon,'yyyy.mm');

horse79
15.04.2002, 16:03
Hab Fehler gefunden...

Teste es und meld mich wieder...

horse79
15.04.2002, 16:06
Funktioniert, WOW Kurt, wenn Du mir noch die funktionsweise Deiner beiden Strings erklären könntest... Das ich als Laie auch kapier was läuft.

Vielen Dank euch beiden.

erwin
15.04.2002, 16:07
...GROUP BY EinsatzPlanung.PK_Einsatzstelle, Format(EINSATZPlanung.DatumVon,'yyyy.mm');

da fehlt was, aber es wird sowieso nicht ganz so, wie beabsichtigt, funktionieren (s.o.)

slg erwin...

Kurt aus Kienitz
15.04.2002, 16:08
<font face="Comic Sans MS">Hallo Erwin,

Das mit dem "voll geplant" habe ich dort herausgelesen:
<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>
DS1: PK_Einsatzstelle 7 01.01.2002 - 04.01.2002
DS2: PK_Einsatzstelle 7 05.01.2002 - 20.01.2002
DS3: PK_Einsatzstelle 7 21.01.2002 - 31.01.2002

In diesem Fall soll ja wieder keine Einsatzstelle kommen, im Moment erscheint aber die Einsatzstelle 7 noch zur Auswahl. Ist ja eigentlich logisch, da ja bei DS 1,2,3 noch Tage frei sind und mit not exists kommt aus den dreien halt nur noch einer...
<hr></blockquote>
Mal sehen was Sven sagt ;)

Grüße Kurt

erwin
15.04.2002, 16:09
@horse79

es funktioniert - kann ich nicht ganz glauben (bei einer Datenkonstellation wie oben von mir zB. aufgezeigt ?!)

@kurt

dann lass mal den "mittleren" DS weg dh. Min(...) = 1.1.2002 und Max(...) = 31.1.2002 **grübel** zusammen mit dem NOT EXIST **noch mehr grübel** ...

slg erwin...

[ 15. April 2002: Beitrag editiert von: erwin ]</p>

horse79
15.04.2002, 16:11
stimmt, vorschnell gejubelt. Sorry

Kurt aus Kienitz
15.04.2002, 18:27
<font face="Comic Sans MS">Hallo Erwin,

<img src="graemlins/top.gif" border="0" alt="[Finger hoch]" /> super, habe ich in der Tat nicht gesehen.

Danke und Gruß
Kurt

horse79
16.04.2002, 07:41
@Erwin:

wie meintest Du mit dem mittleren weglassen?

erwin
16.04.2002, 09:08
ich meinte, dass Kurt's letzte Variante zwar in dem von ihm geposteten Beispiel mit den 3 Datensätzen funktioniert, wenn man davon aber den DS2 (=den mittleren) weglässt, dann funktioniert's nicht.

so long erwin...

horse79
16.04.2002, 09:11
OK, dann wir mir wohl nichts anderes übrigbleiben als, die Tage manuell ausrechnen und dann überprüfen ob diese alle schon belegt sind...

Hätte aber auch den Vorteil, dass ich im Listenfeld noch die Info anbieten kann wieviele Tage frei sind...

erwin
16.04.2002, 12:59
da klappt doch bei dem von mir vorgeschlagenen Vorgehen auch...

slg erwin...

horse79
16.04.2002, 14:48
<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>
1. Tab Hilfstage
- TagX (Integer, Werte 1..31)
2. Tab HilfsMonate
- MonatX (Integer, Werte 1..12)

3. Abfrage:
SELECT Datserial(TagX,MonatX,Year(Now())
FROM HilfsMonate, HilfsTage
WHERE IsDate(Year(Now()) & "-" & MonatX & "-" & TagX);

Mittels dieser Abfrage + Outer-Join auf "Einsatzplanung" bekommst du dann alle Tage an denen nichts geplant ist (jeweils für's aktuelle Jahr - lässt sich mittels weiterer Hilfstab. aber auch für beliebige Jahre erweitern).

<hr></blockquote>

Wie würdes Du dann die Hilfstabelle erstellen. Immer neu per Recordset oder?? Soll der Outer-Join gleich in den SQL eingebaut werden, oder danach extra aufgerufen werden...

Vielen Dank

erwin
16.04.2002, 15:33
ich glaube du durchblickst noch nicht so recht, wie ich das meine: Die beiden Hilfstab. sind dafür da, dir MIT EINER FIXEN Eingabe von 31 (Tage) bzw. 12 (Monate) Tabeinträgen AUTOMATISCH ALLE Tage eines Jahres in Abfrageform zu liefern.

Wobei in meiner Query noch ein Fehler ist <img src="graemlins/nene.gif" border="0" alt="[Nein Nein]" /> , also nachdem du die beiden Hilfstab. gemacht hast + mit Daten 1..31 bzw. 1..12 gefüllt hast, dann liefert dir:

SELECT Dateserial(Year(Now()), MonatX, TagX) AS DerTag
FROM HilfsMonate, HilfsTage
WHERE IsDate(Year(Now()) & "-" & MonatX & "-" & TagX);

Damit hast du mal deine "positive" Grundmenge aller Tage des Jahres - ohne jedesmal irgendeine Kalendertabelle ergänzen zu müssen.

Dann verbindest du die Stellen mit o.a. Abfrage "AlleTage" ca. wobei du direkt den "gewünschten" Datumsbereich eingrenzt ca. so:

Select PK_Einsatzstelle, DerTag
FROM Einsatzstellen, AlleTage
WHERE DerTag BETWEEN #VONj-mm-tt# AND #BISj-mm-tt#;

Auf dieser Query "AlleTageUndStellen" dann erfolgt der Ausschluss ca. so:

SELECT * FROM AlleTageUndStellen A
WHERE NOT EXISTS
(SELECT * FROM tblPlanungAbfrage B
WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle
AND A.DerTag BETWEEN B.DatVon AND B.DatBis)

liefert dir für alle Einsatzstellen im Zeitraum #VONj-mm-tt# AND #BISj-mm-tt# alle NICHT verplanten Tage. Wenn's nur die Anzahl sein soll, dann i.d. letzen Abfrage eben statt * ein Count(*).

HTH erwin...

horse79
16.04.2002, 15:52
Wow <img src="graemlins/top.gif" border="0" alt="[Finger hoch]" /> ,

vielen Dank. Ja, jetzt verstehe ich wo Du hinaus willst <img src="graemlins/idee.gif" border="0" alt="[Idee]" /> . Ich werde es in Ruhe testen und melde mich dann wieder...

horse79
17.04.2002, 07:38
Vielen Dank,

das liefert genau mein gewünschtes Ergebnis...

Auf
SELECT * FROM AlleTageUndStellen A
WHERE NOT EXISTS
(SELECT * FROM tblPlanungAbfrage B
WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle
AND A.DerTag BETWEEN B.DatVon AND B.DatBis)

habe ich dann noch nen Query aufgesetzt der den ErstenWert von PK_Einsatzstelle und die Anzahl von DerTag liefert. Das ist jetzt meine Datenherkunft für mein Listenfeld...

Jetzt ist mir noch was eingefallen was toll wäre, aber ich noch nicht genau weiß wie ich das anstellen soll. Der User will unbedingt noch eine Jahresübersicht als Bericht... Der soll so aussehen:

XXXXX Januar Februar März .....
Stelle1 MA1 MA5 MA87
Stelle2 MA7 MA1 MA8
Stelle3 MA4+MA9

Wie mache ich denn sowas :confused:
In meiner Tabelle Einsatzplanung habe ich ja die Felder PK_Einsatzstelle PK_Mitarebeiter DatumVon DatumBis

THANX
<img src="confused.gif" border="0">

erwin
17.04.2002, 08:07
hmmm... müsste sich mit Kreuztab.Query mit fixierten Spaltenüberschriften, als Basis des Reports, eigentlich irgendwie hinbekommen lassen.

so long erwin,..

horse79
17.04.2002, 11:41
Hab jetzt ein bischen rumgespielt, kriegs aber nicht so ganz hin...

Könntest Du mir einen kleinen Denkanstoß geben?

erwin
17.04.2002, 12:34
als einziges Problem sehe ich, dass es ohne aufwändige Trickserei nicht klappen wird die MA für eine Stelle (Zeile) sowie einen Monat (Spalte) NEBENEINANDER (MA4711+MA0815) zu haben. Allerdings finde ich es auch optisch schöner je Stelle auch die (eventuell mehreren) MA in einzelnen Zeilen zu positionieren - wo soll dann noch ein Problem sein ?

so long erwin...

horse79
17.04.2002, 13:05
Das Problem liegt in meinen bescheidenen Fähigkeiten, ich krieg die Daten nicht aus der Abfrage raus. Also von links nach rechts die Monate, von oben nach unten die Einsatzstellen...
Mit dem Kreuztabellenassistent will er ja immer etwas berechnen oder soll ich da anders ran gehen.

erwin
18.04.2002, 08:27
jetzt hab ich auch was übersehen: DatumVon und DatumBis liegen sicher immer im selben Monat ? Sonst kann das sowieso nicht klappen !

slg erwin...

horse79
18.04.2002, 09:01
Ja, die liegen sicher im selben Monat...
Der User kann immer nur pro Monat die Daten setzen. Wenn er 01.01.02 - 28.02.02 setzen will, muß er 2 DS anlegen:

DS1 01.01.02 - 31.01.02
DS2 01.02.02 - 28.02.02

erwin
18.04.2002, 09:19
ich hab mal angekürzt:
t = Tabellenname
e = Stelle
m = Mitarbeiter
d = datum (egal ob von oder bis)

TRANSFORM Count(t.m) AS [Anzahl von m]
SELECT t.e, t.m
FROM t
GROUP BY t.e, t.m
PIVOT Format([d],"m") In (1,2,3,4,5,6,7,8,9,10,11,12);

so long erwin...

horse79
18.04.2002, 09:43
TRANSFORM Count(Einsatzplanung.PK_Azubi) AS [Anzahl von m]
SELECT Einsatzplanung.PK_Einsatzstelle, Einsatzplanung.PK_Azubi
FROM Einsatzplanung
GROUP BY Einsatzplanung.PK_Einsatzstelle, Einsatzplanung.PK_Azubi
PIVOT Format(#2001-01-01#,"m") In (1,2,3,4,5,6,7,8,9,10,11,12);

Das füllt mir ja nur die Spalte 1 (Januar) aus. Wie bekomme ich das ganze Jahr in die Abfrage?

Acess legt auch nicht nur eine Zeile für PK_Einsatzstelle an sondern viele...?

erwin
18.04.2002, 09:50
die Pivotisierung nach einer Konstanten (#2001-01-01#) ist natürlich Blödsinn - da muss entweder das VON- oder das BIS-Datum rein. Und das Problem mit den "vielen Azubis je Stelle", hab' ich doch oben schon angesprochen, das klappt IMHO mit reinem SQL nicht. Dh. du müstest zuerst eine Public-Function schreiben, welche dir bezogen auf eine Stelle + einen Monat alle Ma_Nrn.(Azubis) in einem String ausgibt - dann diese Fu. in eine gruppierte Abfrage (Stelle/Monat) einbauen + darauf dann die Kreuztab.Abfrage setzen.

so long erwin...

horse79
18.04.2002, 10:00
aber im DatumVon würde doch auch nichts anderes stehen als #2001-01-01#, oder was müsste Deiner Meinung nach in die Variable?

Mit der Funktion werde ich mal rumbasteln...

erwin
18.04.2002, 10:17
häää - in dem TABELLENFELD werden doch wohl auch andere Werte als #2001-01-01# sein - oder ?

slg erwin...

horse79
18.04.2002, 10:26
Uuups, ja natürlich. Ich bin jetzt von einer globalen Variable ausgegangen, so jetzt schaut das etwas besser aus:

TRANSFORM Count(Einsatzplanung.PK_Azubi) AS [Anzahl von m]
SELECT Einsatzplanung.PK_Einsatzstelle, Einsatzplanung.PK_Azubi
FROM Einsatzplanung
GROUP BY Einsatzplanung.PK_Einsatzstelle, Einsatzplanung.PK_Azubi
PIVOT Format(Einsatzplanung.DatumVon,"m") In (1,2,3,4,5,6,7,8,9,10,11,12);

Jetzt füllt er mir brav alle Monate aus...

Mein Ergebnis:

Es gibt für jede Einsatzstelle soviele DSe wie Azubis in diesem Jahr in der Stelle eingeplant sind. In jedem DS steht dann eine 1 in demjenigen Monat in dem der Azubi diese Stelle besucht...

Jetzt habe ich leider noch nicht ganz verstanden was Du weiter oben meintest. Das bezog sich doch darauf, wenn in einem Monat mehrere Azubis in einer Einsatzstelle sind?

Oder kann ich den SQL so setzen das er mir nur einen DS pro Einsatzstelle anzeigt und den PK_Azubi in die Felder 1-12 der Kreuztabelle schreibt. (Gehen wir mal davon aus dass nur EIN Azubi PRO Monat verteilt ist).

Wenn ich dann mehrere pro Monat habe muß ich diese halt in einen String schreiben wie Du ja beschrieben hast, wenn meine Interpretation richtig war.

Vielen Dank

erwin
18.04.2002, 11:14
Wenn dir EINER reicht bzw. in einem Monat sowieso nur EINER an EINER Stelle ist, dann kannst du doch auch eine beliebige Funktion (natürlich nicht Count ;) ) auf den Azubi anwenden und ihn als Zeilenüberschrift weglassen.

zB. Min(Azubi) oder Max(Azubi) oder First(Azubi) würde klappen.

Aber wie gesagt - auch wenn's mehrere sind, siehst du dann immer nur EINEN ! Für alle brauchst du die von mir o.a. Public-Function.

so long erwin...

horse79
18.04.2002, 11:21
TRANSFORM First(Einsatzplanung.PK_Azubi) AS [Anzahl von m]
SELECT Einsatzplanung.PK_Einsatzstelle
FROM Einsatzplanung
GROUP BY Einsatzplanung.PK_Einsatzstelle
PIVOT Format(Einsatzplanung.DatumVon,"m") In (1,2,3,4,5,6,7,8,9,10,11,12);

Dann kommt es so wie gewollt, dann werde ich mal mit der Funktion rumspielen.

Vielen Dank erst mal...

horse79
22.04.2002, 19:03
Hallo,

<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>
Und das Problem mit den "vielen Azubis je Stelle", hab' ich doch oben schon angesprochen, das klappt IMHO mit reinem SQL nicht. Dh. du müstest zuerst eine Public-Function schreiben, welche dir bezogen auf eine Stelle + einen Monat alle Ma_Nrn.(Azubis) in einem String ausgibt - dann diese Fu. in eine gruppierte Abfrage (Stelle/Monat) einbauen + darauf dann die Kreuztab.Abfrage setzen.
so long erwin...

<hr></blockquote>


ich hab jetzt folgende Funktion geschrieben:

<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr> Public Function Jahresplan(Monat As Variant, Einsatzstelle As Variant) As String

On Error GoTo Fehler

Dim rst As Recordset
Dim mdb As DATABASE
Dim SQL As String
Dim Startdatum As Date
Dim Endedatum As Date
Dim Azubi As Variant

SQL = "SELECT DISTINCTROW Einsatzplanung.PK_Einsatzstelle, Azubidaten.Name, Azubidaten.Vorname, Einsatzplanung.DatumVon, Einsatzplanung.DatumBis FROM Azubidaten INNER JOIN Einsatzplanung ON Azubidaten.PK_Azubi = Einsatzplanung.PK_Azubi ORDER BY Einsatzplanung.PK_Einsatzstelle;"
'SQL ist sortiert nach PK_Einsatzstelle

Startdatum = DateAdd("m", 0, DateValue(Monat)) 'Berechnung des ersten eines Monates
Endedatum = DateAdd("m", 1, DateValue(Monat)) - 1 'Berechnung des letzteb eines Monates
'MsgBox Startdatum & " " & Endedatum

Set mdb = CurrentDb()
Set rst = mdb.OpenRecordset(SQL)

Do While Not rst.EOF
If rst!DatumVon >= Startdatum Then
If rst!DatumVon &lt;
= Endedatum Then
'MsgBox Einsatzstelle & " " & rst!PK_Einsatzstelle
Dim Ergebnis As Variant
Ergebnis = Einsatzstelle - rst!PK_Einsatzstelle
If Ergebnis = 0 Then
If Azubi = "" Then
Azubi = rst!name
Else
Azubi = Azubi & " und " & rst!name
End If
End If
End If
End If
rst.MoveNext
Loop

rst.Close
mdb.Close

Ende:
Set rst = Nothing
Set db = Nothing
Jahresplan = Azubi
Exit Function

Fehler:
MsgBox "Es ist ein Fehler aufgetreten..." & Chr(13) & Chr(13) & "Beschreibung des Fehlers:" & Chr(13) & Chr(13) & Err.Description & Chr(13) & Chr(13) & "Der Fehler hat die Nummer: " & Err.Number, vbCritical, "Sicherheitssystem der Azubiverwaltung"
MsgBox "Dieser Fehler trat im Modul Jahresplan auf..." & Chr(13) & "Bitte verständigen Sie den Administrator.", vbInformation, "Sicherheitssystem der Azubiverwaltung"

GoTo Ende

End Function <hr></blockquote>

Jetzt habe ich das Problem wie ich diese Funktion in die Abfrage einbaue?

Kannst Du mir da helfen?
Ich hab da leider nur Syntaxfehler bekommen.
Im VBA mit Übergabe der beiden Parameter funktioniert diese ohne Probleme...

Vielen Dank

Sven

erwin
22.04.2002, 19:46
ganz einfach:

SELECT Jahresplan(DatumVon, PK_Einsatzstelle), .... FROM ...

oder was geht nicht ?

so long erwin...

horse79
22.04.2002, 19:57
eigentlich schon...

<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>
SELECT Jahresplan(DatumVon,PK_Einsatzstelle) AS Ausdr1
FROM Einsatzplanung
WHERE (((Einsatzplanung.DatumBis)&lt;=#1/31/02#) AND ((Einsatzplanung.DatumVon)>=#1/1/02#));
<hr></blockquote>

Dann zeigt er mir das richtig...

Aber wie bring ich da die Kreuztabelle drauf:

<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>

TRANSFORM First([Azubidaten].[Name] & " " & [Azubidaten].[Vorname]) AS [Anzahl von m]
SELECT Einsatzstellen.Name
FROM (Einsatzplanung INNER JOIN Azubidaten ON Einsatzplanung.PK_Azubi = Azubidaten.PK_Azubi) INNER JOIN Einsatzstellen ON Einsatzplanung.PK_Einsatzstelle = Einsatzstellen.PK_Einsatzstelle
WHERE (((Einsatzplanung.DatumVon)>=#1/1/02#) AND ((Einsatzplanung.DatumBis)&lt;=#12/31/02#))
GROUP BY Einsatzstellen.Name
PIVOT Format(Einsatzplanung.DatumVon,"m") In (1,2,3,4,5,6,7,8,9,10,11,12);
<hr></blockquote>


Die erste Abfrage liefert mir ja die Namen für einen Monat als String. z.B.:
3 DS:
DS1: Azubi 1
DS2: Azubi 3
DS3: Azubi 2 und Azubi 14

Da bin ich noch ein bischen ratlos...

erwin
24.04.2002, 11:08
hab' ich doch "oben" schon geschrieben:

...dann diese Fu. in eine gruppierte Abfrage (Stelle/Monat) einbauen + darauf dann die Kreuztab.Abfrage setzen.

Also erst noch eine Query nach Stelle, JJJJ-MM, und DeineFu(...) gruppierte Abfrage, darauf dann die Kreuztab.Abfrage.

so long erwin...

horse79
29.04.2002, 15:06
OK, das habe ich hinbekommen...

Jetzt hab ich nur noch ein riesen Performance-Problem.

Folgende Funktion berechnet meine freien Einsatzstellen mit Anzahl der Tage pro Monat um die Datenherkunft für das Listenfeld herzustellen. Diese wird im Formular immer ausgelöst, wenn man einen anderen Monat auswählt. Also recht oft.

Nur steigt die DB-Größe bei jedem ausführen um 40 KB. Ich denke das hängt damit zusammen, dass per SQL 4 Tabellen bearbeitet werden. Wie kann ich das schneller machen und die Aufblähung verhindern? Geht das auch per Recordset?

Hier meine Funktion:

Public Function unverplanteTageEinerEinsatzstzelleEinesMonates()

DoCmd.SetWarnings False

'schritt1
Dim DerTagVar As Variant
Dim Bedingung As Variant
Dim JAHRESZAHL As Variant
JAHRESZAHL = Right(Forms!frmEinsatzplanung!Datum, 4)

If IsNull(JAHRESZAHL) Then
MsgBox "Bitte wählen Sie ein Datum aus...", vbInformation
Exit Function
End If

DerTagVar = "DateSerial(" & JAHRESZAHL & ", MonatX, TagX)"
Bedingung = "IsDate(" & JAHRESZAHL & " & ' - ' & MonatX & ' - ' & TagX)"
DoCmd.RunSQL "SELECT " & DerTagVar & " AS DerTag INTO Temp_Schritt1 FROM HilfsMonate, HilfsTage WHERE " & Bedingung & ";"

'schritt2
Monat = Forms!frmEinsatzplanung!Datum
AnfangUndEndeEinesMonatesBerechnen (Monat)
'die Variablen GLOBALAnfangMonat und GLOBALEndeMonat werden durch vorige Funktion gefüllt
DoCmd.RunSQL "SELECT PK_Einsatzstelle, DerTag INTO Temp_Schritt2 FROM Einsatzstellen, Temp_Schritt1 WHERE DerTag between " & GLOBALAnfangMonat & " and " & GlobalEndeMonat & ";"

'schritt3
DoCmd.RunSQL "SELECT * INTO Temp_Schritt3 FROM Temp_Schritt2 AS A WHERE (((Exists (SELECT * FROM Einsatzplanung B WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle AND A.DerTag BETWEEN B.DatumVon AND B.DatumBis))=False));"

'schritt4
Forms!frmEinsatzplanung!Liste4.RowSource = "" 'Tabelle freigeben

DoCmd.RunSQL "SELECT DISTINCTROW First(Temp_Schritt3.PK_Einsatzstelle) AS [ErsterWert von PK_Einsatzstelle], Einsatzstellen.Name, Count(Temp_Schritt3.DerTag) AS unverplanteTage INTO Temp_Schritt4 FROM Temp_Schritt3 INNER JOIN Einsatzstellen ON Temp_Schritt3.PK_Einsatzstelle = Einsatzstellen.PK_Einsatzstelle GROUP BY Einsatzstellen.Name;"

Forms!frmEinsatzplanung!Liste4.RowSource = "Temp_Schritt4"

End Function

Vielen Dank

Sven

erwin
29.04.2002, 15:29
huch - was treibst du denn da !

so wie ich das sehe müsste man doch nur in der (oben geposteten) Query "AlleTageUndStellen" die Tage auf einen Monat + ggf. auch noch die Einsatzstelle einschränken - ich sehe da niergends eine Notwendigkeit mit temp.Tab's zu arbeiten ?!

Select PK_Einsatzstelle, DerTag
FROM Einsatzstellen, AlleTage
WHERE DerTag BETWEEN PublicFuVon() AND PublicFuBis();

mit 2 Public Functions, die dir das VON- BIS-Datum (=gobals / public Var.) als String mit Trennz. i.d. Form "#yyyy-mm-dd#" zurückgeben.

Auf dieser Query "AlleTageUndStellen" dann eben - wie auch schon gepostet - noch gruppieren + ausschliessen ca. so:

SELECT A.PK_Einsatzstelle, count(*) AS AnzahlTageUngeplant
FROM AlleTageUndStellen A
WHERE NOT EXISTS
(SELECT * FROM tblPlanungAbfrage B
WHERE B.PK_Einsatzstelle = A.PK_Einsatzstelle
AND A.DerTag BETWEEN B.DatVon AND B.DatBis)
GROUP BY A.PK_Einsatzstelle;

so long erwin...

horse79
29.04.2002, 15:57
Dann muß aber die Query AllleTageundStellen als Abfrage gespeichert sein? Ich hatte das Problem und wollte halt schritt für schritt auf den SQL-Strings aufbauen. Also in Schritt 2 ...From SQL1... und in Schritt 3 ...From SQL2...

Aber das ging irgendwie nicht. Und Variablen hab ich irgendwie nicht in einer gespeicherten Query zum laufen gebracht. Das hat nur in VBA hingehaut. Drum die obere Lösung...

<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>
mit 2 Public Functions, die dir das VON- BIS-Datum (=gobals / public Var.) als String mit Trennz. i.d. Form "#yyyy-mm-dd#" zurückgeben.
<hr></blockquote>

Könntest Du mir das genauer erklären? Da steh ich noch aufm Schlauch...

[ 29. April 2002: Beitrag editiert von: horse79 ]</p>

erwin
29.04.2002, 16:37
latürnich sollst du die einzelnen SQL's als Query speichern !


' Deklarationsbreiche eines allg. Moduls
Public GstrDeinVonDatSQL as String
Public GstrDeinBisDatSQL as String
'
Public Function getDeinVonDatSQL() as string
getDeinVonDatSQL = GstrDeinVonDatSQL
end function
' analog für BIS-Datum
'
' dann im Formular-Code die Var. setzen:
Sub DeinDatumFeld_AfterUpdate()
' simples Beispiel:
GstrDeinVonDatSQL = "#" & format(DeinDatumFeld,"yyyy-mm-dd") & "#"
' ansonsten: ggf. irgendwie VON/BIS aus Jahr/Monat berechnen...


I.d.Query kannst du dann die Funktion
getDeinVonDatSQL()
verwenden wie o.a.

slg erwin...

horse79
02.05.2002, 07:51
Hi,

des mit den Funktionen klappt leider nicht. Schreib ich das Datum manuell in die Query:

SELECT PK_Einsatzstelle, DerTag
FROM Einsatzstellen, AlleTage
WHERE DerTag between #2002-01-01# and #2002-01-31#;

dann geht das...

Baue ich die Funktionen ein:

SELECT Einsatzstellen.PK_Einsatzstelle, AlleTage.DerTag
FROM Einsatzstellen, AlleTage
WHERE (((AlleTage.DerTag) Between fktDatumvon() And fktdatumvon()));

Dann geht das nicht:

Hier die Funktionen (Das Datum steht zu Testzwecken drin; später kommt da das Feld ausm Form rein...):

Public Function fktDatumBis() As String

Dim EndeMonat As String
Dim Monat As String

Monat = "Januar 2002"

EndeMonat = DateAdd("m", 1, DateValue(Monat)) - 1

fktDatumBis = "#" & Format(EndeMonat, "yyyy-mm-dd") & "#"

End Function

ublic Function fktDatumVon() As String

Dim AnfangMonat As Variant
Dim Monat As String

Monat = "Januar 2002"

AnfangMonat = DateAdd("m", 0, DateValue(Monat))

fktDatumVon = "#" & Format(AnfangMonat, "yyyy-mm-dd") & "#"

End Function

Wenn ich die Funktionen über VBA aufrufe, dann liefert Sie die richtigen Daten...

Woran liegt das?

Merci

Sven

erwin
02.05.2002, 08:55
sorry - da ist mir in der Eile ein Fehler unterlaufen:

Wenn du Funktionen so einbindest, dann müssen sie natürlich den korrekten Datentyp liefern - also Date - zB.:

public function VonDatum() as Date
VonDatum = DateSerial(nz(forms!deinform!dasjahr,1900), nz(forms!deinform!dasmonat,1), 1)
end function

slg erwin...

horse79
02.05.2002, 10:02
Public Function fktDatumVon() As Date

Dim AnfangMonat As Date

Dim Monat As String

Monat = "Januar 2002"

AnfangMonat = DateAdd("m", 0, DateValue(Monat))

fktDatumVon = DateSerial(Nz(AnfangMonat, 1900), Nz(AnfangMonat, 1), 1)

Da bekomm ich einen Überlauf? (in der letzten Zeile...)

Mit Dateadd wandel ich doch in Datum um?

[ 02. Mai 2002: Beitrag editiert von: horse79 ]</p>

horse79
02.05.2002, 13:38
Hi,

folgende Funktionen langen auch:
(Funktioniert zumindest jetzt bei mir oder hälst Du DateSerial für zwingend erforderlich :confused: )

Public Function fktDatumBis() As Date

Dim EndeMonat As Date
Dim Monat As String

Monat = "Februar 2002"

fktDatumBis = DateAdd("m", 1, DateValue(Monat)) - 1

End Function


Public Function fktDatumVon() As Date

Dim AnfangMonat As Date
Dim Monat As String

Monat = "Februar 2002"

fktDatumVon = DateAdd("m", 0, DateValue(Monat))

End Function

Sven

[ 02. Mai 2002: Beitrag editiert von: horse79 ]</p>

erwin
02.05.2002, 22:12
NEIN - Dateserial ist sicher nicht "zwingend erforderlich" aber du verwendest da die Datentypen etwas (naja) seltsam ;)

Ich bin davon ausgegangen, dass du 2 Felder am Formular hast: Jahr + Monat

Dann klappt (ohne Public Variable - allerdings programmatisch "nicht ganz sauber") die von mir gepostete Zuweisung i.d. Function "VonDatum".

Wenn du am Formular 1 Datumsfeld (zwecks Auswahl) hast, dann ca. so (auch nicht ganz sauber *g*):

public function VonDatum() as date
dim hDat as date
hDat = nz(forms!DeinForm!DasDatum, #1900-01-01#)
VonDatum = dateserial(year(hDat), month(hDat), 1)
end function

public function BisDatum() as date
dim hDat as date
hDat = nz(forms!DeinForm!DasDatum, #2299-01-01#)
hDat = dateserial(year(hDat), month(hDat), 1)
BisDatum = Dateadd("m", 1, hDat) - 1
end function

"Sauberer" wäre es mit Public Var. zu arbeiten, da man iA. fix ausprogrammierte Formularbezüge in Public Fu's vermeiden sollte.

so long erwin...

PS: Was du da mit "Feber 2002" anstellst, welchen du an DateValue übergibst, ist nicht sehr empfehlenswert, da eine korrekte Erkennung bei der Typkonvertierung von externen (Systemsteuerung) Einstellungen abhängt, was oft zu Folge hat, dass ein Programm mal funktioniert - und dann auch wieder nicht !

[ 02. Mai 2002: Beitrag editiert von: erwin ]</p>

erwin
04.05.2002, 22:00
hooops

wat isse nu - hast du's jetzt geschafft - oder frustriert aufgegeben ;) ?

slg erwin...

horse79
06.05.2002, 07:39
Bin scho no dabei :cool:

Hab no a bisserl Performance Problemchen...

I poste nachher no meinen aktuellem Sachstand.

Bis später...

kama
06.05.2002, 09:10
@Erwin
habe mir den Beitrag in Ruhe angesehen.
Bin überrascht und positiv angetan.
Grüß mir Desiree. <img src="graemlins/biggrinlove.gif" border="0" alt="[biggrinlove]" />
Pflege die Freundschaft mit ihr, sie hat offensichtlich einen positven Einfluß auf dich.
<img src="graemlins/grins.gif" border="0" alt="[grins]" />

erwin
06.05.2002, 13:49
...Freundschaft mit Desiree...

na na na, wir wollen doch mal nicht soooo übertreiben <img src="graemlins/nene.gif" border="0" alt="[Nein Nein]" /> , sonst rieseln bei mir i.d. Antworten demnächst auch Herzchen im Hintergrund :D

so long erwin...

horse79
06.05.2002, 15:02
Hi,

so hier zu meinen aktuellen Problemchen:

(Es läuft ja alles so wie ich mir vorstell... aber...)

Die Datenbank bläht sich pro Klick um 16 KB auf. Im Beitrag weiter oben waren es noch über 40KB, das hat sich schon einiges verbessert. Merci Erwin. Aber ich versteh die 16KB nicht. Ich arbeite nicht mehr mit den TempTables sondern nur noch mit gespeicherten Queries. Aber schaut selber. Hier der Code der ausgeführt wird:
(Ich hab in den Code Kommentare geschrieben, wie sich die DB-Größe zur Laufzeit bei Änderun-gen auswirkt, wenn Du mir da die Hintergründe noch erläutern könntest??)

Private Sub Datum_AfterUpdate()

Dim inhalt As Date
Dim Ende1 As Date
Dim Anfang1 As Date
Dim Ende As String
Dim Anfang As String

inhalt = Me!Datum
Ende1 = DateAdd("m", 1, DateValue(inhalt)) - 1
Anfang1 = DateAdd("m", 0, DateValue(inhalt))

'Umwandlung des Datums ins Datumsformtat für SQL
Ende = "#" & Format(Ende1, "yyyy-mm-dd") & "#"
Anfang = "#" & Format(Anfang1, "yyyy-mm-dd") & "#"

SQL = "SELECT DISTINCTROW Einsatzplanung.*, Einsatzstellen.Name AS [Einsatzstellenname], [Azubidaten]![Name] & chr(32) & [Azubidaten]![Vorname] AS AzubiName, Einsatzpla-nung.DatumVon AS Von, Einsatzplanung.DatumBis AS Bis FROM (Einsatzplanung LEFT JOIN Einsatzstellen ON Einsatzplanung.PK_Einsatzstelle = Einsatzstellen.PK_Einsatzstelle) LEFT JOIN Azubidaten ON Einsatzplanung.PK_Azubi = Azubidaten.PK_Azubi WHERE (((Einsatzplanung.DatumVon) &gt= " & Anfang & ") And ((Einsatzplanung.DatumBis) &lt= " & Ende & ")) ORDER BY Einsatzstellen.Name;"
Forms!frmEinsatzplanung!subfrmEinsatzplanung.Form.RecordSource = SQL
'Würde dieser SQL als Query gespeichert werden, steigt die DB-Größe um über 40 KB statt "nur" 16 KB aktuell
'Lässt man DISTINCTROW aus dem String weg, steigT die DB Größe um 32 KB statt "nur" 16 KB aktuell

unverplanteTageEinerEinsatzstzelleEinesMonates 'gespeichert in Modul Funktionen
unverplanteTageEinesAzubisEinesMonates 'gespeichert in Modul Funktionen

Statusleiste.Panels(1).text = "Sie haben keine Auswahl getroffen..."
'Diese Zeile beeinflusst nicht die Datenbankgröße zur Laufzeit

PrimaryKey = ""
SQL = "SELECT DISTINCTROW [Einsatzplanung].[Datumvon] & ' bis ' & [Einsatzplanung].[Datumbis] & ' ' & [Einsatzstellen].[Name] AS Zeitraum FROM Einsatzstellen INNER JOIN Einsatzplanung ON Einsatzstellen.PK_Einsatzstelle = Einsatzpla-nung.PK_Einsatzstelle WHERE (((Einsatzplanung.PK_Azubi) =" & PrimaryKey & ")) ORDER BY Einsatzplanung.DatumVon;"
Me!Liste9.RowSource = SQL
'Würde hier statt zuweisung des SQL nur Me!liste9.rowsource="" steigt DBGröße um 38 KB statt "nur" um 16 KB aktuell
'Lässt man DISTINCTROW aus dem String weg, hat dies keine Auswirkung auf die DB-Größe im Gegensatz zum String oben

End Sub

Hier noch die Funktionen:

Public Function unverplanteTageEinerEinsatzstzelleEinesMonates()

DoCmd.SetWarnings False

Forms!frmEinsatzplanung!Liste4.RowSource = "AlleFreienStellen"

End Function

Public Function unverplanteTageEinesAzubisEinesMonates()

DoCmd.SetWarnings False

Forms!frmEinsatzplanung!Liste2.RowSource = "AlleFreienAzubis"

End Function

und hier noch die Queries:

AlleTage:

SELECT DateSerial(fktJahr(),[MonatX],[TagX]) AS DerTag
FROM HilfsMonate, HilfsTage
WHERE (((IsDate(fktJahr() & "-" & [MonatX] & "-" & [TagX]))&lt&gtFalse));

AlleTageUndAzubis:

SELECT Azubidaten.PK_Azubi, AlleTage.DerTag
FROM Azubidaten, AlleTage
WHERE (((AlleTage.DerTag) Between fktdatumvon() And fktdatumbis()) AND ((Azubidaten.AusbildungBeginn)&lt=fktdatumvon()) AND ((Azubidaten.AusbildungEnde)&gt=fktdatumBis()));

AlleTageUndStellen:

SELECT Einsatzstellen.PK_Einsatzstelle, AlleTage.DerTag
FROM Einsatzstellen, AlleTage
WHERE (((AlleTage.DerTag) Between fktdatumvon() And fktdatumbis()));

AlleFreienAzubis:

SELECT A.PK_Azubi, [Azubidaten]![Name] & " " & [Azubidaten]![Vorname] AS Inhalt, Count(*) AS AnzahlTageUngeplant
FROM AlleTageUndAzubis AS A INNER JOIN Azubidaten ON A.PK_Azubi = Azubida-ten.PK_Azubi
WHERE (((Exists (SELECT * FROM Einsatzplanung B
WHERE B.PK_Azubi=A.PK_Azubi
AND A.DerTag Between B.DatumVon And B.DatumBis))=False))
GROUP BY A.PK_Azubi, [Azubidaten]![Name] & " " & [Azubidaten]![Vorname];

AlleFreienStellen:

SELECT A.PK_Einsatzstelle, Einsatzstellen.Name, Count(*) AS AnzahlTageUngeplant
FROM AlleTageUndStellen AS A INNER JOIN Einsatzstellen ON A.PK_Einsatzstelle = Einsatz-stellen.PK_Einsatzstelle
WHERE (((Exists (SELECT * FROM Einsatzplanung B
WHERE B.PK_Einsatzstelle=A.PK_Einsatzstelle
AND A.DerTag Between B.DatumVon And B.DatumBis))=False))
GROUP BY A.PK_Einsatzstelle, Einsatzstellen.Name;

Ich hoffe mal jemand kann mir hier beo der Performance Steigerung helfen?

Vielen Dank

Sven

Anne Berg
07.05.2002, 11:41
Hallo Sven!

Ist ja echt spannend, womit du dich so beschäftigst... Aber was erwartest du eigentlich?! Wenn du mit dynamischen Abfragen arbeitest, wie sollte sich die Datenbank da nicht verändern!? Ist nun mal eine Access-typische Eigenart und da hilft nur regelmäßiges Komprimieren.

Gruss Anne

horse79
07.05.2002, 12:12
Mit dynamisch meinst Du dass selbergebastelte Funktionen in den Queries mit eingebunden sind?
Eigentlich werden aber die gespeicherten Tabellen nicht angerührt, da sollte doch nicht soviel Aufblähung passieren?

Vielleicht könnte ja mann die Funktionen an sich no effektiver programmieren...

Public Function Jahresplan(Monat As Variant, Einsatzstelle As Variant) As String

'Funktion gibt je Monat und Einsatzstelle alle eingesetzten Azubis sowie das Einsatzdatum in ei-nem String aus
'Diese Funktion wird für die Erstellung der Halbjahrespläne benötigt

On Error GoTo Fehler

Dim rst As Recordset
Dim mdb As DATABASE
Dim SQL As String
Dim Startdatum As Date
Dim Endedatum As Date
Dim Azubi As Variant
Dim Ergebnis As Variant

SQL = "SELECT DISTINCTROW Einsatzplanung.PK_Einsatzstelle, Azubidaten.Name, Azubida-ten.Vorname, Einsatzplanung.DatumVon, Einsatzplanung.DatumBis FROM Azubidaten INNER JOIN Einsatzplanung ON Azubidaten.PK_Azubi = Einsatzplanung.PK_Azubi ORDER BY Einsatz-planung.PK_Einsatzstelle, Einsatzplanung.DatumVon;"
'SQL ist sortiert nach PK_Einsatzstelle und DatumVon

Startdatum = DateAdd("m", 0, DateValue(Monat)) 'Berechnung des ersten eines Monates
Endedatum = DateAdd("m", 1, DateValue(Monat)) - 1 'Berechnung des letzteb eines Monates

Set mdb = CurrentDb()
Set rst = mdb.OpenRecordset(SQL)

Do While Not rst.EOF
If rst!DatumVon &gt= Startdatum Then
If rst!DatumVon &lt= Endedatum Then
Ergebnis = Einsatzstelle - rst!PK_Einsatzstelle
If Ergebnis = 0 Then
If Azubi = "" Then
Azubi = Left(rst!DatumVon, 5) & "-" & Left(rst!DatumBis, 5) & " " & rst!name & ", " & rst!vorname
Else
Azubi = Azubi & " " & Left(rst!DatumVon, 5) & "-" & Left(rst!DatumBis, 5) & " " & rst!name & ", " & rst!vorname
End If
End If
End If
End If
rst.MoveNext
Loop

rst.Close
mdb.Close

Ende:
Set rst = Nothing
Set db = Nothing
Jahresplan = Azubi 'Funktion gibt den Wert aus Varible Azubi aus
Exit Function

Fehler:
MsgBox "Es ist ein Fehler aufgetreten..." & Chr(13) & Chr(13) & "Beschreibung des Fehlers:" & Chr(13) & Chr(13) & Err.Description & Chr(13) & Chr(13) & "Der Fehler hat die Nummer: " & Err.Number, vbCritical, "Sicherheitssystem der Azubiverwaltung"
MsgBox "Dieser Fehler trat im Modul Jahresplan auf..." & Chr(13) & "Bitte verständigen Sie den Administrator.", vbInformation, "Sicherheitssystem der Azubiverwaltung"

GoTo Ende

End Function

Ich zweifel aber immer noch daran, dass die 16 KB daran liegen...

Aber mal schauen, vielleicht fällt euch dazu noch etwas ein... <img src="graemlins/top.gif" border="0" alt="[Finger hoch]" />

Sascha Trowitzsch
07.05.2002, 12:45
Das soll wohl der Thread des Jahres werden? ;)

Aufblähen: Also, die Zahlen, die du nennest sind doch nicht nenneswert? Was sind schon 16kb ?

Aber zur Erklärung:
Abfragen, ob per String generiert oder gespeichert, erzeugen schon Defragmentierung in der DB und die geht - wie Anne richtig bemerkt - mit Komprimieren wieder weg.
Umso komplexer die Abfrage (wie bei dir), umso mehr Zwischenschritte muss Access ausführen, um zum Ergebnis zu kommen. Wenn du - als Tüftler - genauer sehen willst, wie Access die Abfrage abarbeitet, so kannst du mal einen Queryplan ausgeben lassen.
( In Registry unter
HKEY_Local_Machine\Software\Microsoft\Jet\4.0 oder 3.5\Debug
den Schlüssel JETSHOWPLAN (String) mit "ON" eintragen)
Da wird dann eine Textdatei showplan.out erzeugt, die das Vorgehen von Jet anzeigt.
Wirst dich wundern, was alles beim Ausführen deiner Abfrage abgeht.

Außerdem werden evtl. temporäre Tabellen während der Ausführung angelegt, die man NICHT sieht, wohl aber defragmentieren.

Ciao, Sascha

horse79
07.05.2002, 13:36
Naja 16KB sind natürlich für sich nicht viel, jetzt stelle Dir mal ca. 10 User vor. Die 16KB entstehen bei jedem Klick auf ne Schaltfläche. Diese muß betätigt werden, wenn ein anderer Monat angezeigt werden soll. Also die Kerntätigkeit meiner Anwendung. Durchschnittlich klickt der User also bis zu 100mal pro Tag drauf. Ne einfache Rechnung am Tag. 10*100*16= 16.000 KB pro Tag...
Dazu noch Access95 bei dem man ja so tolle Autokomprimierungen starten kann :(

Ich dachte evtl. dass ich die oben geposteten Queries und Fragmente evtl. no a bisserl entschlanken kann, vielleicht sieht da ja noch jemand Ansätze, weil die VBA-Leuchte bin ich auch nicht so...

[ 07. Mai 2002: Beitrag editiert von: horse79 ]</p>

erwin
07.05.2002, 19:14
also in deiner Function Jahresplan würde ich auf jeden Fall die Datumsselektion NICHT per IF im Code machen, sondern als WHERE Teil in SQL !

Ausserdem liegt doch hoffentlich auch ein Index auf dem Datum i.d. Einsatz-Tab. ?!

Für meinen Geschmack JOIN'st du sowieso in den Abfrage ein bisschen zu viel und zu oft - da müsstest du dir doch selbst überlegen können, wie die sicherlich sowieso schon recht komplexe Abfragerei so gestaltet werden könnte, dass

a) einschränkende Selektion (per indizierter (!) Felder) möglichst "früh" i.d. geschachtelten Abfragen erfolgt.
b) Die Joins so erfolgen, dass nich innerhalb der
Schachtelung der Abfragen dieselben Daten mehrfach gelesen werden müssen.

bzgl. der MDB-Vergrösserung hat dir ja Sascha schon den Tipp gegeben, wie du nachschauen kannst, was JET da macht - bzw. ist es bei komplexen Queries normal, dass JET mit internen temp.Tab's arbeitet.

so long erwin...

horse79
08.05.2002, 07:38
OK, merci. Dann werd ich mal übers lange Wochenende :cool: damit rumbasteln. Ich meld mich mit meinen Ergebnissen am Montag wiededer.

Nice Weekend

Sven

Anne Berg
08.05.2002, 08:24
Hi Sven,

im übrigen arbeitest du keineswegs mit "gespeicherten Queries" (Zitat), wenn du im Code SQL-Strings zusammenbastelst und ausführst. Das ist durchaus ein Unterschied. Ist natürlich klar, dass sich das schwerlich vermeiden läßt.

Aber ehrlich, ich frage mich echt, was der ganze Aufwand soll... Willst du eine Doktorarbeit darüber schreiben? (Ich gebe zu, ich habe nicht den ganzen Thread gelesen.)

Schönes Wochenende und viel Erfolg.
Anne

horse79
08.05.2002, 11:46
Naja, was der ganze Aufwand soll...

Ich hab einfach keine Lust jeden Tag manuell zu komprimieren. Mit Access95 kann ich leider nichts automatisieren in dem Bereich, und möchte halt auch dass die Anwendung flott und so balastfrei wie möglich läuft.

Aber wie gesagt ich teste und bastel weiter und melde mich wieder.

erwin
08.05.2002, 15:33
Das Aufblähen geht ja nicht linear bei jedem User/Verwendung + 16kb.

Wenn Jet intern irgendeine temp.Tab. angelegt, und die "Verursachung" (Query) beendet wurde, dann ist die MDB eben um x kb grösser. Wobei das nächste Mal dieser nun nicht mehr benötigte Bereich wieder verwendet wird dh. irgendwann stellt sich eine "maximal aufgeblasene Grösse" der MDB ein (gleichbleibendes Grunddatenvolumen vorausgesetzt).

so long erwin...

horse79
13.05.2002, 08:01
Morgen,

bin jetzt noch nicht ganz zum austesten gekommen, aber
<blockquote><font size="1" face="Arial, Verdana, Helvetica, sans-serif">Zitat:</font><hr>
Ausserdem liegt doch hoffentlich auch ein Index auf dem Datum i.d. Einsatz-Tab. ?!
<hr></blockquote>

Diesen Index habe ich rausgenommen, da er das Aufblähungsvolumen verdoppelt, kann das eigentlich sein? Geschwindigkeitstechnisch spüre ich mit oder ohne Index keinen nennenswerten Unterschied...

erwin
14.05.2002, 13:48
dann kann deine Datenmenge noch nicht sehr "berauschend" sein - Indizes wirken sich erst nennenswert aus, wenn Acc./Jet die zu bearbeitenden Daten nicht mehr cachen kann.

Wobei ich dein "Kopfzerbrechen" bzgl. der MDB-Vergrösserung nicht ganz nachvollziehen kann.

so long erwin...

Sascha Trowitzsch
14.05.2002, 15:03
@erwin:
Na, ob man das mit dem Index & cachen so sagen kann?

Ein Index bringt IMHO erst dann Performance-Vorteile, wenn die DistinctCount-Eigenschaft wesentlich kleiner ist, als die RecordCount-Eigenschaft. Und sollte dem so sein, dann wird der entspr. DS über den Index auch gecacht schneller ermittelt, als mit dem gecachten Recordset allein.

@horse:
Ja sicher nimmt ein Index auch Speicher ein. Indexe sind nichts anderes als Tabellenfelder auch.

Ciao, Sascha

erwin
14.05.2002, 15:18
@sascha

das war sicher "sehr vereinfacht" ausgedrückt und spiegelt auch eher meine allg. Kenntnisse über rel.DB-Sys. wider - wobei ich mal davon ausgehe, dass MS bei Acc. den DB-Kernel auch nicht "komplett neu erfunden" hat.

Wenn die Grundmenge so "klein" ist, dass Sortierung/Selektion im Speicher ablaufen kann, dann bringt AFAIK ein Index kaum was, wenn aber immer wieder der Massenspeicher zum Weiterlesen bemüht werden muss, dann ist's schon erheblich besser, wenn per Index die Selektion/Sortierung erfolgen kann, als durch direktes Lesen/Auswerten der Grunddaten.

Reinhard hat diesbzgl. glaub' ich auch mal empirische Tests mit Jet vorgenommen, welche die o.a. Aussage bekräftigen.

Deine Aussage
...Ein Index bringt IMHO erst dann Performance-Vorteile, wenn die DistinctCount-Eigenschaft wesentlich kleiner ist, als die RecordCount-Eigenschaft... erscheint mir hingegen unlogisch: Wenn du mal den Extremfall unterstellst, zB.:
- Distinctcount = 1
- Recordcount = 1000000
dann bringt IMHO der Index für Positiv-Selektion gar nix ! Und so allg. hab' ich (zwar von Oracle her) noch im Kopf, dass Indizes auf kaum selektiven Kriterien eher wenig bringen.

so long erwin...

[ 14. Mai 2002: Beitrag editiert von: erwin ]</p>

Sascha Trowitzsch
14.05.2002, 19:15
@erwin:
Nach etlichen Tests mit Abfragen über Tabelle mit 100000 DS bleibe ich bei meiner Auffassung:
Die Abfragen (...mit WHERE auf des indizierte Feld + Sortierung) laufen dann am schnellsten, wenn das Verhältnis von RecordCount/DistinctCount etwa 100 ist, also die Wurzel. Aber auch mit noch mit 10000 läuft es fast so schnell. Bei einem Verhältnis von 10 hingegen brachte derIndex fast nichts mehr.

Die Abfragen liefen sicher auf die Cache-Daten, dazu sind die 100T DS zuwenig. Aber das ändert im Prinzip nichts.

Ich sehe das so (...wiewohl ich keine Ahnung habe, wie Jet seine Hash-Tables verwaltet...):
<Font Face=Courier>

Index1 - DS1
- DS233
- DS756
Index2 - DS4
- DS655
- DS1232

findet DS655 schneller als über:

Index1 - DS1
Index2 - DS4
Index3 - DS233
Index4 - DS655
Index5 - DS765
Index6 - DS1232

</Font>

Ciao, Sascha

[ 14. Mai 2002: Beitrag editiert von: Sascha Trowitzsch ]</p>

horse79
17.05.2002, 11:05
Ok, ich seh schon dass mit meinen Performance-Problemen muß ich no a bisserl zurückstellen.

Jetzt hab ich no a Problem mit der Kreuztablle.
(Code siehe irgendwo weiter oben im Thread)
Die zeigt mir ja alle eingesetzten Azubis von Januar bis Juni, bzw. ne zweite von Juli bis Dezember an. Da aber die Lehre immer im September startet wäre es angebracht mit September zu starten. Also September bis März und April bis August. Wie bastel ich das in den SQL? Da überschreite ich ja das Jahresende...

Merci

Sven

erwin
17.05.2002, 20:21
@sascha
interessant - werde das bei Gelegenheit auch mal testen...

@horse
das wird jetzt schwierig:
Einerseits brauchst du vermutlich die fixierten Spaltenüberschriften, was aber andererseits mit wechselnden Jahreszahlen nicht klappt.

Einziger Ausweg, der mir dazu auf die Schnelle einfällt, ist es das Jahr in der nach Monaten gruppierten Auswertung zu "relativieren" dh. ca. so:

iif(year(tab.datum)=year(now()),"lfd.Jahr " & format(tab.datum,"mm"),"nxt.Jahr " & format(tab.datum,"mm"))

mit fixierten Spaltenüberschriften:
"lfd.Jahr 01"
"lfd.Jahr 02"
...
"nxt.Jahr 01"

so long erwin...

horse79
21.05.2002, 07:46
Hi,

da steh ich noch auf dem Schlauch. :confused:

Wie und wo baue ich das ein? Hab ein bischen damit rumgespielt, aber leider ohne Erfolg...

Vielen Dank

Sven

erwin
21.05.2002, 12:05
das ist ja auch noch nix "fertiges" zum Einbauen, sondern eine grundlegende Idee, wie man es machen könnte !

Wenn ich in einer Kreuztab.Query (die ich auch für einen Report, oder sonstwo verwenden möchte) fixierte Spaltenüberschriften brauche (sonst ändern sich ja die Feldnamen !), und darin einen variablen Zeitraum, der zwar insgesamt auf 24 Monate begrenzt ist, aber das lfd. Jahr + Folgejahr (mal als Annahme) umfassen kann, dann muss ich die Spaltennamen ( = Dateninhalte) so wählen, dass sie "relativ" zum jetzigen Zeitpunkt sind, um fixe Begriffe zu erhalten.

Und das wäre prinzipiell in der o.a. Art incl. "korrekte" Sortierung ("L" &lt "N" !), möglich:
"Lfd.Jahr 01"
"Lfd.Jahr 02"
...
"Nxt.Jahr 01"
"Nxt.Jahr 02"
...etc.

so long erwin...

horse79
03.02.2003, 12:02
Hi,

bischen spät, aber ich arbeite immer noch an diesem Projekt :D

Das mit den variablen Spaltennamen habe ich leider nicht hinbekommen :(

Aber jetzt ergibt sich noch ein anderes Problem. Jetzt kommt noch die tolle Anforderung hinzu, dass die Einsatzstellen in einer bestimmten Reihenfolge in der Kreuztabelle erscheinen sollen. Wie kann ich den das in den Griff bekommen? Die Reihenfolge könnte ich in einer neuen Tabelle festlegen. Dies hätte auch den Vorteil, dass die User diese flexibel ändern könnten...

Vielen Dank

erwin
03.02.2003, 13:35
>Das mit den variablen Spaltennamen habe ich leider nicht hinbekommen

Hast du meinen Grundgedanken verstanden ? (dann sind die Spaltennamen ja gar nicht variabel !)


>Die Reihenfolge könnte ich in einer neuen Tabelle festlegen

wieso neue Tabelle ? reicht nicht ein Sortierfeld i.d. Stammtabelle ?

so long Erwin

horse79
03.02.2003, 13:55
Hi,

Und das wäre prinzipiell in der o.a. Art incl. "korrekte" Sortierung ("L" < "N" !), möglich:

Wie setzt mann dass den mit den Monatsnamen um? :confused: Aktuell habe ich ja 1-6 oder 7-12 als Überschrift, je nach Halbjahr...

RANSFORM First(SQLJahresplan.Inhalt) AS [Anzahl von m]
SELECT Einsatzstellen.Name
FROM Einsatzstellen INNER JOIN SQLJahresplan ON Einsatzstellen.PK_Einsatzstelle = SQLJahresplan.PK_Einsatzstelle
WHERE (((SQLJahresplan.DatumVon)>=#7/1/02#) AND ((SQLJahresplan.DatumBis)<=#12/31/02#))
GROUP BY Einsatzstellen.Name
PIVOT Format([SQLJahresplan].[DatumVon],"m") In (7,8,9,10,11,12);

wieso neue Tabelle ? reicht nicht ein Sortierfeld i.d. Stammtabelle ?

Da hast Du natürlich recht. Aber wie bekomme ich denn die Sortierreihenfolge in meinen SQL-String? Oder muß ich da zwingend eine Query dazwischenschalten?

erwin
04.02.2003, 11:10
wenn i.d. Tab Einsatzstellen noch ein Sortierfeld ist, dann kannst du das doch i.d. Query übernehmen, eben
...
GROUP BY Einsatzstellen.Sort, Einsatzstellen.Name

und zu dem anderen Punkt hast du meine Frage nicht beantwortet - bzw. ich habe ein paar Postings weiter oben (iif....) doch eh schon geschrieben, wie sowas prinzipiell klappen kann. Allerdings ist mir jetzt auch nicht klar, wieso du das brauchst, wenn du sowieso nur ein Halbjahr auswertest. Nötig ist das doch erst bei jahresübergreifender Auswertung.

so long Erwin...

horse79
04.02.2003, 11:32
iif(year(tab.datum)=year(now()),"lfd.Jahr " & format(tab.datum,"mm"),"nxt.Jahr " & format(tab.datum,"mm"))

Aktuell habe ich ja noch diese halbjährliche, ich wollte nur den aktuellen SQL-String posten...

Klar ist es mir noch nicht, wie das ganze dann funktionieren soll. Wie stelle ich das ganze denn in den String hinein? Ich muß ja irgendwie

PIVOT Format([SQLJahresplan].[DatumVon],"m") In (7,8,9,10,11,12);

ersetzen???

erwin
04.02.2003, 12:46
PIVOT iif(year(tab.datum)=year(now()),"lfd.Jahr " & format(tab.datum,"mm"),"nxt.Jahr " & format(tab.datum,"mm")) IN ("lfd.Jahr 01","lfd.Jahr 02",....."nxt.Jahr 12")

so long Erwin...

horse79
04.02.2003, 13:31
Thanx,

aber dann muss ich doch auch den String "SQL-Jahresplan" abändern???

SELECT DISTINCTROW Jahresplan([DatumVon],[PK_Einsatzstelle])
AS Inhalt, Einsatzplanung.PK_Einsatzstelle, Einsatzplanung.DatumVon, Einsatzplanung.DatumBis,
Einsatzplanung.PK_Azubi
FROM Einsatzplanung
ORDER BY Einsatzplanung.PK_Einsatzstelle, Einsatzplanung.DatumVon;

Wenn ich das ganze richtig verstanden habe :confused:

horse79
04.02.2003, 14:08
Noch kleiner Nachtrag:

Hier der aktuelle SQL-String (Der aber keine Daten liefert...)

TRANSFORM First(SQLJahresplan.Inhalt) AS [Anzahl von m]
SELECT Einsatzstellen.Name
FROM Einsatzstellen INNER JOIN SQLJahresplan ON Einsatzstellen.PK_Einsatzstelle = SQLJahresplan.PK_Einsatzstelle
WHERE (((SQLJahresplan.DatumVon)>=#1/1/02#) AND ((SQLJahresplan.DatumBis)<=#6/30/02#))
GROUP BY Einsatzstellen.Name
PIVOT IIf(Year(SQLJahresplan.DatumVon)=Year(Now()),"lfd.Jahr " & Format(SQLJahresplan.DatumVon,"mm"),"nxt.Jahr "
& Format(SQLJahresplan.DatumVon,"mm")) In ("lfd.Jahr 09","lfd.Jahr 10","lfd.Jahr 11","lfd.Jahr 12","nxt.Jahr01","nxt.Jahr02");

erwin
05.02.2003, 09:42
1. kenn ich ja deine Daten nicht, aber
2. es passt ja schon deine Selektion (1.1.2002 - 30.6.2002) <b>nicht</b> zum IN ("lfd.Jahr 09",....) Teil der Query !

Wie gesagt mein Tipp war ein <b>prinzipielles Beispiel</b> und muss natürlich - nachdem du das Prinzip verstanden hast - an deine GEgebenheiten angepasst werden, wobei es <b>prinzipiell</b> nicht klappt, falls der max. Zeitraum für die Auswertung beliebig lange werden kann.

so long Erwin...

horse79
05.02.2003, 10:05
zu 1.

dies hatte ich mittlerweile angepasst.
Es erscheint eine schöne Kreuztabelle links die Einsatzstellen und oben lfd.Jahr 09 - nxt.Jahr02, aber leider keine Inhalte obwohl für den Zeitraum definitiv Daten eingegeben worden sind... Meines Erachtens sollte es doch so funktionieren, oder habe ich noch etwas zu anpassen übersehen?

zu 2.
falls der max. Zeitraum für die Auswertung beliebig lange werden kann.
Die Auswertung ist immer halbjährlich. Also 09-02 und 03-08. Oder meintest Du hier etwas anderes.

Ich versuche zu verstehen wie die nötige Vorgehensweise hierfür ist, aber ist ja nicht so einfach...für mich jedenfalls.

erwin
05.02.2003, 12:56
wenn sie "immer jalbjährlich ist", dann musst du eben den für den Zeitausschnitt, in welchen das Auswertungsintervall fallen kann, die PIVOT's so "relativieren", dass sich fixe Spaltennamen ergeben. Das ist bei dem von mir geschilderten Ansatz wiederum ein Problem, falls der 6-Monats-Zeitausschnitt beliebig auf der Zeitachse angesiedelt sein kann:
<pre><big>
1.2000.......1.2001......2.2002......1.2003
######
######
#####</big></pre>
dann braucht man eine "andere Art" der Relativierung, da ja dann "lfd.Jahr" bzw. "nxt.Jahr", so wie ich das beispielhaft ermittle, Unsinn ergäbe.

so long Erwin...

horse79
05.02.2003, 13:23
es soll "nur" 2 Möglichkeiten geben:

März laufendes Jahr - August laufendes Jahr

September laufendes Jahr - Februar nächstes Jahr

Wenn mann da zwei feste Queries machen könnte, wäre es super. Weiß aber nicht wie gut das Möglich ist?? Wenn dies funktionieren würde, wäre ich mehr als zufrieden...

Hab mal meine Datei angehängt ;)

Nouba
09.02.2003, 04:29
Hallo Sven,

ich habe mal ein Beispiel basierend auf Deinen Daten angehangen, was eine Auswahl verschiedener Halbjahre im Formular ermöglicht.

horse79
11.02.2003, 07:56
Hi Nouba,

habe Dein Beispiel erst heute früh testen können, da ich in der Arbeit nur Office95 hatte und mir erst einen PC mit XP organisieren musste. Sieht mal wahnsinnig klasse aus. Ich hoffe ich bekomme dies in mein 95er Access hinein. Du hast ja auch etwas an meiner Tabellenorganisation geändert :top: . War wohl nicht so optimal mein Design, bin halt noch am lernen... Denkst Du ich könnte meine alten Formulare problemlos aufs neue Design übernehmen?

Thanx a lot

horse79
11.02.2003, 20:08
Hi,

habe mich jetzt intensiver mit Deinem Beispiel auseinandergesetzt und bin auf folgendes Problem gestossen:

Wenn ein Azubi in einem Monat 2 oder mehr Einsatzstellen hat, wird er nur in einer Einsatzstelle angezeigt. In meiner Ursprünglichen Datenbank zeigt mir Access dann z.B. 01.10.-15.10.2002 Azubi1 & 16.10.2002-30.10.2002 Azubi 2 an...

Dann habe ich noch ein weiteres Problem in meiner ursprünglichen DB. Ich habe in der Tabelle "Einsatzstellen" ein neues Feld Sortierung eingefügt. Dies soll eine individuelle Sortierung nach Einsatzstelle ermöglichen.
Anschliesen habe ich meiner Query "Halbjahr2" die Sortierung nach dem Feld "Sortierung" hinzugefügt. Das klappt. Anschlisend habe ich den Code beim Öffnen des Berichtes "Halbjahr2" geändert. Im SQL-String habe ich zwischen dem GROUP BY und PIVOT Teil ORDER BY Einsatzstellen.Sortierung eingebaut. Aber der Bericht sortiert nicht wie in der Abfrage... Woran kann das denn liegen.

Dies möchte ich erst fertigstellen und wenn das klappt versuche ich die ganze Anwendung nach Noubas Vorschlag umzudesignen...

Vielen Dank für Eure Hilfe

Nouba
11.02.2003, 20:39
Hallo Sven,

ich habe mir Deine Formulare nur sehr oberflächlich angesehen. In meinem Minimalbeispiel (mehr soll es nicht darstellen) werden Azubis, die in einem Monat an mehreren Stellen ausgebildet werden, angezeigt. Ich habe der Darstellung wegen die Anzeige gekürzt (Datum rausgenommen), da aus dem Beispiel nicht hervorging, dass auch innerhalb eines Monats Stellengewechselt werden.

Du kannst mehrere Row Headings in einer Kreuztabelle haben. Vorschlag: Lasse das Feld in der Kreuztabelle anzeigen, füge es aber nicht in den Bericht ein. Über das Kontextmenü (Rechtsklick im Berichtsentwurf - Sorting and Grouping) des Berichts sollte trotzdem eine Sortierung über dieses Feld möglich sein.

horse79
11.02.2003, 20:47
Super, ja das war es. Habe nicht gewusst, dass ich in einer Kreuztabelle mehr als ein Feld als Zeilenbeschriftung anzeigen lassen kann. Habe einfach SELECT Einsatzstellen.Name, Einsatzstellen.Sortierung genommen und dann konnt ich im Bericht auch danach sortieren. Vorher hatte ich da ja nur Name, 7,8,9,10,11,12 zur Auswahl...

Vielen Dank, die Erläuterung zu Deinem Bsp. lasse ich mir morgen früh durch den Kopf gehen.