Endlos-Formular: wie kann ich die Anzahl unterschiedlicher Werte anzeigen?

[herb3472]

Hallo,

ich habe ein Endlos-Formular zur Anzeige von Bestellungen und den zugeordneten Bestellpositionen. Jede Bestellung besteht aus einer oder mehreren Bestellpositionen (Datensätzen), die einer gemeinsamen Auftragsnummer zugeordnet sind. Im Formularfuß möchte ich nun ein Feld generieren, welches mir die Anzahl der Aufträge (=unterschiedlichen Auftragsnummern) anzeigt.

Beim Googeln habe ich leider nichts gefunden, vielleicht kann mir hier jemand einen Tip geben?

MfG Herb

[DF6GL]

Hallo,

unklar ist die Datenkonstellation:  Was heißt: "Jede Bestellung besteht aus einer oder mehreren Bestellpositionen (Datensätzen), die einer gemeinsamen Auftragsnummer zugeordnet sind."

Sind die Auftragsnummern nun den Bestellpositionen oder den Bestellungen zugeordnet ?

Wie lauten die beteiligten Tabellen und deren Schlüsselfelder?

[herb3472]

@DF6GL:

Die Datensätze entstammen einer Auswahlabrage zur Eingrenzung (Autragsdatum_von, Auftrragsdatum_bis, etc....), Quelle ist die Tabelle tbl_Bestellungen. Jede Bestellung hat eine Auftragsnummer (um die gehts hier) und unter dieser Auftragsnummer verschiedene Bestellpositionen:

Auftrags-Nr     Bestell_ID:           Artikel_Nr

1300001               01                    012345
1300001               02                    345672
1300001               03                    889637

1300002               04                    543210
1300002               05                     382001

1300003               06                     777999
1300003               07                     613347
1300003               08                     000807
1300003               09                     915273
__________________________________

  = 3  (Anzahl der Auftragsnummern soll im Fuß des Endlosformulars angezeigt werden

Mit einer extra SQL-Abfrage beim Neuladen des Formulars läßt sich das natürlich hinkriegen, ich denke mir aber, dass es bei Access doch dafür eine Aggregatfunktion geben müsste, da diese Rechenoperation doch meines Erachtens relativ häufig vorkommt?

MfG Herb

[DF6GL]

Hallo,

naja,  das sieht aber so aus, als hätte jede Bestellung (Bestellnummer, Bestell_ID) einen Auftrag zugewiesen...

Erstelle eine Abfrage ("qry_Auftraege"):

Select [Auftrags-Nr] From tbl_Bestellungen) group by [Auftrags-Nr]

und in den Steuerelementinhalt eines Textfeldes im Formularfuß kommt:

=DCount("*";"qry_Auftraege")

[herb3472]

@DF6GL

Herzlichen Dank für Deinen Tipp - im Prinzip funktionierts - aber leider nur im Prinzip. Als Datenbasis für das Endlosforumaler habe ich eine sehr umfangreiche Auswahlabfrage mit 8 einstellbaren Auswahlparametern. Wenn ich da noch eine Gruppierungsabfrage dranhänge, wird das Ding so langsam, dass es nicht mehr praktikabel ist.

Außerdem tritt ein Phänomen auf, dessen Ursache ich mir überhaupt nicht erklären kann: das Ergebnis von DCount wird nur angezeigt, wenn dieses Textfeld den Focus hat.

Vielleicht ist es doch die bessere Lösung, wenn ich ein paar Zeilen VBA-Code schreibe?

MfG Herb

[DF6GL]

Hallo,


mit ein paar Zeilen VBA ist es auch nicht getan..


Du solltest, wie vorher schon gefragt, den Aufbau der Tabellen detailliert schildern und evtl. auch die Selektionskriterien angeben.

[herb3472]

Hier die dem Endlosformular zugrunde liegende Abfrage:

SELECT tbl_Bestellungen.Mark1, tbl_Bestellungen.BestellNr, Format([Bestelldatum],"dd/mm/yyyy") AS Anzeigedatum, Format([Bestelldatum],"Short Time") AS AnzeigeUhrzeit, tbl_Bestellungen.TischNr, tbl_Mitarbeiter.Kellner_Kennung, tbl_Bestellungen.[BestellteAnzahl*], [BestellteAnzahl]-[BestellteAnzahl*] AS BestellteAnzahlNetto, tbl_Bestellungen.BestellteAnzahl, tbl_Bestellungen.ArtikelKurztext, tbl_Bestellungen.ArtikelEinzelpreis, tbl_Bestellungen.[Artikelsumme*], [Artikelsumme]-[Artikelsumme*] AS ArtikelSummeNetto, tbl_Bestellungen.ArtikelSumme, tbl_Zahlungsarten.Zahlungsart
FROM tbl_Zahlungsarten INNER JOIN (tbl_Mitarbeiter INNER JOIN tbl_Bestellungen ON tbl_Mitarbeiter.Kellner_Schluessel = tbl_Bestellungen.KellnerRef) ON tbl_Zahlungsarten.ZahlungsartID = tbl_Bestellungen.Zahlungsart
WHERE (((tbl_Bestellungen.BestellNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) AND ((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.TischNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((tbl_Zahlungsarten.Zahlungsart)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis])) OR (((tbl_Bestellungen.BestellNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) AND ((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((tbl_Zahlungsarten.Zahlungsart)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) Is Null)) OR (((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.TischNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((tbl_Zahlungsarten.Zahlungsart)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) Is Null)) OR (((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((tbl_Zahlungsarten.Zahlungsart)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) Is Null) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) Is Null)) OR (((tbl_Bestellungen.BestellNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) AND ((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.TischNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) Is Null)) OR (((tbl_Bestellungen.BestellNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) AND ((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) Is Null) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) Is Null)) OR (((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.TischNr)=[Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) Is Null) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) Is Null)) OR (((Format([Bestelldatum],"Short Time")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlUhrzeitBis]) AND ((tbl_Bestellungen.ArtikelEinzelpreis)<>0) AND ((Format([Bestelldatum],"yyyy/mm/dd")) Between [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumVon] And [Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlDatumBis]) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlTischNr]) Is Null) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlBestellNummer]) Is Null) AND (([Formulare]![frm_UmsatzBericht_intern]![cmb_AuswahlZahlungsart]) Is Null))
ORDER BY tbl_Bestellungen.BestellNr, Format([Bestelldatum],"dd/mm/yyyy"), Format([Bestelldatum],"Short Time");

Zur Eingrenzung und Auswahl der Datensätze aus der Tabelle tbL_Bestellungen dienen 8 Kombinationsfelder des Hauptformulars:

cmb_Berichtsauswahl (es stehen verschiedene Berichte in Form von Unterformularen zur Auswahl), der gegenständliche ist nur einer von mehreren;
cmb_AuswahldatumVon, cmb_AuswahldatumBis, cmb_AuswahlUhrzeitVon, AuswahlUhrzeitBis zur Eingrenzung des Berichtszeitraums;
cmb_AuswahlBestellNr, cmb_AuswahlTischNr und cmb_AuswahlZahlungsart zur Eingrenzung von auftragsspezifischen Details

Defaultmäßig werden alle Bestellungen des ausgewählten Berichtszeitraums angezeigt, zur weiteren Eingrenzung dienen die Kombinationsfelder cmb_AuswahlBestellNr, cmb_AuswahlTischNr und cmB_AuswahlZahlungsart, wobei die dort angebotenen Auswahllisten abhängig vom aktuellen Inhalt der Auswahlabfrage sind (Default ist jeweils NULL, es können nur Eingrenzungskriterien ausgewählt werden, die in dem ausgewählten Berichtszeitraum auch tatsächlich vorhanden und gelistet sind).

Hoffe, dass die Information ausführlich und transparent genug ist?

Mfg Herb

[DF6GL]

Hallo,


mhmm,  und diese Where-Condition funktioniert?? 

Unabhängig von der Vergewaltigung des Datums (in Bezug auf die fragwürdige Umformatierung) und der schlechten, weil unformatierten Darstellung des SQL-Strings sehe ich auch nicht die Bedeutung der wiederholten gleichen Kriterien... Diese massenartige Verwendung der Format-Funktion sorgt ja auch nicht gerade zur Beschleunigung der Abfrage.

Gehört dieser SQL-String zu  einer abgespeicherten Abfrage oder befindet sich der SQL-String in der Datenherkunft des Forms?

Was ist mit:
"Wenn ich da noch eine Gruppierungsabfrage dranhänge, wird das Ding so langsam, dass es nicht mehr praktikabel ist."

gemeint? Wo dranhängen? Wie lang dauert die Abfrage als solche? 

Möglicherweise  könnte man mit einer Ersatzfunktion für DCount ein Quäntchen Performance herausreissen. Den größten Performance-Gewinn erreichte man, wenn diese Datums-Formatierungen eliminiert würden.


Wenn diese Kriterien so eingesetzt bleiben müssen wie sie sind, sehe ich jetzt ad hoc keine andere Möglichkeit als die beschriebene. 

[herb3472]

Der SQL-String ist als Abfrage extern und nicht im Formular abgespeichert. Ich habe die Abfrage mit dem grafischen Access-Abfrage-Generator gemacht, dabei wird der SQL-String von der Syntax her nicht unbedingt optimiert generiert, denke ich. Ich hatte mir bisher auch noch nicht die Mühe gemacht, den SQL-String anzuschauen, sondern hatte mich lediglich auf die grafische Darstellung beschränkt.

Was Du mit der "Vergewaltigung des Datums" meinst, ist mir nicht ganz klar. Für die Datumseingrenzung "von Datum - bis Datum" per Kombinationsfeldern brauche ich für deren Auswahlliste eine Gruppierungsabfrage mit isoliertem Datum ohne Uhrzeit. Das Datum soll aufsteigend sortiert sein, daher ist dafür das Format "yyyy.mm.dd" erforderlich. Die Anzeige soll dagegen im bei uns üblichen Format "dd.mm.yyyy" erfolgen. Ich wüsste keinen anderen Weg, als das Datum "zu vergewaltigen"? Stehe ich auf der Leitung?

Die Abfrage ist deshalb so umfangreich, weil es zusätzlich zu den Auswahlmöglichkeiten für "Datum von" und "Datum bis" auch noch drei weitere Kombinationsfelder mit Parametern zur zusätzlichen Eingrenzung gibt ("Bestellnummer", "Tischnummer", "Zahlungsart"), von denen jedoch jeweils höchstens eines aktiv in die Auswahl eingreifen darf (Im Ruhezustand haben alle drei den Wert NULL, bei Auswahl eines Parameters werden die jeweils beiden andern auf NULL gesetzt). Das Ding ist ziemlich "sophisticated" und ermöglicht mit einem einzigen Formular (es gibt auch noch weitere) eine ganze Reihe von Aussagen über betriebliche Abläufe und Umstände.

Typische Abfragen könnten etwa so aussehen:

"Welche Bestellungen gab es im Zeitraum [Datum_von] bis [Datum_bis] zwischen [Uhrzeit_von] und [Uhrzeit_bis]?
"Welche Umsätze (bar, Kreditkarte, Firmenrechnung... wurden im Zeitraum ....gemacht?
"Welche Tische sind noch nicht abgerechnet ("offen")?
"Welchen Umsatz hat Tisch Nr. 123 im Zeitraum ..... gebracht?
"Was wurde unter der Bestellnummer 123456 an welchem Tisch und wann konsumiert, und ist die Bestellung schon abgerechnet?"
"Wie lange hat die Abwicklung der Bestellung 123456 insgesamt gedauert?

In diesem Zusammenhang ist es auch von Interesse, wieviele Bestellungen (Bestellnummern) in einem bestimmten Zeitraum abgewickelt wurden (d.h. wieviele Gäste bzw. Gästegruppen bedient wurden). Es wäre praktisch und bedienerfreundlich, wenn sich diese Auswertung (Dcount über gruppierte Bestellnummern) auch noch in diesem Abfrageformular unterbringen ließe. Aber so wie es im Moment aussieht, ist Access damit rtwas überfordert. Die Funktion DCount an sich wird korrekt ausgeführt und liefert das gewünschte Ergebnis, aber:

- das Textfeld mit der DCount-Funktion muss mit dem Focus "gekitzelt" werden, damit es sich gnädig bereit erklärt, eine Anzeige zu liefern (ansonsten bleibt es "blank") - keine Ahnung, warum....

- In der Abfrage gibt es ein Feld zur Markierung von Datensätzen mit "*" (zur weiteren Filterung in einer anderen Abfrage). Die Reaktion auf eine Eingabe in dieses Feld dauert ca. 2 Sekunden - das ist effektiv zu langsam für eine saubere Bedienung.

Vielleicht wäre doch eine VBA-Prozedur die bessere Lösung - sie braucht nur einmal beim Wechsel der Auswahlparameter aufgerufen werden und würde die Reaktionsgeschwindigkeit bei der Dateneingabe nicht beeinträchtigen. Auch das Anzeigeproblem wäre damit sicherlich gelöst....

MfG Herb

[DF6GL]

Hallo,

nur prizipiell:

Das Datum wird intern als Double-Datentyp geführt, unabhängig von einer Formatierung bei der Anzeige.  Ein numerischer Vergleich geht immer schneller als ein alphanumerischer. Zudem sind solche "unbehandelten" Felder indizierbar, was  bei einer Umwandlungsfunktion nicht der Fall ist.

Um ein Feld mit Datum und Uhrzeit in "reines" Datum zu wandeln, braucht es nur die Anwendung der INT()- oder FIX()-Funktion.


Um Vergleiche in der Where-Condition für ein Datum zu erreichen, braucht lediglich das Kriterium in das ISO- oder USA-Format ( mit der Format-Funktion) gewandelt werden.:

ISO-Format:   #2013-12-31#
USA-Format:  #12/31/2013#


und als String in den SQL-String im Where-Condition-Bereich eingebaut werden. Dabei vergleicht SQL numerisch das Datum und nicht alphanumerisch.



In der Tat bietet sich VBA insofern hier an, als dass es sich um eine reiche Suchfunktionalität handelt. Somit könnte im "Suchformular"  der SQL-String per VBA optimiert an die jeweiligen akt. Kriterien erstellt/zusammengesetzt und dann ausgeführt (der Datenherkunft des Forms zugewiesen) werden.



Vba dient hier lediglich zum zusammenstellen und Ausführen einer Abfrage.  Die Abfrage-Funktionalität ist und bleibt SQL vorbehalten.

Mit der o. g. Vorgehensweise würde (sehr wahrscheinlich) auch das "Kitzeln" wegfallen.

Das "Kitzeln" zu automatisieren  ist möglich mit der Recalc oder evtl. auch mit der Refresh-Methode.


Zudem könnte die langsame DCount-Funktion durch eine schnellere Recordset-Lösung ersetzt werden.

[herb3472]

Herzlichen Dank für Deine umfangreiche Auseinandersetzung mit meinem Thema und für Deine ausführliche Antwort!

Danke für den Tipp bezüglich Datumsformatierung. Mir gehen die vielen unterschiedlichen Datumsformate ohnehin selbst auch schon auf den Wecker - entstanden aus einer nächtlichen "schnell-schnell"-Versuchsversion, und dann nicht mehr ausgebessert.

Beim Zählen der Anzahl unterschiedlicher Bestellnummern hatte ich ursprünglich gedacht, es gäbe vielleicht eine eingebaute Access-Funktion, die mir bisher entgangen war - immerhin ist das ja eine Aufgabenstellung, die doch relativ häufig vorkommt. Werde mir das Thema mit VBA und SQL zu Gemüte führen, mal sehen, was dabei herauskommt.

MfG Herb