Hallo zusammen,
ich habe eine Tabelle mit Bestellungen. In dieser Tabelle gibt es eine Spalte mit einem jeweiligen Datum im kurzen Format. Ich möchte nun gerne den Mittelwert der Differenz der jeweiligen Terminen ermitteln. DatDiff funktioniert ja nur mit zwei Werten.
Beispiel:
12.01.2013
Diff: 3
15.01.2013
Diff: 5
20.01.2013
Mittelwert: 3 + 5 = 8 / 2 = 4
Vier wäre also mein gesuchter Wert.
Löse ich das über eine SQL-Abfrage oder über VBA? Leider reichen meine Kenntnisse offensichtlich nicht aus.
Freue mich über jeden Tipp! Hab im Netz nur ansatzweise etwas finden können.
Vielen Dank für jede Unterstützung!
Uli
ZitatIn dieser Tabelle gibt es eine Spalte mit einem jeweiligen Datum
Gibt es da noch weitere brauchbare Spalten (eindeutige ID) bzw. ist sichergestellt, dass es pro Datum höchstens eine Bestellung gibt?
MfGA
ebs
Ja! Das ist sichergestellt, es gibt eine ID! Viele Grüße!
SELECT
T1.BDatum,
Avg(T1.BDatum -
(
SELECT
Max(T2.BDatum)
FROM
tblBestellung AS T2
WHERE
T2.BDatum < T1.BDatum
)
) AS D
FROM
tblBestellung AS T1
GROUP BY
T1.BDatum
MfGA
ebs
Hallo ebs17! Herzlichen Dank! Die Abfrage bringt die Differenzen zum jeweiligen nächsten Datum. Ich werde morgen versuchen, das Ganze in eine VBA Prozedur zu schreiben, die dann die Spalte D - also die Summe der Differenzen - durch die Anzahl teilt. Dankeschön!
Hallo Ebs17! Hallo Forum-Mitglieder!
Tja, da war ich wohl zu optimistisch. Ich schaffe es einfach nicht, das Ergebnis aus Deiner wunderbaren SQL-Abfrage
in ein Formular in einem Textfeld zu übertragen...Ich habe im folgenden Code ein paar Ansätze. Kriege aber immer neue Fehlermeldungen und komme nicht weiter. Mit fehlt der Weg, das Ergebnis der Abfrage Spalte D als Variable zu speichern und
die Tabelle tblBestellungen anhand des Fremdschlüssels des jeweiligen Kunden aus dem Hauptformular zu filtern. Also das der Mittelwert mitgeht.
Es wäre super, wenn Du Ebs17 oder ein anderes Forumsmitglied, mir nochmals helfen würde.
Kleinlaute Grüße ;)Uli
Sub Avg()
Dim conn As New ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.Open "SELECT Sum(T1.BDatum) AS Ausdr1, Avg(T1.BDatum-(SELECT"
Max (T2.BDatum) * FROM tblBestellung As T2" _
& "WHERE T2.BDatum < T1.BDatum )) AS D, FROM tblBestellung AS T1",
conn , adopenKeyset, adLockOptimistic
D = Me!txt_avg.Value
Set conn = Nothing
Set rest = Nothing
End Sub
Hallo, ich habe recherchiert und bin auf die Anweisung CurrentDb.OpenRecordset("str")(1) gekommen.
Damit ist es wohl möglich, einen Wert aus der angewiesenen, geöffneten Abfrage zu erhalten. Die zweite Klammer
gibt die Stelle an, worauf man zugreift.
Leider funzt die Abfrage unter VBA nicht, so dass ich vorher Fehler bekomme. Auch eine Neuaufbau der Tabellen hat kein Erfolg gebracht.
....Woran liegt es?? Probiere nun schon gefühlt ewig und
freue micht über jede Unterstützung. Danke!
Private Sub Befehl575_Click()
Dim Wert As Integer
Dim str As String
str = "SELECT Sum ( tbl_Bestellungen.bes_Auftraege ) AS Summevonbes_Auftraege " & _
"Avg( [tbl_Bestellungen].[bes_Auftraege]-( SELECT Max (T2.bes_Auftraege ) " & _
"FROM tbl_Bestellungen AS T2 " & _
"WHERE T2.bes_Auftraege < tbl_Bestellungen.bes_Auftraege)) AS D " & _
"FROM tbl_Bestellungen "
'Folgende Anweisung müsste noch in die WHERE Answeisung, damit der Wert
'im Formular mitläuft.
'tbl_Bestellungen.Kunde_id_f = " & Me!Kunde_id
DoCmd.RunSQL str
Wert = CurrentDb.OpenRecordset("str")(1)
MsgBox Wert
End Sub
Hallo,
irgendwie bist Du vollkommen auf dem Holzweg...
Kopier den von ebs17 geposteten SQL-String im Abfrageentwurf in das SQL-Ansicht-Fenster und führ die Abfrage. Wenn die angegebenen Namen der Tabellen und Felder stimmen, erhältst Du das, was anfänglich gefragt war.
Wie stellest Du Dir nun die Anzeige der (vielen) Werte in einem Textfeld vor?
Besser zur Darstellung wäre z. B. ein Listenfeld (oder auch ein Endlos-(Unter-)Form), dem einfach der SQL-String an dessen Rowsource- (bzw. Recordesource-) Eigenschaft zugewiesen wird...
Das wird auch dann funktionieren, wenn das zusätzliche Kriterium (Berechnungen bezogen auf einen bestimmten Kunden) hinzukommt:
Me!lstFeld1.Rowsource ="SELECT T1.BDatum, Avg(T1.BDatum - (SELECT Max(T2.BDatum) FROM tblBestellung AS T2 WHERE T2.BDatum < T1.BDatum ) AS D FROM tblBestellung AS T1 Where T1.Kunde_id_f = " & Me![Kunde_id] & " GROUP BY T1.BDatum"
Hallo Franz! Vielen herzlichen Dank für Deine Antwort!
Ich klopfe tatsächlich auf Holz, aber noch bringt es nichts:)
Eigentlich dachte ich auch, dass das so funktioniert, wie Du beschrieben hast! Die Abfrage funktioniert bei mir im Abfragefenster spitze, wie gewünscht. Da ich im ersten Wert eine Summe bilde, bekomme ich in einer Zeile mit zwei Spalten auch das, was ich haben möchte. Der zweite Wert ist der gewünschte Durchschnitt.
Nehme ich jetzt die Abfrage und füge sie so ein, wei Du es beschreibst, bekomme ich egal im welchen Formular #Name. Woran könnte das liegen? Viele Grüße!!!
Das Listenfeld hat eine RowSource, ein Formular eine RecordSource.
Zusätzlich sollte man beim Listenfeld auf eine passende Anzahl an (sichtbaren) Spalten achten, beim Formular, dass zusätzlich auch die Textfelder an die Abfragefelder gebunden sind (ControlSource).
Etwas Grundlagenstudium wäre ganz hilfreich.
Dankeschön Ebs17, Dankeschön Franz!
--für das Feedback und Lösungen! Gerade weil ich mich offensichtlich dusselig anstelle. Dachte auch nicht, das
ich da auf derlei Probleme stoße. Ich habe nun viel Zeit in eine Lösung gesetzt und es nicht abschließend
vollbracht. Da ich Feeback in einem Forum u.a. als das Wichtigste ansehe, beschreibe ich Euch meinen Stand.
- Das Listenfeld zeigt nun die Daten der Abfrage an. Das klappt! Allerdings habe
ich es nicht geschafft, den Inhalt auf die Kunden_Id des Hauptformulares
bzw. auf die Kunden_Id_f des Unterformulares einzugrenzen.
- Der Abfragegenerator hängt ein "Having"an die Where-Klausel, wenn ich bei Kunden_Id_F
eine ID angebe. Eine Variable aus dem Formular zieht er nicht, habe es mit Me!Kunden_ID und Forms! probiert.
Auch weiss ich nicht, ob die Daten aus dem Listenfeld für Berechnungen weiter verwendet werden können.
- Probleme machen dann zudem die anderen berechneten Felder im Hauptformular, die auch auf die Bestellungen, also
auf die gleiche Tabelle zugreifen, wie die Mittelwertsabfrage. Diese sind plötzlich leer, sobald ich
das Listenfeld in das Hauptformular oder in den Kopf einfüge.
- Ich habe als zweiten Weg ein neues Formular mir dem Kunden als Hauptformular und der Abfrage als Unterformular
erstellt. Das funktioniert. In einer Textbox die Summe von D durch die Anzahl von D berechnet. So wie ich es benötige.
Das funktioniert auch und es wird der richtige Wert angezeigt. Übertrage ich das Unterformular in mein anderes Hauptformlar, so
ist nun das berechnete Feld leer, obwohl die erforderlichen Felder eingebunden sind und Access nicht
meckert. Die anderen, berechneten Felder im Hauptformular, berechnen sich zudem nicht neu, wenn ich den Kunden wechsle und zurückgehe.
Gleiches Phänomen wie beim Listenfeld.
- Für mich nicht ganz nachvollziehbar ist, dass das neue Formular, also das mit der Abfrage, ebenfalls
keine Summe mehr anzeigt, wenn das andere, bestehende Formular als erstes geöffnet wurde. Andersrum kein Problem.
Offensichtlich blockiert das eine Formular die Berechnung des Summenfeldes in der Fußleiste.
Viele Grüße! U.