Abfragenorganisation

[ebs17]

Abfrage 2, unter Weglassung des Kriteriums und Änderung des konstanten Textes im SELECT-Teil wird auch Abfrage 1 erledigt:

Code [Auswählen] Erweitern

SELECT
   "unter 25" AS Kriterium,
   COUNT(*) AS g,
   SUM(M.geschlecht = 1) * - 1 AS m,
   SUM(M.geschlecht = 2) * - 1 AS w
FROM
   tblMitarbeiter AS M
WHERE
   M.Geburtsdatum < DateAdd("yyyy", - 25, Date())

Abfrage 4, mit Reduzierung Abfrage 3:

Code [Auswählen] Erweitern

SELECT
   "Quali 3, nicht 5" AS Kriterium,
   COUNT(*) AS g,
   SUM(M.geschlecht = 1) * - 1 AS m,
   SUM(M.geschlecht = 2) * - 1 AS w
FROM
   tblMitarbeiter AS M
WHERE
   EXISTS
      (
         SELECT
            NULL
         FROM
            tblQualiMitarbeiter AS QM
         WHERE
            QM.MitarbeiterID_F = M.MitarbeiterID
               AND
            QM.QualifikationsID_F = 3
      )
   AND
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            tblQualiMitarbeiter AS QM
         WHERE
            QM.MitarbeiterID_F = M.MitarbeiterID
               AND
            QM.QualifikationsID_F = 5
      )

Erkennbar: Die Abfrage auf eine Tabelle (Mitarbeiter) ist immer die gleiche. Variiert wird nur die erste Spalte (Bezeichnung der Filterung) sowie dann das Kriterium. Somit ließen sich die Abfragen in einer VBA-Schleife aus konstantem Anteil und variablen Anteil zusammensetzen, vorbehaltlich einer geeigneten Speicherung der variablen Teile.

Dass man hier zwei Abfragen beispielhaft für weitere zeigen kann, dokumentiert dann Variabilität, Erweiterbarkeit und Pflegbarkeit des Ganzen.

Wie das alles dann genau nach Excel zu übergeben wäre, hängt auch davon ab, wie die Darstellung genau aussehen soll. Damit dann auch, ob man die Abfragen per UNION zusammenhängt oder gleich einzeln per CopyFromRecordset überträgt, ist dann auch nur ein Nebenkriegsschauplatz.

[crystal]

Wow,

so schnell hat Eberhard eine Antwort gefunden! Bravo.

Naja - es fehlt noch etwas die Übertragung nach Excel bzw. zuvor die Auswertung der Ergebnisse in VBA und zuvor der Aufruf der Abfragen in einer Routine. Es ist eben nicht damit getan, Abfragen für die verschiedenen Fragestellungen isoliert zu formulieren, das kann (fast) jeder. Die Ergebnisse dieser Abfragen sollen ja auch zusammen dargestellt werden. Oder soll Xoar das anschließend manuell machen?

Morgen mehr.

lg
crystal

[ebs17]

Oder soll Xoar das anschließend manuell machen?

So wie er sich bisher dargestellt hat, kann er die Informationen selbst in einem zusammenfassenden Code verarbeiten. Immerhin fragte er eingangs gezielt nach einer Strategie für die Abfragen. Zum Übertrag nach Excel scheint er Vorstellungen bzw. Lösungen zu haben.
Und da er sein Projekt besser kennt und hier nur einen Auszug gezeigt hat, kann er das an der Stelle besser wie jeder andere.

Wer hilft, wo fördern reicht, schädigt.

Wie lautet doch gleich der Thementitel:
Abfragenorganisation

Abfragen für die verschiedenen Fragestellungen isoliert zu formulieren, das kann (fast) jeder.

Ein starkes Wort.
An dieser Feststellung und der tatsächlichen Umsetzung darf der Zitierte sich messen lassen.

[Xoar]

Ui danke,
da sieht man mal wirklich das ich von SQL sehr wenig Ahnung habe. Sowas hätte ich nie geschafft.

Nach Excel exportieren würd ich das ganze über nen Recordset mit ner Schleife. Das bekomm ich hin, hab ich anderweitig schon gemacht.

Bin hier heute leider total eingebunden, werde das ganze morgen Abend ausführlich testen und versuchen den genauen Syntax zu verstehen.

Besten Dank schonmal

[ebs17]

Nach Excel exportieren würd ich das ganze über nen Recordset mit ner Schleife.

Das könnte bspw. so aussehen:

Code [Auswählen] Erweitern

Sub Statistikausgabe(objExcelsheet As Object, Startzeile As Long)
    Dim db As DAO.Database
    Dim rsAusgabe As DAO.Recordset
    Dim rsFilter As DAO.Recordset
    Dim sSQL As String
    Dim i As Long

    Set db = CurrentDb
    Set rsFilter = db.OpenRecordset( _
                   "SELECT Filtername, Filterstring FROM tblFilterspeicher", _
                   dbOpenForwardOnly)
    With rsFilter
        Do While Not .EOF
            sSQL = "SELECT '" & .Fields("Filtername") & "' AS Kriterium," & _
                   " COUNT(*) AS g," & _
                   " SUM(M.geschlecht = 1) * - 1 AS m" & _
                   " SUM(M.geschlecht = 2) * - 1 AS w" & _
                   " FROM tblMitarbeiter AS M" & _
                   " WHERE " & .Fields("Filterstring")
            Set rsAusgabe = db.OpenRecordset(sSQL, dbOpenSnapshot)
            objExcelsheet.Cells(Startzeile + i, 1).CopyFromRecordset rsAusgabe
            rsAusgabe.Close
            i = i + 1
            .MoveNext
        Loop
        .Close
    End With
    Set rsFilter = Nothing
    Set rsAusgabe = Nothing
    Set db = Nothing
End Sub

Hier wird eine Referenz auf ein Excel-Arbeitsblatt verwendet. Somit kannst Du im Vorfeld selber entscheiden, ob Du eine neue oder eine vorhandene Mappe verwenden willst und in welches Blatt der Übertrag erfolgen soll.

Es wird weiterhin eine Tabelle tblFilterspeicher verwendet, in der die Namen und Inhalte der verschiedenen Filter abgespeichert sind. Tabelle daher, weil sich hier Inhalte besser pflegen lassen als in einem Code, mit einer zusätzlichen Bedienoberfläche durchaus auch für einen normalen User.
Über ein zusätzliches Feld in der Tabelle könnte man darüber hinaus Auswahlkriterien anbieten, wenn z.B. in manchen Durchläufen nur bestimmte ausgewählte Auswertungen berücksichtigt werden sollen.
Hinweis: Für den allerersten Filter (alle Mitarbeiter) der eigentlich leer ist, True als Feldinhalt verwenden.

[crystal]

Hallo
und nebenbei bemerkt,

wirklich schönen Code hat Eberhard hier beigesteuert! Das ist doch VIEL besser, als allgemeine Floskeln, die nicht immer zu verstehen sind.

Bravo und bitte weiter so.

lg
crystal

[ebs17]

@crystal:
Ich bin im Gegenzug gespannt auf den Alternativ-Wettbewerbsbeitrag, besonders auf Aspekte wie
- Codeverständlichkeit
- Pfleg- und Erweiterbarkeit (wer 50 Auswertungen braucht, braucht auch 75)
- Performance, wenn es mengenmäßig um die Mitarbeiter von VW und nicht nur die der benachbarten Schmiede geht

... und ganz besonders, wie man die vielen und zum Teil gegensätzlichen Bedingungen in einer Abfrage unterbringt. Da würde ich dann wirklich sehr gerne lernen.

allgemeine Floskeln, die nicht immer zu verstehen sind

Nun, das Unverständnis gilt wohl eher individuell. Bei dem TE konnte ich das nicht feststellen.
Der Code ist nichts anderes, als was ich im Vorfeld skizzierte, mit der Abweichung, dass ich statt Kreuztabellenabfrage eine einfache Auswahlabfrage verwende. Da Codes konkret sind, sind sie erst sinnvoll, wenn die Ausgangssituation beim TE klar ist. Darauf habe ich aber regelmäßig verwiesen.

[Xoar]

Hallo

heißt, die Tabelle bekommt folgende Felder:
FilterID: Autowert
Filtername: Wie ich den Filter benannt haben will
Filterstring: M.Geburtsdatum < DateAdd("yyyy", - 25, Date())        muss der dann so eingetragen werden?

und dann halt für jeden Filter.
Hier mal mein Verständnis der Syntax.
sSQL = "SELECT '" & .Fields("Filtername") & "' AS Kriterium," & _ Feld: Filtername als "Kriterium" bezeichnet anzeigen lassen
                   " COUNT(*) AS g," & _ Zähle alle DS
                   " SUM(M.geschlecht = 1) * - 1 AS m" & _ summiere wo Geschlecht = 1 ist, wieso -1?
                   " SUM(M.geschlecht = 2) * - 1 AS w" & _ summiere wo Geschlecht = 2 ist
                   " FROM tblMitarbeiter AS M" & _ von tblMitarbeiter, verkürzt als M bezeichnet
                   " WHERE " & .Fields("Filterstring") Bedingung, je nach Schleifendurchlauf der Inhalt des Filterstrings aus der Tabelle (im Rst geladen)

Sehr gute Variante, hab ich viel durch gelernt. Danke *Daumen hoch*

PS: was genau macht das "true" als Filterstring? Sagt er der where Bedingung, sozusagen das keine Bedingung vorherrscht?

[ebs17]

@Xoar:

Filterstring: M.Geburtsdatum < DateAdd("yyyy", - 25, Date())        muss der dann so eingetragen werden?

Genau. Alles, was hinter dem Schlüsselwort WHERE der Hauptabfrage kommt.
Ich habe die Struktur der Abfrage bewusst so gewählt, weil man in der gleichen Weise auch andere Tabellen einbinden könnte, also statt Qualifikationen auch Urlaubstage, Dienstreisen oder andere Sachverhalte einbeziehen wollte.

was genau macht das "true" als Filterstring?

Im WHERE-Teil wird ja geprüft, ob eine Bedingung erfüllt wird. Diese Prüfung ergibt dann True oder False. Bei mehreren Bedingungen müssten dann alle True sein.
Die Schreibweise "WHERE True" erspart ein Abschneiden des WHERE und ermöglicht eine einfache Schleife wie gezeigt.

Code [Auswählen] Erweitern

SUM(M.geschlecht = 1) * - 1 AS m
M.geschlecht = 1 ... ist eine logische Prüfung und ergibt True (-1) oder False (0). Die Summierung ergibt die benötigte Anzahl, die Multiplikation mit -1 entfernt das Vorzeichen (ersatzweise könnte man auch Abs() ) verwenden.

Code [Auswählen] Erweitern

FROM tblMitarbeiter AS M
Das M ist ein Tabellenalias, siehe Grundlagen - SQL ist leicht (2) - Alias

//Edit:

Filterstring: M.Geburtsdatum < DateAdd("yyyy", - 25, Date())

Wer sich das genau angeschaut hat, wird bemerkt haben, dass der Operator falsch ist, wenn man die unter 25-jährigen haben will: '<' => '>'

[crystal]

Hallo,
ich bin noch dabei, zu programmieren, dauert bei mir halt etwas...

@Eberhard: wirklich schön zu sehen, wie gut du auf Xoars Fragen eingehst und erklärst! Du bist damit deutlich in meiner Achtung gestiegen. Die Art und Weise, wie du jetzt reagierst und antwortest, ist prima und ich freue mich, wenn es so bleibt. Ich sagte ja schon öfter, dass ich dein Expertenwissen sehr schätze, jetzt hast du wohl auch einen Weg gefunden, es mitzuteilen und zu vermitteln. Kleinere Seitenhiebe sind dann verschmerzlich...

lg
crystal

[crystal]

So,
nun bin ich mit einer ersten Version fertig.

Bitte folgendes zu beachten:
1. In Modul1 habe ich Arrays definiert; wenn die Zahl der Ausgabespalten oder -zeilen größer werden sollte, müssen die Konstanten in Modul1 angepasst werden. (Hintergrund: mit Redim kann man nur die letzte Dimension eines Arrays vergrößern, nicht aber die erste bei mehrdimensionalen Arrays).
2. Eine Excel-Ausgabe habe ich nicht implementiert. Ich schreibe aber ins Debug-Fenster eine "CSV": Text im Debug-Fenster komplett selektieren, ausschneiden, in eine Text-Datei einfügen und diese dann in ein Excel-Arbeitsblatt importieren (Trennzeichen Semikolon).

In der DB einfach nur das Formular1 aufrufen und den Button klicken. der Debugger bleibt dann bei der Dummy-Zeile i=0 stehen und man kann den Text im Direktfenster kopieren (s.o.).

Ob meine Lösung nun gut oder schlecht ist, sei dahingestellt. Ich fürchte mich auch nicht vor Kritik. Immerhin denke ich, dass sie durchaus performant ist, auch bei größeren Datenbeständen...

Ich freue mich auf eure Lacher.

lg
crystal

[crystal]

Hallo,
noch keine Reaktion?
Ist meine Lösung sooo schlecht, dass niemand eine Antwort schreibt?

Verbesserungs-Möglichkeiten:
1. Man könnte die möglichen Werte z.B. aus der Quali-Tabelle laden und mit Anzahl=0 im Array initialisieren. -Das könnte die Übersichtlichkeit verbessern, wenn man dies z.B. für die Spalten "alle", "m", "w" und "n.a." parallel macht, so dass in all diesen Spalten jeweils alle Qualis gelistet sind und eben nur da eine Anzahl > 0 steht, wo sie auch gezählt wurde (zusätzlicher Modus 2).
1a. Den Modus sollte man als letzte Übergabe und mit optional=1 definieren.
2. Der Code ist straight forward entspr. der gegebenen Anforderung ("Spaghetti-Code", wie Lachtaube es nannte). Die zugrundeliegende Abfrage liefert deshalb auch nur die Qualis. Wenn andere Felder hinzukommen (z.B. "besuchte Seminare"), würden Datenzeilen entspr. hinzukommen und eine Quali eines MA würde mehrfach auftauchen, was beim Zählen entspr. berücksichtigt werden muss (hier: überspringen aller Qualis eines Ma, die schon gezählt wurden).
3. Arrays: die Dimensionierung der Arrays dynamisch zu gestalten (mit Redim), ist nicht erforderlich, wenn man sich überlegt, wie viele z.B. Qualis (etc.) es überhaupt gibt (s. a. Punkte 1 und 2). Die Zahl der Spalten ergibt sich ohnehin aus der Programmierung.
4. Eine direkte Übergabe der Ergebnisse an Excel ist zweifelsfrei möglich, es mangelt mir nur an Erfahrung damit.

Es wäre schon schön, eine Rückmeldung zu meiner ersten Version zu erhalten, selbst wenn sie sehr negativ ausfällt.

lg
crystal

[Xoar]

Hi,
ich habe leider noch keine Zeit gehabt drüber zu schauen.
Hab momentan sehr viel um die Ohren.
Werde noch ein Feedback geben!

[crystal]

Hallo,

Ich habe meine Lösung noch mal geringfügig geändert (siehe vorvorige Nachricht Punkte 1 bis 3).
Im angefügten Zip habe ich eine Excel-Datei mit der Ausgabe beigefügt.

lg
crystal