Dynamischen Bericht erstellen

[Lachtaube]

Franz,

im Upload müsste vermutlich noch der Formularparameter gesetzt werden, um an die Feldnamen zu gelangen.

Zum Testen:

Code [Auswählen] Erweitern

Sub Test()
   Const DDL As String = _
         "CREATE TABLE t (" & vbLf & _
         "  a TEXT," & vbLf & _
         "  b TEXT," & vbLf & _
         "  c INTEGER);"

   Const APP As String = "INSERT INTO t VALUES ([@a], [@b], [@c]);"

   Const XTB As String = _
         "TRANSFORM AVG(c) AS x" & vbLf & _
         "SELECT a" & vbLf & _
         "FROM   t" & vbLf & _
         "GROUP BY a, b" & vbLf & _
         "PIVOT  b;"

   Dim i&, j&, va, vb

   va = Array("Peter", "Claudia", "Henriette", "Isolde", "Heinz")
   vb = Array("rot", "grün", "gelb", "blau", "weiß", "schwarz", "violett")

   CurrentDb.Execute DDL

   With CurrentDb.CreateQueryDef(vbNullString, APP)
      For i = 0 To UBound(va)
         .Parameters("@a") = va(i)
         For j = 0 To Int(Rnd * 3)
            .Parameters("@b") = vb(Int(Rnd * 5))
            .Parameters("@c") = Int(Rnd * 6) + 1
            .Execute
         Next
      Next
   End With

   CurrentDb.CreateQueryDef "q", XTB
     
   Dim fn$(), f
   
   With CurrentDb.QueryDefs("q")
      ReDim fn(.Fields.Count - 1): i = 0
      For Each f In .Fields
         fn(i) = "[" & f.Name & "]"
         i = i + 1
      Next
   End With
     
   CurrentDb.QueryDefs.Delete "q"
   CurrentDb.Execute "DROP TABLE t;"
     
   MsgBox Join(fn, ", ")

End Sub

[DF6GL]

Hi,

ja ok, danke.
Ich nehme an, das ist getestet...

Es bestätigt ja dann meine Vermutung, das im Query-Object eine statische Definition vorhanden sein muss, ob nun durch die Parameters-Angabe oder durch den In-Ausdruck...

[Lachtaube]

@Franz,

nein, ganz im Gegenteil - es widerlegt Deine Behauptung.

[X-ess]

Schönen guten Morgen,

erfreulich das hier so rege diskutiert wird, schwierig nachzuvollziehen für alle die den Titel Meister oder Guru noch nicht erworben haben

Gibt es einen einheitlichen Konsens bzw. eine Zusammenfassung, wie das Problem am einfachsten gelöst werden kann?
Ich werde mich mit den Anregungen von EUch nochmal dran setzen, der Vorschlag von Ekkehard mit der Function JahresListe scheint ein gangbarer Weg zu sein, wenn ich das Teil so nicht zum Laufen bekomme.

[Lachtaube]

Du musst Dich nur exakt an eines der drei Beispielberichte halten und den Code in das Berichtsmodul verfrachten. Wobei ich persönlich den Formularparameter eher gegen eine Funktion tauschen würde, um auch unabhängig vom Formular den Bericht nutzen zu können.

[X-ess]

Hallo nochmal,
ich bin hier am verzweifeln 

• Der große Unterschied zum Beispiel von Roger liegt, wie ich nun auch Verstanden habe, in den fehlenden Spaltenüberschriften der Kreuztabelle. Genau aus dem Grund bringt es aber auch nichts dieses Beispiel 1 zu 1 auf mein Problem anzuwenden, weil ich damit nicht weiter komme (Auslesen von Spaltenüberschriften über VBA unmöglich)
• Mit dem Zusatz "In ("2013","2014","2015","2016","2017")" im SQL-Befehl funktioniert der Bericht logischerweise wunderbar, löst aber nicht mein Problem
• Die Function JahresListe wäre sehr verlockend, funktioniert aber leider nicht -> "Fehlendes Element ( oder ]" bzw. "Syntax-Fehler in TRANSFORM Anweisung"

Hat noch jemand einen Plan wie ich hier weiter komme?
Kann ich die Funktion Year(Now()) irgendwie in den SQL-Befehl integrieren? -> In ("Year(Now())-2","Year(Now())-1", ...)

[X-ess]

Ich hatte nun eine zündende Idee: Statt die Überschriften aus der Kreuztabelle abzufragen habe ich diese einfach selbst im VBA-Code generiert. Damit scheint es gut zu laufen.
Ein Thema in Worte zu fassen hilft oft bei der Lösungsfindung.

Vielen Dank.

Code [Auswählen] Erweitern

sJahr = Year(Now())

Set db = CurrentDb
Set qdf = db.QueryDefs("qryStatistikAkq_AnzJeMA_Kreuztabelle")
FieldList = "[KW] as Field0, "
indexx = 1

    For iAnzFelder = 1 To 5
            FieldList = FieldList & "[" & sJahr & "] as Field" & indexx & ", "
            ReportLabel(indexx) = sJahr
'           MsgBox Label(indexx)
        indexx = indexx + 1
        sJahr = sJahr - 1
    Next iAnzFelder
    For i = indexx To 7
            FieldList = FieldList & "null as Field" & i & ","
     Next i

[PhilS]

Code [Auswählen] Erweitern


Set qdf = db.QueryDefs("qryStatistikAkq_AnzJeMA_Kreuztabelle")
indexx = 0
    For Each fld In qdf.Fields
          'Tue etwas
    Next fld


Nur mal so als Anmerkung. Ein QueryDef-Objekt kann in einigen Fällen (z.B. Kreutabellen, Pass-Through) unmöglich die Felder der Ergebnismenge kennen ohne die Abfrage auszuführen.

In diesem Fällen kann man die Felder über das resultierende Recordset ermitteln nachdem die Abfrage ausgeführt wurde.

Ich bin im Moment nicht sicher, ob man in einer MDB/ACCDB auf das Recordset eines Berichtes zugreifen kann um diese zu ermitteln. - Wäre aber einen Versuch wert.

[Lachtaube]

@PhilS,

es muss explizit kein Recordset für den Zugriff auf die Feldnamen der Kreuztabellenabfrage geöffnet werden! Eine Querydef-Instanz stellt diese (auch in einer Kreuztabellenabfrage ohne fixierte Spaltenüberschriften) zur Verfügung. Selbst der Abfrageentwurf über eine Kreuztabellenabfrage zeigt die dynamischen Feldnamen in der Feldliste an.

[PhilS]

es muss explizit kein Recordset für den Zugriff auf die Feldnamen der Kreuztabellenabfrage geöffnet werden!

Danke für den Hinweis. - Du hast recht und das gilt sogar auch für Pass-Through-Abfragen. - Ich bin entsetzt.

Entsetzt deshalb, weil meine Aussage:

Ein QueryDef-Objekt kann [...] unmöglich die Felder der Ergebnismenge kennen ohne die Abfrage auszuführen.

ebenso korrekt ist.

Ein "harmloser" Zugriff auf die Fields-Collection führt eine Abfrage bereits aus. - Das kommt für viele sicherlich unerwartet und ist ziemlich schlecht, wenn entweder die Abfrage eine sehr lange Laufzeit hat und/oder (bei Pass-Through-Abfragen) auch DML-Operationen in einer Stored Procedure ausgeführt werden, die man vermutlich nur bei einem expliziten Execute/OpenRecordset tatsächlich ausführen möchte.

[Beaker s.a.]

Hallo,

3.Die Function JahresListe wäre sehr verlockend, funktioniert aber leider nicht -> "Fehlendes Element ( oder ]" bzw. "Syntax-Fehler in TRANSFORM Anweisung"

Das kann aber nicht an der Function liegen, die gibt die gewünschte
Auflistung der Jahreszahlen SQL-konform zurück. Da stimmt was mit
der Abfrage an sich nicht. Eine fehlende Klammer sollte man aber mit
Debug.Print ausfindig machen können.
gruss ekkehard

[jagger]

Hallo Beaker,
ich habe eine Frage zu "Spaltenüberschriften mit Funktion variabel gestalten". Da mein Formular als Ufo leider nicht funktioniert, weil Access dann bei der Kreuztabelle, die die Daten liefert, fixierte Spaltenüberschriften verlangt, könnte der Kniff mit der Funktion die Lösung sein. Das Ufo (Endlos) hat ungebundene Textfelder. Mit .ControlSource (Kombifeld pro Spalte) weise ich den Steuerlelementeinhalt zu. Klappt super. Aber eben nicht wenn ich das Formular als Ufo nutze. Die Spaltennamen sind Abkürzungen z.B. "ZE", "ADB", "DfR";...
Hier mal die SQL-Ansicht von der Abfrage:

Code [Auswählen] Erweitern

PARAMETERS [Formulare]![frm_Kalender]![WJ] Text ( 255 ), [Formulare]![frm_Kalender]![tfldDatum] Text ( 255 );
TRANSFORM Count(qry_Kalender_Wochenübersicht_mt_Kollektionen_01.tAGKDNR) AS AnzahlvontAGKDNR
SELECT IIf([Stunden]="iLdT","iLdT",[Stunden] & ":00") AS h
FROM tab_int_Stunden_Kalender LEFT JOIN qry_Kalender_Wochenübersicht_mt_Kollektionen_01 ON tab_int_Stunden_Kalender.Stunden = qry_Kalender_Wochenübersicht_mt_Kollektionen_01.Stundenkey
GROUP BY tab_int_Stunden_Kalender.Sortierung, IIf([Stunden]="iLdT","iLdT",[Stunden] & ":00")
ORDER BY tab_int_Stunden_Kalender.Sortierung
PIVOT IIf([Kollkürzel] Is Null,"alle",[Kollkürzel]);
Die Abkürzungen [Kollkürzel] werden vom User festgelegt, sind also variabel. Sie stehn in der Tabelle tab_ex_Lieferanten.
Wie muss nun die Funktion aussehen, die einfach nur alle [Kollkürzel] liefert?
Wie bekomme ich die in die Abfrage?
Ich habe keinen vernünftigen Weg gefunden.
Kannst Du hier bitte helfen?
Ich hoffe, ich habe alle nötigen Infos aufgeführt

Danke und LG
jagger
(Access 2016)

[Lachtaube]

Ein Beispiel einer etwas anderen Umsetzung findest Du unter: Variable Bedingung in Kreuztabelle für Unterformular

[Beaker s.a.]

Hallo Jagger,
Wenn die Überschriften in einer Tabelle stehen brauchst du keine
Funktion, da reicht eine Unterabfrage.

Code [Auswählen] Erweitern

PIVOT IN (SELECT [Kollkürzel] FROM tab_ex_Lieferanten)
Falls die Kürzel in der Tabelle mehrfach auftauchen

Code [Auswählen] Erweitern

PIVOT IN (SELECT DISTINCT [Kollkürzel] FROM tab_ex_Lieferanten)
gruss ekkehard

[jagger]

Hallo Ekkehard,

ich habe beide Varianten ausprobiert (in der SQL-Ansicht der Abfrage eingetragen), aber leider kommt da nur die Meldung: "Syntaxfehler in TRANSFORM-Anweisung" 
Jetzt stehe ich völlig auf dem Schlauch.

Hast Du eine Idee?
LG
jagger