Januar 20, 2021, 14:50:46

Neuigkeiten:

Ist euer Problem gelöst, dann bitte den Knopf "Thema gelöst" drücken!


Fixierte Spaltenüberschriften in einer Kreuztabelle

Begonnen von mad, Januar 03, 2021, 09:05:15

⏪ vorheriges - nächstes ⏩

mad

Hallo Zusammen,
habe den Vorschlag von ebs17 (https://www.access-o-mania.de/forum/index.php?topic=25301.0) jetzt wie folgt angewendet.

SELECT Sum(Abs([Geschlecht]=1)) AS Anzahl27M, Sum(Abs([Geschlecht]=2)) AS Anzahl27W, Sum(Abs([Geschlecht]=3)) AS Anzahl27D
FROM Personal
WHERE (((Personal.statusID_P)=1 Or (Personal.statusID_P)=2))
HAVING (((IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))>17 And (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))<27));

Möchte das ganze jetzt aber noch erweitern.
Nun würde ich das ganze gerne für das jeweilige Alter (12 - 19) runtergebrochen haben.

Habe es leider mit dem Vorschlag von ebs17 nicht hinbekommen. Weis auch nicht ob es geht, darum habe ich es mit  einer Kreuztabelle versucht.

Hier mein erster Ansatz für die Kreuztabellenabfrage. Wenn aber noch keine Daten mit dem Geschlecht=3 ,,Divers" vorhanden sind, wird diese Spalte auch nicht angezeigt.

TRANSFORM Count([Alter]) AS Anzahl
SELECT IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])) AS [Alter]
FROM Personal
WHERE (((Personal.Geschlecht)=1 Or (Personal.Geschlecht)=2 Or (Personal.Geschlecht)=3) AND ((IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=12 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=13 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=14 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=15 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=16 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=17 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=18 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=19) AND ((Personal.statusID_P)=1 Or (Personal.statusID_P)=2) AND ((Personal.gruppe) Like "JF*"))
GROUP BY IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])), Personal.gruppe
PIVOT Personal.Geschlecht;
Siehe Bild: ohne.png
Siehe Bild: wennVorhanden.png

Bin dazu gerade dran das Thema ,,FixierteSpaltenüberschriften" an der Kreuztabellenabfrage auszuprobieren, dazu habe ich folgenden Beitrag gefunden:
https://www.ardiman.de/datenbanken/grundlagen/abfragen/fixierte_spaltenueberschriften.html


Nun habe ich versucht mit Hilfe des Beitrags eben auch die Spalte ,,Divers" immer darstellen zu lassen, auch wenn noch keine Daten vorhanden sind.

TRANSFORM Count([Alter]) AS Anzahl
SELECT IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])) AS [Alter]
FROM Personal
WHERE (((IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=12 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=13 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=14 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=15 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=16 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=17 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=18 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=19) AND ((Personal.statusID_P)=1 Or (Personal.statusID_P)=2) AND ((Personal.gruppe) Like "JF*"))
GROUP BY IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])), "Geschlecht" & Format([Personal].[Geschlecht],"\g")
PIVOT "Geschlecht" & Format([Personal].[Geschlecht],"\g") In ("Geschlecht m","Geschlecht w","Geschlecht d");

Nach meinem Verständnis sind für das Anzeigen der drei Spalten (Geschlecht m, Geschlecht w, Geschlecht d) diese letzten Zeile zuständig:

....
GROUP BY IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])), "Geschlecht" & Format([Personal].[Geschlecht],"\g")
PIVOT "Geschlecht" & Format([Personal].[Geschlecht],"\g") In ("Geschlecht m","Geschlecht w","Geschlecht d");

Aber die Wertefelder bleiben jetzt leider alle leer.
Bild: werteLeer.png
Hätte jemand eine Idee?


Gruss
Mad

ebs17

ZitatWenn aber noch keine Daten mit dem Geschlecht=3 ,,Divers" vorhanden sind, wird diese Spalte auch nicht angezeigt.
Wenn man "alles" anzeigen will, muss man eine entsprechende vollständige Menge vorhalten und daran seine realen Daten anknüpfen, etwa ...
... FROM tblGeschlecht LEFT JOIN tblPersonal ...Man kann da nebenbei erkennen: Abfragedesign geht einher mit einer vorhandenen und bekannten Datenmodellierung (für Dritte Beziehungsbild anzeigen).

ZitatFormat([Personal].[Geschlecht],"\g")
Was bewirkt dieses Format real?
In einer Kreuztabellenabfrage werden Feldinhalte zu Spaltenüberschriften. Damit man aber bestimmte Spaltenüberschriften daraus bekommt oder aber bestimmte Feldinhalte fixierten Spaltenüberschriften zuordnen kann, muss man zuvor entsprechende verarbeitbare Feldinhalte in der Abfrage erzeugen.

Daneben, das hatte ich eingangs nicht in eine Diskussion einbezogen:
IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum]))Was soll der Ausdruck bewirken?
Ein IIF ohne Sonst-Klausel kann sehr rasch kritisch werden.
Mit freundlichem Glück Auf!

Eberhard

mad

Danke erstmal,

möchte mich jetzt Schritt für Schritt rantasten.

Habe das Thema "Left Join" umgesetzt. Bild "tab_Beziehung.png" zeigt die Beziehung beider Tabellen, dort habe ich nichts geändert. Bild "Abfrage Left Join.png" zeigt die Beziehung in der Abfrage beider Tabellen. Und Bild "Anzeige.png" zeigt das Ergebnis der Abfrage. Bild "Anzeige Formular.png" zeigt die anzeige im Formular.

Neuer Code:
TRANSFORM Count([Alter]) AS Anzahl
SELECT IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])) AS [Alter]
FROM tblgeschlecht LEFT JOIN Personal ON tblgeschlecht.ID = Personal.Geschlecht
WHERE (((IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=12 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=13 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=14 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=15 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=16 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=17 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=18 Or (IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])))=19) AND ((Personal.statusID_P)=1 Or (Personal.statusID_P)=2) AND ((Personal.gruppe) Like "JF*"))
GROUP BY IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum])), Personal.gruppe
PIVOT tblgeschlecht.Geschlecht;

Woran könnte es liegen das immer noch ohne der dritten Spalte "Divers" angezeigt wird?
Bild "tblgeschlecht.png" zeigt das ein DS "3" vorhanden ist.


Das Thema mit dem Format,
ZitatFormat([Personal].[Geschlecht],"\g")
habe ich erstmal weggelassen.

Das Thema,
ZitatEin IIF ohne Sonst-Klausel kann sehr rasch kritisch werden.
möchte ich im zweiten Schritt lösen.


Gruss
mad


ebs17

Januar 04, 2021, 11:14:08 #3 Letzte Bearbeitung: Januar 04, 2021, 12:44:07 von ebs17
ZitatSchritt für Schritt rantasten
Das klingt gut, wäre auch meine Handlungsempfehlung.  Über die Reihenfolge der Maßnahmen könnte man aber diskutieren.

IIf((Year(Now())-Year([GebDatum])),Year(Now())-Year([GebDatum]))Die Prüfung beseitigt nur ein Alter 0, aus meiner Abstraktion heraus könnte man das wie folgt eindampfen (das Alter wird eh nachfolgend gefiltert):
Year(Now())-Year(GebDatum)... was gleichlautend der Differenz aus aktuellem und Geburtsjahr entspricht (eine Altersberechnung könnte alternativ auch bezogen auf das aktuelle Datum bezogen sein).

ZitatWoran könnte es liegen das immer noch ohne der dritten Spalte "Divers" angezeigt wird?
Siehe Grundlagen - SQL ist leicht (11) - Reihenfolge der Abfrageabarbeitung
Durch den LEFT JOIN, der vermutlich zuerst ausgeführt wird, wird das dritte Geschlecht hinzuverknüpft, durch die nachfolgende Filterung aber wieder weggefiltert. Das kannst Du für sich mal testen, indem Du den WHERE-Teil komplett weglässt.

Schrittweises Herangehen - siehe Dir folgendes an:
1) Zuerst filtern
SELECT
   Geschlecht,
   Year(Now()) - Year(GebDatum) AS [Alter]
FROM
   Personal
WHERE
   Year(Now()) - Year(GebDatum) BETWEEN 12 AND 19
      AND
   statusID_P IN(1, 2)
      AND
   gruppe Like "JF*"
2) Danach Geschlechtertabelle hinzuverknüpfen:
SELECT
   G.ID,
   P.[Alter]
FROM
   tblgeschlecht AS G
      LEFT JOIN
         (
            SELECT
               Geschlecht,
               Year(Now()) - Year(GebDatum) AS [Alter]
            FROM
               Personal
            WHERE
               Year(Now()) - Year(GebDatum) BETWEEN 12 AND 19
                  AND
               statusID_P IN(1, 2)
                  AND
               gruppe Like "JF*"
         ) AS P
         ON G.ID = P.Geschlecht
3) Aus der zweiten Abfrage heraus könntest Du die Kreuztabellenabfrage erstellen.
Mit freundlichem Glück Auf!

Eberhard

mad

Hallo ebs17,

habe nun mal die beiden Codes von Dir in jeweils eine Abfrage (ebs17_abf1 und ebs17_abf2) kopiert.
Habe anschliessend die Abfrage "ebs17_abf2" als Datenherunft für die Kreuztabelle "3Neu_qyrJahresbericht_JF_AnzahlnachGeschlechtundAlter" verwendet.

Aber irgenwie kommt immer das selbe Ergebnis raus, siehe Vergleich der beiden Kreuztabellenabfragen:
2Neu....
3Neu....
Nie wird die dritte Spalte "Divers" mit angezeigt.

Habe mir mal erlaubt eine abgespeckte "test.accdb" anzufügen.


Gruss
mad


ebs17

Januar 04, 2021, 19:19:31 #5 Letzte Bearbeitung: Januar 05, 2021, 09:45:23 von ebs17
ZitatAbfrage ... ebs17_abf2 kopiert
Wahrheitshalber: In der DB steht aber etwas anderes als mein Vorschlag. Wenn Du diesen benutzt hättest, hätte man in der aufbauenden Kreuztabellenabfrage zumindest die dritte gewünschte Spalte, wenn auch noch mit einem unbefriedigenden Ergebnis, schon weil die Ergebnismenge entsprechend Filterung von 14 bis 18 geht statt 12 bis 19 wie lt. gewünschter Filterung, und D ist zusätzlich dem Alter NULL zugeordnet, da es keinen Eintrag dafür gibt.

Schlussfolgerung: Die vollständige Menge, an die die vorhandenen Daten angeknüpft werden sollten, muss vollständiger erstellt werden.

1) Zusätzliche Tabelle T99 mit einem einzelnen Feld I (= PK) erstellen, das die Zahlen von 0 bis 99 fortlaufend enthält. Daraus kann man die vollständige Altersliste ableiten. Die genannte vollständige Menge ergibt sich dann aus ...
SELECT
   T.I,
   G.ID,
   G.Geschlecht
FROM
   T99 AS T,
   tblgeschlecht AS G
WHERE
   T.I BETWEEN 12 AND 19

2) Daran kann man die gefilterte Personaltabelle anknüpfen:
SELECT
   V.I AS [Alter],
   V.Geschlecht,
   P.PID
FROM
   (
      SELECT
         T.I,
         tblgeschlecht.ID,
         tblgeschlecht.Geschlecht
      FROM
         T99 AS T,
         tblgeschlecht
      WHERE
         T.I BETWEEN 12 AND 19
   ) AS V
      LEFT JOIN
         (
            SELECT
               PID,
               Geschlecht,
               Year(Now()) - Year(GebDatum) AS [Alter]
            FROM
               Personal
            WHERE
               statusID_P IN(1, 2)
                  AND
               gruppe Like "JF*"
         ) AS P
         ON V.I = P.[Alter]
            AND
         V.ID = P.Geschlecht

3) Daraus kann man nun die Kreuztabellenabfrage ableiten, die nun tatsächlich 8 Zeilen und 3 Pivotspalten enthält:
TRANSFORM
   COUNT(B.PID) AS XY
SELECT
   B.[Alter]
FROM
   (
      SELECT
         V.I AS [Alter],
         V.Geschlecht,
         P.PID
      FROM
         (
            SELECT
               T.I,
               tblgeschlecht.ID,
               tblgeschlecht.Geschlecht
            FROM
               T99 AS T,
               tblgeschlecht
            WHERE
               T.I BETWEEN 12 AND 19
         ) AS V
            LEFT JOIN
               (
                  SELECT
                     PID,
                     Geschlecht,
                     Year(Now()) - Year(GebDatum) AS [Alter]
                  FROM
                     Personal
                  WHERE
                     statusID_P IN(1, 2)
                        AND
                     gruppe Like "JF*"
               ) AS P
               ON V.ID = P.Geschlecht
                  AND
               V.I = P.[Alter]) AS B
GROUP BY
   B.[Alter]
PIVOT
   B.Geschlecht

Wünschenswert von Anfang an wäre, ordentliche Bezeichnungen für Tabellen und Felder zu verwenden, um auf die eckigen Klammern zur Maskierung von Bezeichnungen mit Sonderzeichen sowie Bezeichnungen in großer Nähe zu reservierten Worten (ALTER) verzichten zu können. Wer SQL-Anweisungen nicht nur hinwirft, sondern auch lesen und verstehen mag, wird solche reduzierende Übersicht zu schätzen wissen.
Mit freundlichem Glück Auf!

Eberhard

mad

ebs17,

erstmal herzlichen Dank für die entsprechenden Codes.

Um das aber besser verstehen zu können habe ich mich versucht das in Klarbezeichnungen darzustellen.
Für den ersten Code ist mir das auch schon gelungen. Für die folgenden bin ich noch dran. Irgendwann werde ich das schon hinbekommen!
Aber erstmal ist mir perfekt geholfen.

Tausend Dank
mad