Summe in Abfrage erstellen

[Blaupunkt79]

Hallo Zusammen,

wie bilde ich eine Summe in einer Excel VBA Abfrage?

Habe es so versucht, jedoch hängt sich dann Excel komplett auf und es geschieht nichts.

Code [Auswählen] Erweitern

Set DB = DBEngine.OpenDatabase(aktuelle_datei, True, False)
sqlsearch = " SELECT Sum(umsatz) AS Summe " & _
            " FROM umsatz9 " & _
            " where Artikel IN (SELECT Artikel FROM [" & laufwerk & "\artikelstamm.mdb].artikelstamm9 WHERE disponent IN ('101')) "
Set rec_adr = DB.OpenRecordset(sqlsearch)
MsgBox (summe)

"Umsatz" wurde als "Numeric" in der Datenbank deklariert, ist das ok soweit? In der Datenbank stehen in der Spalte "Umsatz" Dezimalzahlen mit Komma.

Danke

Gruß

Mirko

[DF6GL]

Hallo,

Code [Auswählen] Erweitern

in einer Excel VBA Abfrage?
??  Wo läuft denn der Code?

sqlsearch = " SELECT Sum(umsatz) AS UmsatzSumme " & _  'Summe in Umsatzsumme oder Ähnliches umbenennen
            " FROM umsatz9 " & _
            " where Artikel IN (SELECT Artikel FROM [" & laufwerk & "\artikelstamm.mdb].artikelstamm9 WHERE disponent = '101') "   ' wenn disponent aus Datentyp TEXT besteht. Bei LONG die Hochkommata weglassen
Set rec_adr = DB.OpenRecordset(sqlsearch)

MsgBox (summe)

Es muss das Recordset-Feld angezogen werden:

Msgbox rec_adr!UmsatzSumme   oder in diesem Fall:  Msgbox rec_adr(0)

[Blaupunkt79]

So habe jetzt mal den Code fertig gestellt, nur dauert die Ermittlung richtig, richtig lange, das bin ich ja per Hand schneller. Irgendwo muss noch ein gravierender Fehler sein, ich habe in der Beispielumsatzdatenbank mit Absicht auch relativ wenige Daten. Wo genau klemmt es? Der Disponent ist im übrigen ein Textfeld.

Hier mal der VBA Code, in den Filtern werden dann noch die entsprechenden Userabfragen mit implementiert, beispielsweise nur spezielle Kunden oder Branchen oder Umsatztage.

Code [Auswählen] Erweitern

sqlsearch2 = ""
filter(0) = " SELECT Sum(umsatz) AS umsatzsumme " & _
            " FROM umsatz9 as U "
For j = 0 To filteranzahl
sqlsearch2 = sqlsearch2 & filter(j)
Next j

Dim i As Integer
i = 4
Set DB = DBEngine.OpenDatabase(laufwerk & "\artikelstamm.mdb", True, False)
Set rec_adr = DB.OpenRecordset("SELECT DISTINCT Disponent FROM artikelstamm9")
If rec_adr.RecordCount = 0 Then DB.Close: Set DB = Nothing: MsgBox ("Kein Disponenten gefunden!"): Exit Sub
rec_adr.MoveFirst
Do While rec_adr.EOF = False
Set DB2 = DBEngine.OpenDatabase(aktuelle_datei, True, False)
sqlsearch3 = sqlsearch2 & " And u.Artikel IN (SELECT Artikel FROM [" & laufwerk & "\artikelstamm.mdb].artikelstamm9 WHERE disponent = '" & CStr(RTrim(rec_adr!disponent)) & "')"
Set rec_adr2 = DB2.OpenRecordset(sqlsearch3)
Worksheets("BV_final").Range("a" & i) = "Disponent: " & CStr(RTrim(rec_adr!disponent))
Worksheets("BV_final").Range("b" & i) = rec_adr2(0) & " €"
i = i + 1
DB2.Close
Set DB2 = Nothing
rec_adr.MoveNext
Loop

Hier mal eine Beispiel-SQL Abfrage:

Die Abfrage durchläuft dann alle vorhandenden Disponenten, damit ich eine Aufstellung habe, wo welcher Umsatz lief.

Code [Auswählen] Erweitern

SELECT Sum(umsatz) AS umsatzsumme 
FROM umsatz9 as U 
where U.Art IN ('Abholung','Lieferung',"")
AND U.Kunde IN (SELECT kunde FROM [C:\Users\admin\Desktop\BV_Master\System\\kundead.mdb].kundead9 WHERE branche IN ('AMBULANTE PFLEGEDIEN')) 
And U.kunde IN (SELECT kunde FROM [C:\Users\admin\Desktop\BV_Master\System\\kundead.mdb].kundead9 WHERE fachberater IN ('S274'))
And U.Umsatz_Datum IN ('03.11.2021','04.11.2021','05.11.2021')
And u.Artikel IN (SELECT Artikel FROM [C:\Users\admin\Desktop\BV_Master\System\\artikelstamm.mdb].artikelstamm9 WHERE disponent = '101')

Danke

Gruß

Mirko

[ebs17]

Irgendwo muss noch ein gravierender Fehler sein

Man muss schon großer Optimist sein, um an nur einen Fehler zu glauben.

Code [Auswählen] Erweitern

Do While rec_adr.EOF = False
   Set DB2 = DBEngine.OpenDatabase(aktuelle_datei, True, False)
   ' ...
   DB2.Close
   ' ...Stelle Dir vor, Du hast einen Zug, und Du hast 877 Personen zu transportieren.
Dein momentanes Verfahren ist dann vergleichsweise, dass Du pro Person den Zug losschickst und die Person einzeln beförderst, um Dich danach der nächsten Person zu widmen.
Spätestens, wenn Du nicht nur den Spaß bezahlen musst, sondern auch diese Personen mit Dir in Dialog treten, dürfte Dir die Idee kommen, dass man alle mit einmal hätte transportieren können und das wäre billiger gewesen.

Also: EIN Datenbankzugriff und EINE richtige Abfrage über alles mit einer Ergebnismenge über alles. SQL ist für Massendatenverarbeitung konzipiert und kann das gut.

Die nächste Frage wäre jene nach Indexnutzung. Wie findet die bei Dir statt?

Die Abfrageformulierung selber hat auch noch Reserven.
In den (unnötigen) Recordsets finden auch einige Sünden statt, begonnen mit dem Nichtschließen.

[Blaupunkt79]

Das Stichwort "Indexnutzung" war gold wert, hatte bzw. wußte ich vorher nicht. Nun habe ich einige suchrelevante Spalten zum Index hinzugefügt und siehe da, die Auswertung hat ordentlich an PS aufgenommen, in allen belangen. Die besagte Umsatzsumme habe ich nun innerhalb einiger weniger Sekunden.

Werde jetzt noch ein paar Textspalten in Zahlen umwandeln, bringt wohl auch noch einiges an Nutzen.

Was ich als nächstes noch vor habe, sind die Top 10 Artikel je Disponent anzeigen zu lassen nach Umsatz bzw. die Flop 10 Artikel je Disponent nach Bestandsveränderungen. Da war ja das Problem, dass eine Sortierung via ORDER BY nicht funktioniert. Wie kann ich das Problem umgehen?

Code [Auswählen] Erweitern

Set DB = DBEngine.OpenDatabase(aktuelle_datei, True, False)
sqlsearch = " SELECT * " & _
            " FROM umsatz9 " & _
            " where Artikel IN (SELECT Artikel FROM [" & laufwerk & "\artikelstamm.mdb].artikelstamm9 WHERE disponent = '101') " & _
            " Order BY umsatz "
Set rec_adr = DB.OpenRecordset(sqlsearch)

Um bei dem Zugbeispiel zu bleiben, Du bist der Lokführer, mit wahnsinniger Erfahrung und ich bin gerade hinten als blinder Passagier zugestiegen, ich kämpfe mich nach vorne und versuche, das Ticket zu lösen. :-)

Danke

Gruß

Mirko

[ebs17]

Da war ja das Problem, dass eine Sortierung via ORDER BY nicht funktioniert.

Echt? Wenn Sortieren nicht funktioniert, macht man etwas falsch, oder man kann seine Anwendung/Installation gleich wegwerfen.

Wie kann ich das Problem umgehen?

...?

Falls Du auf einen Abfragevorschlag hoffst: Dazu vergegenwärtige ich mir zuvor die Tabellenstrukturen und -zusammenhänge, also das, was man landläufig in einem Datenmodell finden würde. Das, um den Weg zu identifizieren, möglichst einfach, direkt und performant ein Abfrageergebnis zu erstellen.

Prinziplösung:

Code [Auswählen] Erweitern

SELECT
   T.*
FROM
   TabelleX AS T
WHERE
   T.ID IN
      (
         SELECT
            X.ID
         FROM
            TabelleX
         WHERE
            X.Merkmal = T.Merkmal
         ORDER BY
            X.Wert DESC
      )