Hallo zusammen,
ich möchte in einem Bericht mehrere unterschiedliche Gruppierungen und Summenberechnungen unterbringen, und das auch noch für einen bestimmten auszuwählenden Zeitraum (Ist das überhaupt möglich?).
Folgende Ausgangsgrundlage:
In einer Tabelle gibt es 4 unterschiedliche Personen die Aufträge vergeben können. Diese 4 Personen kommen aus 2 Fachabteilungen (jeweils 2 Personen pro Fachabteilung). Bei den jeweiligen Aufträgen dieser Personen können maximal 3 Rechnungssummen und 3 Auszahlungssummen vorhanden sein. Zusätzlich gibt es noch 3 mögliche Rechnungsdatums. Die Tabelle ist also von den Feldern her vereinfacht so aufgebaut:
Feld 1: ID
Feld 2: Auftraggber
Feld 3: Rechnungssumme 1
Feld 4: Rechnungssumme 2
Feld 5: Rechnungssumme 3
Feld 6: Auszahlungssumme 1
Feld 7: Auszahlungssumme 2
Feld 8: Auszahlungssumme 3
Feld 9: Rechnungsdatum 1
Feld 10: Rechnungsdatum 2
Feld 11: Rechnungsdatum 3
Jetzt möchte ich in einem Bericht folgendes anzeigen lassen:
zuerst soll eine Parameterabfrage den Rechnungszeitraum eingrenzen. Würde ich in einer Abfrage normalerweise über Parameter->[vonDatum] und [bisDatum] als Kriterium für Rechnungsdatum 1 bis 3 machen. Aber da kommen ja jetzt noch mehrere mögliche Filteroptionen. Der Bericht soll mir also für ein zuvor ausgewählendes vonDatum/bisDatum folgendes anzeigen:
1. Summe von Rechnungssumme 1-3 für Auftraggeber 1 oder 2
2. Summe von Auszahlungssumme 1-3 für Auftraggeber 1 oder 2
3. Summe von Rechnungssumme 1-3 für Auftraggeber 3 oder 4
4. Summe von Auszahlungssumme 1-3 für Auftraggeber 3 oder 4
Bekommt man das in einer Abfrage überhaupt hin oder muss ich mir da was aus mehreren Abfragen zusammenbasteln?
Gruß
Marcus
Hallo,
bevor Du Dich in Tricksereien verlierst, normalisiere die Tabelle (bzw. die ganze Datenstruktur) und baue den Bericht darauf auf. Dann kannst Du nach Belieben gruppieren, sortieren, berechnen und weitere schöne Dinge anstellen... 8)
Hallo Franz,
das normalisieren der Tabelle ist denke ich zu spät, da schon zuviele Daten darin enthalten sind. Irgendwie muss es doch per VBA oder so möglich sein die verschiedenen Summenbildungen im Bericht oder einer Abfrage durchzuführen. Wie wäre das mit einem bericht mit Unterberichten, die auf unterschiedlichen Abfragen basieren?
Gruß
Marcus
Hallo,
Zitatdas normalisieren der Tabelle ist denke ich zu spät
es ist nie zu spät... die Daten (egal wie viele) allein sind auch jetzt noch in normalisierte Daten (Tabellenstruktur) überführbar.
Du kannst ja mal die von Dir schon selber angedachte (Trick-)Lösung verfolgen..
Soll heißen, für jede Gruppe der zusammengehörenden Textfelder (xxx1, xxx2, xxx3) einen Bericht mit passender Abfrage erstellen und diese in einen vierten Bericht (enthält ID und Auftraggeber) als Unterberichte einbauen.
Ich habe die Idee mit den Unterberichten mal weiterfolgt, allerdings bereitet die Sache mit den zuvor anzugebenden Parametern für die Eingrenzung des Rechnungszeitraums noch Probleme.
Ich habe testweise mal 3 Abfragen erstellt.
1.) Abfrage 1 für SummevonRechnungssumme 1 und SummevonAuszahlungssumme 1 für Auftraggeber 1oder2. 3x Rechnungsdatum 1 (1xBedingung vonDatum/bisDatum, 1xMin (Gruppierung), 1xMax (Gruppierung)
2.) Abfrage 2 für SummevonRechnungssumme 2 und SummevonAuszahlungssumme 2 für Auftraggeber 1oder2. 3x Rechnungsdatum 2 (1xBedingung vonDatum/bisDatum, 1xMin (Gruppierung), 1xMax (Gruppierung)
3.) Abfrage 3 für SummevonRechnungssumme 3 und SummevonAuszahlungssumme 3 für Auftraggeber 1oder2. 3x Rechnungsdatum 3 (1xBedingung vonDatum/bisDatum, 1xMin (Gruppierung), 1xMax (Gruppierung)
Klicke ich jede Abfrage mal einzeln an, werde ich nach dem [von Datum] und [bis Datum] gefragt und anschließend werden die Rechnungs-/Auszahlungssummen und das minimale und maximale Rechnungsdatum innerhalb des zuvor ausgewählten Zeitraums angezeigt.
So weit so gut.
Auf Basis dieser 3 Abfragen habe ich 3 entsprechende Berichte erstellt. Jeden Bericht einzeln geöffnet das gleiche, Abfrage Zeitraum, anschließend Anzeige von Rechnungs-/Auszahlungssummen und das minimale und maximale Rechnungsdatum.
Jetzt habe ich einen 4. Bericht erstellt und die 3 zuvor genannten Berichte als Unterberichte eingebaut. Jetzt habe ich nur folgendes (logisches) Problem: Ich werde beim Öffnen des 4. Berichts 3x (für die 3 Unterberichte) nach dem Rechnungszeitraum (vonDatum, bisDatum) gefragt, was natürlich nervig ist, da der auszuwählende Zeitraum ja für alle 3 Unterberichte/Abfragen gelten soll. Gibt es da eine Möglichkeit das der Zeitraum nur einmal abgefragt wird und dann an die Abfragen der Unterberichte weitergegeben wird?
Hallo,
die einzelnen Abfragen müssten erst per VBA mit den Kriterien versorgt und danach der Bericht geöffnet werden. Die z. Z. in den Abfragen enthaltenen Parameter müssen entfernt werden.
z.B:
Dim db as Database
set db = Currentdb
db.Querydefs!Abfrage1.SQL =" select .......... where [Rechnungsdatum 1] between " & Format(Me!vonDatum,"\#yyyy-mm-dd\#) & " and " & Format(Me!bisDatum,"\#yyyy-mm-dd\#)
db.Querydefs!Abfrage2.SQL =" select .......... where [Rechnungsdatum 2] between " & Format(Me!vonDatum,"\#yyyy-mm-dd\#) & " and " & Format(Me!bisDatum,"\#yyyy-mm-dd\#)
db.Querydefs!Abfrage3.SQL =" select .......... where [Rechnungsdatum 3] between " & Format(Me!vonDatum,"\#yyyy-mm-dd\#) & " and " & Format(Me!bisDatum,"\#yyyy-mm-dd\#)
Docmd.Openreport "rptRechnungen",acpreview,,"ID = " & Me!ID
Die Unterberichte müssen über das ID-Feld mit dem Hauptbericht verknüpft werden.
Bei welcher Ereignisprozedur müsste der VBA-Code denn plaziert werden? "Bei Laden", "Beim Öffnen" in der Berichtseigenschaft?
Und wie mache ich das mit der ID? Generiere ich mir da eine neue (die Auftrags-ID der einzelnen Aufträge kann ich ja nicht nehmen, da ich die dazugehörigen Rechnungssummen ja in der Funktion aufsummiere)? Z.B. eine ID die ein aktuelles Datumsfeld ist? Sorry, wie Du bestimmt mekrst bin ich in puncto VBA und Access-Aufbau nicht gerade der Fortgeschrittenste...
Hallo,
das gehört z. B. in die Click-Ereignisprozedur einer Formularschaltfläche.
Wenn die die Berichtsdaten nicht von der ID abhängen, brauchst Du die Verknüpfungseigenschaften auch nicht benutzen und das Öffnungskriterium bei Openreport kann auch entfallen.
Hallo,
du kannst die Kriterien auch mit Hilfe von Funktionsaufrufen in die Abfrage holen.
Ein Beispiel dazu findest du in der Access-FAQ 3.15 (http://www.donkarl.com?FAQ3.15).
@Franz: Die Berichtsdaten hängen zwar nicht von einer (Auftrags-)ID ab, aber von einem zuvor per Parameter festzulegenden von-bis-Datum (ich will ja nicht die Rechnungssumme über den kompletten Datenbestand angezeigt bekommen, sondern nur für einen bestimmten Abrechnungszeitraum). Wenn ich jetzt die Parameter aus den Abfragen lösche und den Bericht mit den 3 integrierten Unterberichten öffne, kommt natürlich keine Abfrage des gewünschten Zeitraums und es werden alle Rechnungssummen angezeigt. Geht also so nicht.
@MaggieMay: Leider werde ich aus der Hilfe nicht ganz schlau (bin ein Access-Dummie). Wie erstelle ich die Funktion in den Abfragen? Zur Zeit sehen die 3 Abfragen so aus wie im Anhang dargestellt. Und wo würde ich den Code
Public Function fctSendVar() ' hier evtl. noch den Typ angeben
fctSendVar = DeineGlobaleVariable
End Function
platzieren?
Hallo,
zu den Parametern habe ich mich schon geäußert.
Allerdings ist die dargestellte Berichtsabfrage so nicht zu gebrauchen, wenn Datumsbereiche eingeschränkt werden sollen. Dazu müssen die Datumsfelder mit in die Abfrage aufgenommen werden. Wenn dadurch aber die geweünschte Gruppierung "flöten" geht, dann ist es sinnvoller, die Gruppierung(en) und Berechnungen im Bericht selber zu machen und die Berechnungen/Gruppierungen aus der Abfrage heraus zu nehmen..
Hallo Franz,
zu der Abfrageansicht (angehängtes JPG) muss ich dazu sagen, dass ich das Feld "Rechnungsprüfung 1 am" noch einmal zusätzlich in der Abfrage hatte. Dieses Feld hatte als Funktion "Bedingung" und als Kriterium "zwischen [von Datum] und [bis Datum]", wobei [von Datum] und [bis Datum] als Parameter in der Abfrage deklariert waren. Mit dieser Abfrage hatte die Zeitraumeingrenzung auch wunderbar funktioniert. Vor dem Öffnen wurde ich nach [von Datum] und [bis Datum] gefragt und nach der Eingabe wurde die akkumulierte Rechnungssumme, die akkumulierte Auszahlungssumme sowie das minimale und das maximale Rechnungsdatum innerhalb des zuvor ausgewählten Zeitraum angezeigt. Wunderbar also, nur beim Öffnen des Berichts (bzw. der 3 Unterberichte) wurde der Zeitraum 3x abgefragt und nicht wie gewünscht nur einmal.
Gruß
Marcus
Hallo,
ZitatDieses Feld hatte als Funktion "Bedingung" und als Kriterium "zwischen [von Datum] und [bis Datum]", wobei [von Datum] und [bis Datum] als Parameter in der Abfrage deklariert waren.
Hast Du denn mein vor-vorletztes Posting nicht verstanden?
Meinst Du
Zitatdas gehört z. B. in die Click-Ereignisprozedur einer Formularschaltfläche.
Wenn die die Berichtsdaten nicht von der ID abhängen, brauchst Du die Verknüpfungseigenschaften auch nicht benutzen und das Öffnungskriterium bei Openreport kann auch entfallen.
Deswegen hatte ich ja die Parameterabfrage aus der Abfrage entfernt....
Sorry, ich weiß ich bin ein schwerer Fall :-\
Hallo,
nein, ich meinte das so, wobei die "select...." -Teile eben dem Abfrage-SQL-String entsprechen. Abfrage1-Abfrage3 sind die Abfragen für die 3 Unterberichte:
ZitatDie z. Z. in den Abfragen enthaltenen Parameter müssen entfernt werden.
z.B:
Code: [Auswählen]
Dim db as Database
set db = Currentdb
db.Querydefs!Abfrage1.SQL =" select .......... where [Rechnungsdatum 1] between " & Format(Me!vonDatum,"\#yyyy-mm-dd\#) & " and " & Format(Me!bisDatum,"\#yyyy-mm-dd\#)
db.Querydefs!Abfrage2.SQL =" select .......... where [Rechnungsdatum 2] between " & Format(Me!vonDatum,"\#yyyy-mm-dd\#) & " and " & Format(Me!bisDatum,"\#yyyy-mm-dd\#)
db.Querydefs!Abfrage3.SQL =" select .......... where [Rechnungsdatum 3] between " & Format(Me!vonDatum,"\#yyyy-mm-dd\#) & " and " & Format(Me!bisDatum,"\#yyyy-mm-dd\#)
Docmd.Openreport "rptRechnungen",acpreview,,"ID = " & Me!ID
Die Unterberichte müssen über das ID-Feld mit dem Hauptbericht verknüpft werden.
Hallo,
so ließe sich die Parameterübergabe per Funktionsaufruf umsetzen:
1. deklariere zwei globale Variablen in einem allgemeinen Modul:
Public pDatVon, pDatBis
2. schreibe darunter folgende Funktionen:
Public Function getDatVon()
getDatVon = Format(pDatVon,"\#yyyy-mm-dd\#)
End Function
Public Function getDatBis()
getDatBis = Format(pDatBis,"\#yyyy-mm-dd\#)
End Function
3. schreibe folgenden Code ins Load-Ereignis des Hauptberichts:
pDatVon = InputBox("Bitte Datum-von eingeben:", "Berichtsanforderung", DateAdd("m",-1,Date))
pDatBis = InputBox("Bitte Datum-bis eingeben:", "Berichtsanforderung", Date)Wenn die Vorgabe eines Defaultwertes keinen Sinn macht, kannst du das natürlich weglassen.
4. ändere den SQL-Code der Abfragen wie folgt:
Select ... From ... Where [Rechnungsdatum 1] Between getDatVon() And getDatBis
Natürlich kannst du auch anstelle der globalen Variablen und Abfrage der Daten per Inputbox aus den "get-Funktionen" direkt auf Formularfelder zugreifen.
Vorteil: Es können gespeicherte Abfragen ohne Anpassung eingesetzt werden.
Also, ich verzweifle hier langsam...
ich habe die Schritte von MaggieMay mal befolgt, aber ich bekomme fehlerhafte Syntaxmeldungen und unbekannte Ausdrücke. Ich habe mal meine DB in vereinfachter/verkürzter Form eben neu erstellt (inkl. des Moduls) und im Anhang beigefügt. Aber wenn ich auf Bericht4 klicke (der die 3 Unterberichte zu den 3 Abfragen enthält), dann funktioniert nix.
Wo kann denn da der Fehler liegen?
Gruß
Marcus
Hi,
es fehlen die schließenden Gänsefüßchen in der Format-Anweisung.
Sorry, mein Fehler, aber man hätte drauf kommen können...
Den Datentypenkonflikt verursachen möglicherweise die Leerfelder, um ihn zu beheben, könnte man evtl. die Kriterien umschreiben:
FROM Tabelle1
WHERE (
((Tabelle1.Auftraggeber)="Meier" Or (Tabelle1.Auftraggeber)="Müller") AND
((Tabelle1.Rechnungsdatum3) Is Not Null)
)
GROUP BY Tabelle1.Auftraggeber
HAVING
(((Tabelle1.Rechnungsdatum3) Between getDatVon() And getDatBis()))
;
So, Änderungen übernommen, jetzt kommt die Meldung "Sie wollten eine Abfrage ausführen, die den angegeben Ausdruck 'Tabelle.Rechnungsdatum1 Between getDatVon() And getDatBis()' nicht als Teil der Aggregatfunktion einschließt."
Öffne ich die Abfragen in der Entwurfsansicht und wechsel in die SQL-Ansicht steht da Folgendes:
In Abfrage 1:
SELECT Tabelle1.Auftraggeber, Sum(Tabelle1.Rechnungssumme1) AS SummevonRechnungssumme1, Sum(Tabelle1.Auszahlungssumme1) AS SummevonAuszahlungssumme1
FROM Tabelle1
WHERE (((Tabelle1.Auftraggeber)="Meier" Or (Tabelle1.Auftraggeber)="Müller") AND
((Tabelle1.Rechnungsdatum1) Is Not Null))
GROUP BY Tabelle1.Auftraggeber
HAVING (((Tabelle1.Rechnungsdatum1) Between getDatVon() And getDatBis()));
In Abfrage 2:
SELECT Tabelle1.Auftraggeber, Sum(Tabelle1.Rechnungssumme2) AS SummevonRechnungssumme2, Sum(Tabelle1.Auszahlungssumme2) AS SummevonAuszahlungssumme2
FROM Tabelle1
WHERE (((Tabelle1.Auftraggeber)="Meier" Or (Tabelle1.Auftraggeber)="Müller") AND
((Tabelle1.Rechnungsdatum2) Is Not Null))
GROUP BY Tabelle1.Auftraggeber
HAVING (((Tabelle1.Rechnungsdatum2) Between getDatVon() And getDatBis()));
In Abfrage 3:
SELECT Tabelle1.Auftraggeber, Sum(Tabelle1.Rechnungssumme3) AS SummevonRechnungssumme3, Sum(Tabelle1.Auszahlungssumme3) AS SummevonAuszahlungssumme3
FROM Tabelle1
WHERE (((Tabelle1.Auftraggeber)="Meier" Or (Tabelle1.Auftraggeber)="Müller") AND
((Tabelle1.Rechnungsdatum3) Is Not Null))
GROUP BY Tabelle1.Auftraggeber
HAVING (((Tabelle1.Rechnungsdatum3) Between getDatVon() And getDatBis()));
Die o.g. Meldung erhalte ich 3x, dann öffnen sich die Abfrageboxen Eingabe vonDatum und Eingabe bisDatum und danach der Bericht, der allerdings keinen Inhalt (keine 3 Unterberichte) zeigt.
Hi,
das mit der Gruppierung kann man noch hinkriegen:
SELECT Tabelle1.Auftraggeber,
Sum(Tabelle1.Rechnungssumme3) AS SummevonRechnungssumme3,
Sum(Tabelle1.Auszahlungssumme3) AS SummevonAuszahlungssumme3
FROM Tabelle1
WHERE (
((Tabelle1.Auftraggeber)="Meier" Or (Tabelle1.Auftraggeber)="Müller") AND
(NZ(Tabelle1.Rechnungsdatum3,0) Between getDatVon() And getDatBis())
)
GROUP BY Tabelle1.Auftraggeber
;
aber dann geht's immer noch nicht.
Ich weiß nicht, warum das mit der Datumsformatierung nicht klappt, also versuche es mal hiermit:
SELECT Tabelle1.Auftraggeber,
Sum(Tabelle1.Rechnungssumme1) AS SummevonRechnungssumme1,
Sum(Tabelle1.Auszahlungssumme1) AS SummevonAuszahlungssumme1
FROM Tabelle1
WHERE (
(Nz(Tabelle1.Rechnungsdatum1,0) Between getDatVon() And getDatbis()) And
((Tabelle1.Auftraggeber)="Meier" Or (Tabelle1.Auftraggeber)="Müller"))
GROUP BY Tabelle1.Auftraggeber;
Das gilt entsprechend für alle drei Abfragen.
Und die Funktionen ändern sich wie folgt:
Public Function getDatVon() As Double
getDatVon = CDbl(DateValue(pDatVon)) 'Format(DateValue(pDatVon), "#yyyy-mm-dd#")
End Function
Public Function getDatBis() As Double
getDatBis = CDbl(DateValue(pDatBis)) 'Format(DateValue(pDatBis), "#yyyy-mm-dd#")
End Function
Hallo MaggieMay,
Auch mit diesen Änderungen funktioniert es nicht richtig. Ich habe die entsprechende Datenbank mal beigefügt.
Mache ich einen Doppelklick auf die Abfragen öffnen sich diese zwar ohne Fehlermeldung, es wird in den Feldern (Summenfeldern und Auftraggeberfeld) aber auch nichts angezeigt. Es erfolgt auch keine Datumsabfrage.
Öffne ich den Bericht4, bekomme ich die Fehlermeldung "Laufzeitfehler '13' Typen unverträglich" und im Debugger wird die Zeile
getDatVon = CDbl(DateValue(pDatVon)) 'Format(DateValue(pDatVon), "#yyyy-mm-dd#")
aus dem Modul angezeigt.
Was wäre, wenn man folgende einfachere Programmierungslösung macht:
Wenn ich in einem Berichtsauswahlformular den Bericht4 auswähle öffnet sich ein neues Formular. Darin habe ich 2 Buttons ("Schließen" und "Öffnen") und 2 ungebundene Textfelder. In diesen Textfeldern gebe ich das vonDatum und bisDatum ein. Beim Klick auf den Button "Öffnen" sollen die Abfragen 1 bis 3 die Werte aus den ungebundenen Texfeldern als Kriterium für die Zeitraumeingrenzung nutzen.
Das müsste doch eigentlich einfacher sein als o.g. Lösung, oder? Frage wäre, wie müsste die on-klick-Prozedur des "Öffnen"-Buttons aussehen und wie der Aufbau der Abfragen?
Gruß
Marcus
Hallo,
im Grunde genommen ist das ein Thema das zeigt, dass eine normalisierte Struktur unbedingt anzustreben ist. Wenn Du am 25.9. begonnen hättest eine normalisierte Struktur anzulegen, wäre das innerhalb einer kurzen Zeit erledigt gewesen. Ohne Unterberichte mit deren Tücken.
Wenn Du bei der Struktur bleiben willst, könnte man auch über eine Unionabfrage nachdenken, die die normalisierte Struktur behelfsmäßig erzeugt.
Dann kann man diese Abfrage für den Bericht verwenden und braucht dann auch keine UBs.
Sorry, ich hatte vergessen darauf hinzuweisen, dass die Parameter im OPEN-Ereignis des Berichts abgefragt werden müssen.
PS:
Das hättest du eigentlich merken müssen, dass die Datumsabfrage viel zu spät kommt...
Nachtrag:
Der Vorschlag von Klaus ist leicht umzusetzen:
SELECT Abfrage1.Auftraggeber, 1 as Spalte, Abfrage1.SummevonRechnungssumme1 As Rechnungsbetrag, Abfrage1.SummevonAuszahlungssumme1 As Auszahlungsbetrag
FROM Abfrage1;
UNION
SELECT Abfrage2.Auftraggeber, 2, Abfrage2.SummevonRechnungssumme2, Abfrage2.SummevonAuszahlungssumme2
FROM Abfrage2;
UNION
SELECT Abfrage3.Auftraggeber, 3, Abfrage3.SummevonRechnungssumme3, Abfrage3.SummevonAuszahlungssumme3
FROM Abfrage3;
und ein Bericht auf Basis dieser Abfrage schnell erstellt.
Aber auch die Eingabe der Parameter via Formular ist eine gute Idee, ändert aber nichts an der Notwendigkeit einer geeigneten Formatierung der Datumswerte.
Hallelujah!!!
Problem gelöst! Ich habe jetzt einfach wie zuvor beschrieben ein Formular erstellt mit 2 ungebundenen Textfeldern. Dann einen Button der einen Bericht öffnet. In diesem Bericht 3 Unterberichte die sich auf jeweilige Abfragen beziehen. In diesen Abfragen als Kriterium für das Rechnungsdatum "Zwischen [Forms]![FRM_Zeitraumauswahl_Rechnungsstatistik1]![vonDatum] Und [Forms]![FRM_Zeitraumauswahl_Rechnungsstatistik1]![bisDatum]"
geschrieben und schon funktioniert es wie gewünscht.
Auch wenn es jetzt eine etwas umständlichere und wahrscheinlich programmierungstechnisch unbedarfte Lösung ist Euch Allen vielen Dank für die Eure Hilfe!
Gruß
Marcus