PDA

Vollständige Version anzeigen : Sortieren und Berechnen von Feldern nach Kriterien


Torsten S.
23.01.2008, 14:38
Folgende Frage:
Ich habe die Tabelle Auftrag mit nachfolgenden Feldern
Feld PosNr
Feld Absender Text
Feld Empfänger Text
Feld Rechnungsempf Text
Feld Kezi1 Text
Feld Kezi2 Text
Feld Kezi3 Text
Feld Kezi4 Text
Feld Keziw1 Zahl
Feld Keziw2 Zahl
Feld Keziw3 Zahl
Feld Keziw4 Zahl

Im Formular "Auftragsbearbeitung" gebe ich die Daten ein

Absender Meier
Empfänger Müller
Rechnungsempf Schmidt

Kezi1 VA Keziw1 50,00
Kezi2 Maut Keziw2 5,00
Kezi3 DZ Keziw3 2,00
Kezi4 Maut Keziw4 6,00

Mit klick auf Button "Berechnen" wird ein Formular "Berechnung" geöffnet, wo folgende Berechnung stattfinden soll:

VA 50,00
DZ 2% 1,00
Maut 5,00
Maut 6,00
Summe 62,00

Oder wenn statt "DZ" der Eintrag "97" ist:

VA 50,00
Maut 5,00
Maut 6,00
97 2% 1,20
Summe 62,20

Das Feld Kezi bestimmt immer, was genau mit dem Feld Keziw gemacht werden soll
z.B. Kezi VA ist Grundbetrag
Kezi Maut ist Zuschlag (fester Betrag)
Kezi 97 ist Zuschlag in % auf den Rechnungsbetrag "Netto"
Kezi 95 ist Zuschlag auf Grundbetrag

Ist es möglich, dies so in einer VBA-Funktion zu berechnen?

Im voraus besten Dank für eine Antwort

Gruss Torsten

Anne Berg
23.01.2008, 16:17
Hast du jetzt Kezi "DZ" mit "95" verwechselt? :confused:

Grundsätzlich ist davon abzuraten, mehrfache Felddefinitionen in einem Datensatz zu machen. Was ist, wenn noch weitere Spalten hinzukommen? Dann kannst du alle Abfragen, Formulare und Berichte ändern!

Die Berechnung könnte in einer Abfrage erfolgen, da du aber nicht weißt, in welcher Spalte der Prozentsatz steht, resultiert das in endloser Abfragerei (Wenn kezi1 = "DZ", dann rechne dies, sonst: Wenn Kezi1 = "97", dann rechne das, sonst: Wenn Kezi2 = "DZ, dann ...") wovon man nur abraten kann.
Überlege mal, ob du deine Tabellenstruktur nicht erst "normalisieren" willst, ehe du weiter damit arbeitest.

Torsten S.
23.01.2008, 16:55
Hallo Anne,

zur Berechnungsgrundlage will oder kann ich entweder eigene Daten eingeben oder feste Vereinbarungen (Tarife) mit Kunden als Berechnungsgrundlage nehmen. Die Felder Kezi1 - Kezi4 /Keziw1-keziw2 in der Auftragstabelle sind eigentlich fest. Kann man nicht für die verschiedenen Berechnungsarten jeweils ein Modul anlegen?

Wie kann man denn sonst die Tabelle so aufteilen, dass ich zu dem gewünschten Ergebnis kommen könnte?

Danke für deine Hilfe :)

Gruss Torsten

Anne Berg
23.01.2008, 17:04
Kannst du denn wenigstens festlegen, in welcher Spalte der Prozentsatz eingetragen wird?
Der Inhalt hat ja wohl auch Einfluss auf die Anzeigereihenfolge...

Torsten S.
31.01.2008, 16:21
Hallo Anne,

Entschuldigung für die lange Pause,
im Anhang habe ich eine Excel-Datei angefügt, wo ein Bespiel für die Berechnung ist. Die während der Auftragserfassung eingegebenen Berechnungsdaten könnten auch in eine 2. Tabelle (in Verbindung mit Auftragsnummer) gespeichert werden.

Im voraus Dank für deine Hilfe

Gruss Torsten

Anne Berg
31.01.2008, 20:54
Du brauchst dich nicht zu entschuldigen, Zeit spielt keine Rolle. Ich fürchte nur, mir gehen angesichts der Excel-Tabelle die Ideen aus. Ganz gewiss musst du da gewaltig umdenken.

Ich würde vorschlagen, die einzelnen Rechnungsposten in der gewünschten Reihenfolge als Rechnungspositionen zeilenweise, also in separaten Datensätzen, zu erfassen?

Torsten S.
31.01.2008, 22:46
Hallo,

wenn ich nun die Rechnunungsposten jeweils in einen Datensatz speichere, kann ich ja mittels Abfrage die Summe errechnen, wie rechne ich aber dann z.B. einen prozentualen Zuschlag auf die Summe, welcher auch in den Rechnungsposten enthalten ist:

Datensatz 1 100,00
Datensatz 2 50,00
Datensatz 3 60,00
Datensatz 4 4,00 (%)

?Summe (Abfrage) 210,00 * 4/100

Oder mache ich das in einem Formular mit Hilfe einer Funktion und Sql?

Torsten S.
19.02.2008, 17:26
Hallo Anne,

ich hab's jetzt so gemacht:

Daten für die Berechnungsgrundlage wird in eine extra Tabelle (Unterformular)
eingetragen bzw. gespeichert.
Die eigentliche Berechnung (wird nicht gespeichert) führe ich im Hauptformular
bzw. Bericht mit zusätzlichen Textfeldern für jede eingetrage
Berechnungsgrundlage durch.

Jetzt habe ich aber ein anderes Problem,

wie kann ich mehrere Datensätze aus z.B. Formular1 per Befehlsschaltfäche
in Formular2----Unterformular---- anfügen?

Bisher habe ich nur 1 Datensatz aus Formular1 in Formular2 eingefügt und bei
einem Unterformular in Formular2 mit mehreren Datensätzen funktioniert das nicht:

Private Sub Befehl14_Click()
DoCmd.OpenForm "Formular2"
Forms!Formular2!textfeld1 = Me.textfeldx
Forms!Formular2!textfeld2 = Me.textfeldy
DoCmd.Close acForm, "formular1", _
acSaveYes
End Sub


Gruss Torsten

Anne Berg
19.02.2008, 18:46
Hallo Torsten.
Wenn Formular2 an eine Tabelle gebunden ist, kannst du zum neuen Datensatz springen.

Andernfalls musst du mal nach "Endlosformular ungebunden" suchen, da gibt es wohl eine Lösung mit einem ADO-Recordset. Das habe ich aber noch nie so gebraucht.

Torsten S.
20.02.2008, 16:19
Hallo Anne,

Formular2 = Auftrag ist an eine Tabelle gebunden und das Unterformular =
Auftragberechnung ist an eine Tabelle gebunden. Verbindung besteht über die
Auftragsnummer.

Folgendes bekomme ich nicht hin:

Wenn ich im Uf Auftragsberechnung Daten eingebe, wird automatisch für jeden
Datensatz die gleiche Auftragsnummer aus dem Hauptformular eingetragen.

Ich möchte nun aus einem 2. Formular = Vereinbarung, mehrere Datensätze in
das Uf Auftragsberechnung mittels VB einfügen.

Hier entstehen nun zwei Probleme:

1. Ich bekomme den Focus nicht auf das Unterformular gesetzt

2. Bekomme ich nicht die gleiche Auftragsnummer wie im Hauptformular hin
:(

Gruss Torsten

Anne Berg
20.02.2008, 16:26
Zeig doch mal deinen Code, dann haben wir eine gemeinsame Grundlage.

ebs17
20.02.2008, 16:59
Ich möchte nun aus einem 2. Formular = Vereinbarung, mehrere Datensätze in
das Uf Auftragsberechnung mittels VB einfügen.

Dem Formular 1 liegt eine andere Tabelle zugrunde als dem Unterformular? Datensätze ausschneiden oder kopieren (Redundanz?)?

Das lässt sich mit einer Anfügeabfrage realisieren, wobei der Hauptdatensatz (-> Auftragsnummer) schon vorher bestehen muss.

Torsten S.
20.02.2008, 22:43
Hallo Anne und Eberhard,

im Anhang ist eine Testmdb, Problem ist halt, die Daten in das Unterformular zu übernehmen!

Gruss Torsten

Anne Berg
20.02.2008, 23:41
Hallo Torsten,

eine mögliche Lösung wäre, die Datensätze aus dem Formular mit einer Anfügeabfrage zu übernehmen.
Dazu habe ich im Form "Vereinbarung" eine Schaltfläche mit folgender Ereignisprozedur eingefügt:Private Sub Befehl5_Click()
On Error GoTo Err_Befehl5_Click

DoCmd.RunSQL "Insert Into tbl_fra (fr_sort,fr_ktext,fr_kwert) " & _
"SELECT tbl_off.of_sort, tbl_off.of_kz, tbl_off.of_kzw " & _
"FROM tbl_offn LEFT JOIN tbl_off ON tbl_offn.ofn_id = tbl_off.of_ofna " & _
"WHERE (((tbl_offn.ofn_abs) = [Formulare]![auftrag]![af_amc]) And " & _
"((tbl_offn.ofn_empf) = [Formulare]![auftrag]![af_emc]) And " & _
"((tbl_offn.ofn_frz) = [Formulare]![auftrag]![af_rid]) And " & _
"((tbl_off.of_ldm) = [Formulare]![auftrag]![af_ldm])) " & _
"ORDER BY tbl_off.of_sort, tbl_off.of_kz;" ', dbFailOnError

Exit_Befehl5_Click:
Exit Sub
Err_Befehl5_Click:
MsgBox Err.Description
Resume Exit_Befehl5_Click
End Sub

Den Code für die Abfrage habe ich der Datenherkunft des Formulars entnommen.

Ich hoffe, das hilft dir weiter.

ebs17
21.02.2008, 00:16
Ergänzung:
Für die Verknüpfung HFo-UFo müsste noch zusätzlich
[Formulare]![auftrag]![af_sn] an das Feld fr_sendnr
übergeben werden.

Anne Berg
21.02.2008, 08:36
Danke, Eberhard - hatte ich doch das Wichtigste glatt vergessen! :rolleyes:

Torsten S.
21.02.2008, 08:48
Hallo Anne und Eberhard,

danke für eure prompte Antwort :) .

Die Ergänzung von Eberhard ist jetzt noch das Problem, wie kann man die
Verknüpfung HFo-UFo [Formulare]![auftrag]![af_sn] --> fr_sendnr
in den Sql-Befehl einbauen, damit die Nummer aus dem HFo Feld af_sn in die Tabelle tbl_fra mit eingetragen wird?

Gruss Torsten

Anne Berg
21.02.2008, 09:11
o sollte es gehen:DoCmd.RunSQL "Insert Into tbl_fra (fr_sendnr, fr_sort,fr_ktext,fr_kwert) " & _
"SELECT [Forms]![auftrag]![af_sn], tbl_off.of_sort, tbl_off.of_kz, tbl_off.of_kzw " & _
...

Torsten S.
21.02.2008, 13:04
Hallo Anne,

das genau ist es, vielen Dank für Deine und Eberhards Hilfe.

Es wird wahrscheinlich nicht die letzte Frage gewesen sein, da ich mit der
gesamten Anwendung noch lange nicht fertig bin ;) .


Gruss Torsten

Anne Berg
21.02.2008, 13:13
...o sollte es gehen!

(wollte eben nur das verloren gegangene S nachreichen. ;))

Und viel Erfolg noch bei der Arbeit!

Torsten S.
21.05.2008, 15:24
Hallo,

lang ist's her...

Folgendes, ich habe jetzt zwei Tabellen
tbl_at
tbl_fra

und zwei Formulare
frm_at mit Unterformular uf_fra
frm_berechnung

Wenn ich im Formular frm_at auf den Button "Berechnung" klicke, soll
im Formular frm_berechnung alle Datensätze aus der Tabelle tbl_fra
eingetragen werden.
Das Problem ist jetzt
1. Ich bekomme die SQL-Abfrage mit "where" nicht hin,
Kriterium ist in frm_at das Feld af_sn und bezieht sich auf das Feld
fr_sendnr der Tabelle tbl_fra, jeweils ein Textfeld
2. Max. können 9 Datensätze im Formular frm_berechnung eingetragen
werden, sind es aber weniger, erscheint eine Fehlermeldung, da z.B. nur 3
Datensätze vorhanden sind (frm_berechnung dient zur Endkontrolle der
Berechnung)

Im Anhang befindet sich die Musterdatenbank.

Im voraus besten Dank für eine Antwort

Gruss Torsten

Anne Berg
21.05.2008, 15:35
Hallo, so sollte das SQL-Statement mit Kriterium aussehen: strSQL = "select * from tbl_fra " & _
"WHERE (((tbl_fra.fr_sendnr)='" & Forms!frm_at!af_sn & "')) " & _
"ORDER BY tbl_fra.fr_sendnr, tbl_fra.fr_sort;"
Und so klappt's auch mit variabler Datensatzanzahl:Private Sub Form_Current()
Dim i
'sendnr = Nz(Me!Forms!frm_at.af_sn.Value, "")
' Bezug auf aktuelle Datenbank zurückgeben.
Set db = CurrentDb
' SQL-Zeichenfolge erstellen.
strSQL = "select * from tbl_fra " & _
"WHERE (((tbl_fra.fr_sendnr)='" & Forms!frm_at!af_sn & "')) " & _
"ORDER BY tbl_fra.fr_sendnr, tbl_fra.fr_sort;"
'Recordset-Objekt vom Typ Snapshot öffnen.
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
rst.MoveFirst
i = 0
Do While Not rst.EOF
i = i + 1
Me("txtkezi" & i) = rst("fr_ktext")
Me("txtschluessel" & i) = rst("fr_kpra")
Me("txtwert" & i) = rst("fr_kwert")
rst.MoveNext
Loop
rst.Close
Set db = Nothing
Set rst = Nothing
End Sub

Torsten S.
21.05.2008, 16:07
Hallo Anne,

nachfolgender Fehler erscheint bei öffnen von Formular
frm_berechnung:

Laufzeitfehler '3464"
Datentypen in Kriterienausdruck unverträglich


Im Modul wird folgende Zeile markiert:

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)


Wenn ich im SQL-Befehl den Code mit "Where" weglasse,
funktioniert es, hat das vielleicht irgendetwas mit
dem Textfeld als Kriterium zu tun?


Gruss Torsten

Josef P.
21.05.2008, 16:11
Welchen Datentyp hat das Tabellenfeld "fr_sendnr"?

Torsten S.
21.05.2008, 16:29
Hallo,

nachfolgend die Details zu den 2 Feldern:


Tabelle tbl_at
Feld af_sn

Felddatentyp = Text
Feldgroesse = 10
Beschriftung = Sendnr
Eingabe erforderlich = Ja
Leere Zeichenfolge = Nein
Indiziert = Ja (Ohne Duplikate)

Tabelle tbl_fra
Feld fr_sendnr

Felddatentyp = Text
Feldgroesse = 10
Beschriftung =
Eingabe erforderlich = Nein
Leere Zeichenfolge = Nein
Indiziert = Nein

Josef P.
21.05.2008, 16:32
Wie sieht die SQL-Anweisung aus?
Ist möglicherweise im Feld Forms!frm_at!af_sn ein ' enthalten?

Anne Berg
21.05.2008, 16:46
fr_sendnr ist vom Typ Text, da hat jemand wohl meine nachträgliche Korrektur übersehen... ;)

Torsten S.
21.05.2008, 16:47
Hallo,

nachfolgend der Code, im Anhang ist aber auch nochmal
die Datenbank

Gruss Torsten

Private Sub Form_Current()

Dim db As DAO.Database
Dim rst As Recordset
Dim strSQL As String
Dim i As String
Set db = CurrentDb
' SQL-Zeichenfolge erstellen.
strSQL = "select * from tbl_fra where (((tbl_fra.fr_sendnr= " & [Forms]![frm_at]![af_sn] & ")) ORDER BY tbl_fra.fr_sendnr, tbl_fra.fr_sort;"

'WHERE (((tbl_fra.fr_sendnr)= " & Forms!frm_at!af_sn & _
"))

'Recordset-Objekt vom Typ Snapshot öffnen.
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
rst.MoveFirst
i = 0
Do While Not rst.EOF
i = i + 1
Me("txtkezi" & i) = rst("fr_ktext")
Me("txtschluessel" & i) = rst("fr_kpra")
Me("txtwert" & i) = rst("fr_kwert")
rst.MoveNext
Loop

rst.Close
Set db = Nothing
Set rst = Nothing


End Sub

Anne Berg
21.05.2008, 16:50
das war jetzt wirklich überflüssig :rolleyes:

Torsten S.
21.05.2008, 20:22
Hallo,

Entschuldigung für die späte Rückmeldung, kein Internetzugang mehr.

Also der Tipp von Anne war richtig, die ' fehlten, jetzt funktioniert es.

Danke für eure Hilfe

Gruss Torsten

Torsten S.
27.05.2008, 12:09
Hallo,

wenn ich nachfolgende Function ausführe, muss ich jeden Datensatz
einzeln Bestätigen, kann man das auch Automatisieren?

Gruss Torsten



'------------------------------------------------------------
' Ergebnisse aus Formular Berechnung in Tabelle tbl_fra speichern
'
'-----------------------------------------------------------------
Function updatetblfraber()
On Error GoTo updatetblfraber_Err

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber1] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi1]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber2] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi2]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber3] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi3]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag " _
& "= [Formulare]![frm_berechnung]![txtber4] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi4]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber5] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi5]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber6] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi6]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber7] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi7]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber8] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi8]));"

DoCmd.RunSQL "UPDATE tbl_fra SET tbl_fra.fr_betrag = " _
& "[Formulare]![frm_berechnung]![txtber9] " _
& "WHERE (((tbl_fra.fr_sendnr)=[Formulare]![frm_at]![af_sn]) " _
& "AND ((tbl_fra.fr_ktext)=[Formulare]![frm_berechnung]![txtkezi8]));"



updatetblfraber_Exit:
Exit Function

updatetblfraber_Err:
MsgBox Error$
Resume updatetblfraber_Exit

End Function

Anne Berg
27.05.2008, 12:23
Mit CurrentDB.Execute kannst du den Code ebenfalls ausführen, ohne Bestätigung.

Oder du setzt an den Anfang ein "DoCmd.SetWarnings False" und ans Ende "DoCmd.SetWarnings True".

Torsten S.
27.05.2008, 13:00
Hallo,

danke für die schnelle Antwort, ich habe die 2. Variante genommen

Gruss Torsten