Hallo allerseits, ich habe den ganzen Nachmittag Foren durchstöbert und alles Probiert, dennoch kein Erfolg. Ich bin deutlicher Anfänger und mit VBA kann ich nicht viel anfangen.
Ich hoffe einer kann mir Helfen.
Ich habe eine Tabelle [Auftritte] von der ich ausgehend von eingetragenen Fahrt-Kilometern, Fahrtkosten errechne und automatisch eintrage(via Aktualisierungs Abfrage). Den Aufwand betreibe ich, da sich der Km-Satz mit der Zeit ändern wird. Bisher nehme ich den [Kilometer_Betrag] aus der Tabelle [Kilometergeld] mit DMax([Kilometergeld_ID]) und berechne nur wenn zuvor noch keine Fahrkosten eingetragen sind.
Nun der Plan:
Mit DomWert soll aus der Tabelle [Kilometergeld] die einen [Kilometer_Betrag] mit einem Datumsbereich [Dat_von] , [Dat_bis] enthält der richtige Betrag rausgesucht werden. Im Kriterium soll dann "[Kilometergeld]![Dat_von]<[Auftritte]![Auftritt_Dat]<[Kilometergeld]![Dat_von]" stehen.
Ich habe ebenfalls erfolglos mit ZDouble experimentiert.
Danke für die Hilfe
Hi,
ZitatIch hoffe einer kann mir Helfen.
falls du wider Erwarten auch weibliche Mitstreiter akzeptierst, könntest du evtl. die folgende Frage beantworten:
Was genau möchtest du mit dem folgenden Kriterium bewirken:
[Kilometergeld]![Dat_von]<[Auftritte]![Auftritt_Dat]<[Kilometergeld]![Dat_von]Kannst du das bitte mit Worten beschreiben?
Und zeig uns bitte dazu auch noch den vollständigen SQL-Code der Abfrage.
Ich meinte damit natürlich auch "eine" ;-
Das ganze ist ein Kassensystem für unsere Band.
UA. werden Auftritte eingepflegt die an einem bestimmten Auftrittsdatum statt gefunden haben. Und dafür werden Fahrtkosten für die Mitspieler ausbezahlt. Da sich die Kilometerpauschale ändert/ändern kann berechne ich das Sprittgeld aus Kilometern und füge das per Aktualisierungsabfrage ein. Damit nicht im Nachhinein bei Neuberechnung der falsche Kilometersatz verwendet wird. Gibt es eine Tabelle Kilometergeld mit einem Betrag und einem Gültigkeitszeitraum.
D.h. für die Berechnung soll mir DomWert den Korrekten Kilometersatz durch Vergleich mit dem Auftrittsdatum rausgeben.
Da ich nicht in SQL direkt arbeite (Neuling) und meine Syntax falsch ist musste ich etwas tricksen. Ich habe zum Kopieren den Aktualisieren Wert wie ich Ihn mir vorstelle in den SQL-Code eingefügt.
UPDATE Auftritte_Ber, Kilometergeld SET Auftritte_Ber.Fahrtkosten = ([Auftritte_Ber]![Kilometer]*( DOMWERT("[Kilometer_Betrag]";"[Kilometergeld]; "[Kilometergeld]![Dat_von]<[Auftritte]![Auftritt_Dat]<[Kilometergeld]![Dat_von]")))
WHERE (((Auftritte_Ber.Fahrtkosten) Is Null) AND (([Auftritte_Ber]![Auftritt_Kerndaten_ID])=[Formulare]![Auftritte]![Auftritt_Kerndaten_ID]));
Vielen Dank
Hallo,
(Luftcode, Korrekturen erbeten)
UPDATE Auftritte_Ber AS AB
SET AB.Fahrtkosten = AB!Kilometer *
(SELECT K.Kilometer_Betrag
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN K.Dat_von AND K.Dat_bis)
WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID =
Forms!Auftritte!Auftritt_Kerndaten_ID
gruss ekkehard
Hi,
ZitatD.h. für die Berechnung soll mir DomWert den Korrekten Kilometersatz durch Vergleich mit dem Auftrittsdatum rausgeben.
Sorry, aber das erklärt nicht, was du dir bei dem Ausdruck
[Kilometergeld]![Dat_von]<[Auftritte]![Auftritt_Dat]<[Kilometergeld]![Dat_von]gedacht hast.
Oder willst du tatsächlich mit einem Zeitbereich (von-bis) vergleichen, wie von ekkehard vermutet?
Danke euch beiden!
Ja ich möchte mit einem Zeitbereich vergleichen. Das Kilometergeld ist in einem Bereich gültig und wenn der Auftritt in dem Bereich liegt soll der entsprechende Betrag verwendet werden.
Habe den Code eben in die SQL-Ansicht kopiert, da kam Syntaxfehler.
Ich habe keine Ahnung wo der zu suchen ist.
BTW das Von_Datum ist theoretisch auch redundant, es könnte auch das vorherige Bis_Datum +1 Tag sein. Macht es aber nur noch komplizierter.
Hallo,
ZitatHabe den Code eben in die SQL-Ansicht kopiert, da kam Syntaxfehler.
Vielleicht postest du den, und auch deinen SQL-Versuch.
Wahrscheinlich ist das Formular einfach nicht geöffnet. Das ist in diesem
Zusammenhang Voraussetzung.
Zitatdas Von_Datum ist theoretisch auch redundant, es könnte auch das vorherige Bis_Datum +1 Tag sein.
Das müsste dann so gehen.
UPDATE Auftritte_Ber AS AB
SET AB.Fahrtkosten = AB!Kilometer *
(SELECT K.Kilometer_Betrag,
(MAX(K.Dat_bis) +1) AS DVon
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN DVon AND K.Dat_bis)
WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID =
Forms!Auftritte!Auftritt_Kerndaten_ID@Regulars
Bitte, bitte korrigiert das. Ich habe das nicht getestet, sondern echt so aus
der hohlen Hand hingeschrieben, auch ohne Kenntnis der Daten.
Wenn das so richtig ist, bin ich stolz auf mich. Ansonsten bin ich ja lernfähig.
gruss ekkehard
Hallo,
an
Zitat... = AB.Kilometer *
(SELECT K.Kilometer_Betrag,
(MAX(K.Dat_bis) +1) AS DVon
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN DVon AND K.Dat_bis)...
mußt Du noch arbeiten, weil die Unterabfrage mehr als einen Wert in der Selectliste aufweist und zudem mehr als einen DS (Wert) liefern könnte.
Zudem:
SET AB.Fahrtkosten = AB.Kilometer *
Zitat von: Beaker s.a. am Februar 23, 2017, 18:15:23
Hallo,
ZitatHabe den Code eben in die SQL-Ansicht kopiert, da kam Syntaxfehler.
Vielleicht postest du den, und auch deinen SQL-Versuch.
/quote]
Ich habe den von Ihnen oben einfach in die SQL Ansicht kopiert.
Entschuldigt bitte die Formatierung... ich habs bisher weder geschafft ein Code Feld zu verwenden noch anscheinend korrekt zu zitieren.
Das mit der Redundanz ist auch erstmal nicht so wichtig, so lange das andere funktioniert.
Hätte ich den Code von Ekkehard nicht einfach in die SQL Ansicht einer Abfrage kopieren sollen? Wegen dem ID Vergleich mit dem Formular bekomme ich doch nur eine Parameterabfrage wenn ich die Anfrage ausführe... und das Formular nicht geöffnet ist, oder.
Vielen Dank
David
Hallo Franz,
Danke.
Zitatweil die Unterabfrage mehr als einen Wert in der Selectliste aufweist
Klar, logisch, wird ja also Faktor verwendet.
Also erstmal wie zuerst (mit Korrektur)
UPDATE Auftritte_Ber AS AB
SET AB.Fahrtkosten = AB.Kilometer *
(SELECT K.Kilometer_Betrag
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN K.Dat_von AND K.Dat_bis)
WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID =
Forms!Auftritte!Auftritt_Kerndaten_ID
Zitatzudem mehr als einen DS (Wert) liefern könnte.
Daran hatte ich auch gedacht, aber ohne die Daten zu kennen, bin ich
erstmal davon ausgegangen, dass da nur ein DS zurück kommt.
Könnte man das mit "MAX(K.Kilometer_Betrag) AS Foo" lösen?
Und auch das Startdatum direkt mit MAX + 1 berechnen?
.
(SELECT MAX(K.Kilometer_Betrag) AS KBetrag
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN (MAX(K.Dat_von)+1) AND K.Dat_bis)
.
@David
Sorry, wenn ich deinen Thread hier zum selber lernen "missbrauche".
Aber ich denke, dass am Ende auch eine Lösung für dich dabei herauskommt.
ZitatWegen dem ID Vergleich mit dem Formular bekomme ich doch nur eine Parameterabfrage wenn ich die Anfrage ausführe... und das Formular nicht geöffnet ist, oder.
Hast du es ausprobiert? Und den Fehler mit dem "!" korrigiert?
gruss ekkehard
Kein Problem, so hat jeder was von dem Problem ;-
also ich habe jetzt folgendes in die SQL Ansicht eingefügt (das von der Antwort zuvor):
UPDATE Auftritte_Ber AS AB
SET AB.Fahrtkosten = AB.Kilometer *
(SELECT K.Kilometer_Betrag
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN K.Dat_von AND K.Dat_bis)
WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID =
Forms!Auftritte!Auftritt_Kerndaten_ID
Da kam Syntaxfehler in Updateanweisung.
Das Mit dem nicht geöffneten Formular kenne ich von der vorherigen einfachen Lösung.
Aber um sicherzugehen war es gerade offen.
Hallo David,
ZitatDa kam Syntaxfehler in Updateanweisung.
Mehr nicht?
Normal wird bei einem SQL-Fehler doch auch der String mit angezeigt.
Bei dir nicht?
Mach mal Folgendes. Öffne im VBA Editor ein allgemeines Modul und
schreibe folgende Testprozedur.
Public Sub TestSQL()
Dim sSQL As String
sSQL = _
"UPDATE Auftritte_Ber AS AB " _
& "SET AB.Fahrtkosten = AB.Kilometer * " _
& "(SELECT K.Kilometer_Betrag " _
& "FROM Kilometergeld AS K " _
& "WHERE AB.Auftritt_Dat BETWEEN K.Dat_von AND K.Dat_bis) " _
& "WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID = " _
& "Forms!Auftritte!Auftritt_Kerndaten_ID"
Debug.Print sSQL
End SubDann öffnest du das Direktfenster mit Strg-G, setzt den Cursor in die
Prozedur und drückst F5. Da sollte dann der fertige SQL-String der
Abfrage auftauchen, und du kannst sehen was da nicht stimmt.
Zusätzlich kopierst du den String und postest ihn hier.
Am aller einfachsten wäre natürlich eine Beispiel-DB; - am Besten als
.mdb.
gruss ekkehard
Hallo,
wenn
ZitatIch habe eine Tabelle [Auftritte]
dann sollte die auch aktualisieret werden:
TOP 1 sorgt dafür, dass Access(Jet) auch nur
einen zurück gelieferten DS annimmt. Zudem: Namen auf Identität prüfen...
UPDATE Auftritte AS AB
SET AB.Fahrtkosten = AB.Kilometer *
(SELECT TOP 1 K.Kilometer_Betrag
FROM Kilometergeld AS K
WHERE AB.Auftritt_Dat BETWEEN K.Dat_von AND K.Dat_bis)
WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID =
Forms!Auftritte!Auftritt_Kerndaten_ID
Wenn's nicht mit der Unterabfrage klappt, kann zum Test auch die Dlookup-Funktion herhalten:
UPDATE Auftritte AS AB
SET AB.Fahrtkosten = AB.Kilometer * Dlookup("Kilometer_Betrag","Kilometergeld","Auftritt_Dat BETWEEN" & [Dat_von] & " AND " & [Dat_bis])
WHERE AB.Fahrtkosten Is Null AND AB.Auftritt_Kerndaten_ID =
Forms!Auftritte!Auftritt_Kerndaten_ID
Hallo Franz,
Welche Tabelle da jetzt aktualisiert werden soll weiss ich natürlich nicht,
DM wurde ja nicht vollständig gezeigt. Also bin ich von der Abfrage im
OP ausgegangen.
Zitatauch nur einen zurück gelieferten DS annimmt.
"nur
einen" ist klar. Nur, ist das auch der richtige in einem unsortierten RS ?
Wäre MAX da nicht besser?
Dlookup("Kilometer_Betrag","Kilometergeld","Auftritt_Dat BETWEEN" & [Dat_von] & " AND " & [Dat_bis])Kann das richtig sein? "Auftritt_Dat" ist IMO kein Feld von "Kilometergeld".
Oder was sehe ich da mal wieder nicht?
gruss ekkehard
Hallo,
k. A. wie die Felder heißen, deshalb riet ich letzthin, die Felder (Feldnamen) zu überprüfen.
Habe auch nicht den ganzen Threat durchforstet.
Zitat
"Wäre MAX da nicht besser?"
dürfte "egal" sein... Es sollte in dem Datumsbereich ja eh nur
einen Wert geben.
DMAX liefert halt den größten Wert von "Kilometer_Betrag", Dlookup den ersten beliebigen . Beides wäre falsch, gäbe es mehrere DS.
TOP 1 ist auch nur deshalb eingesetzt, um Jet unmissverständlich zu erklären, dass nur ein DS von der Abfrage geliefert werden wird. Access nimmt das ohne TOP 1 (im Vorfeld) nicht als selbstverständlich an, auch wenn letztendlich und tatsächlich nur ein DS geliefert würde.
Hallo Franz,
Danke für Erklärung.
gruss ekkehard
Guten Abend allerseits,
ich war über das WE auf einer Tagung daher die späte Antwort.
Ich habe eben versucht alle neuen Antworten zu verstehen... leider nicht so ganz
Daher im Anhang die Datenbank, habe aus Datenschutzgründen die Tabellen geleert, sollte aber hoffe ich keine Bezugsprobleme geben. Enthalten ist aktuell nur die Update Variante ohne den benötigten Datumsvergleich dort gespeichert.
Ich hoffe das macht die Sache einfacher.
(und noch als .mdB, ist das vollständig abwärtskompatibel mit Access 2016?)
>Viele Grüße und vielen Dank
David
PS: Da die Dateien größer als 300 KB , per Link
Links gelöscht, wegen Virenwarnungen. MzKlMu
Hallo,
ich habe die beiden Links gelöscht. Da kommen Warnungen für Malware und Viren.
Verwende Dropbox.com, da komm so etwas nicht vor.
Und nebenbei, Beispieldb sollte nur das enthalten, das zum Nachstellen des Problems notwendig ist. Und dann noch "Komprimieren/Reparieren" (Access Dienstprogramm) und anschließend Zippen. Dann sollte die Größe auch für das Forum klein genug sein.
Komprimiert und repariert sowie zippen hatte ich probiert... wie auch immer ich habe jetzt wie empfohlen alle nicht zwingenden Objekte gelöscht.
Ich hoffe jetzt passt alles.
Hallo David,
Hat ein bisschen gedauert.
Überarbeitete Version deiner DB anbei.
Was ich gemacht habe:
Den Beziehungen referentielle Integrität verpasst.
Das Feld Fahrtkosten aus Auftritte_Teilnehmer entfernt, da es aktuell
berechnet werden kann. Dafür in die Tabelle Auftritte ein Fremdschlüssel-
Feld auf die Tabelle Kilometer eingefügt.
Auf Basis der Tabelle Kilometer im Formular Auftritte ein Kombifeld
erstellt, das an das neue FK-Feld gebunden ist. Dieses ist dann auch gleich
auf das Auftrittsdatum gefiltert (sobald eins erfasst wurde. Evtl. muss da noch
ein Requery auf das Kombi beim Datumsfeld_AfterUpdate ausgeführt
werden.)
Das Ufo ist jetzt an eine neue Abfrage qTeilnehmer gebunden, in der dann
automatisch die Fahrtkosten aller erfassten Teilnehmer berechnet/angezeigt
werden.
Noch ein Hinweis zu einer Nebenbaustelle, die mir aufgefallen ist. Du hast
jeweils eine Tabelle für Einnahmen und Ausgaben. Dazu reicht eine Tabelle
bzw. ist eine Tabelle sogar besser (für Saldo- u.ä. Berechnungen.)
Einnahme und Ausgabe werden dabei, entweder direkt als + bzw. - Beträge
eingegeben, oder über einen Faktor (+1/-1) unterschieden.
gruss ekkehard
Vielen Dank erst einmal,
ich versuche jetzt mal in Ruhe alles Nachzuvollziehen.
Sowohl die Einzelgagen als auch die Fahrtkosten hatte ich geplant zu speichern, auch wenn es tendenziell redundant ist. War eine Absprache mit den Vorstandsleuten, die genaue Begründung weiß ich nicht mehr. Ein Punkt war, dass sich z.B. der Algorithmus für die Gagenverteilung verändern kann und dann Probleme auftreten wenn es nicht berechnet und gespeichert werden würde. Ähnliches galt für die Fahrtkosten wenn es nicht mehr beim Multiplizieren mit einem Betrag bleibt. Dass der Km-Betrag anpassbar ist war zumindest eine "Problem von vornherein ausschließen" Strategie von mir. Käme nur eine Änderung des KM-Betrages in Frage wäre es an der Stelle natürlich redundant. Wenn ich es richtig Überblicke kann ich deinen Lösungspfad aber trotzdem verwenden. Versuch macht klug an der Stelle.
Bezüglich der ref Integritäten: Aktuell hattest du nur die Aktualisierungweitergabe angeklickt... was passiert wenn ich jetzt einen Datensatz aus der Tbl Auftritte lösche? Bleiben die verbundenen Datensätze in Tbl Auftritt_Teilnehmer dann erhalten und verursacht das Probleme? Bzw. müsste ich dann nicht einfach auch die Löschweitergabe anklicken?
Bezüglich der Nebenbaustelle, ist bereits alles gelöst. Es gibt von vornherein verschiedene
Einnahme und Ausgabe-quellen wie zB. Kreditraten und Auftrittsgagen die für die Kassenbuch Ansicht schon per UNION gebündelt werden, da fand ich es nur stringent dann auch die weiteren Einnahmen und Ausgaben zu trennen. Ist vermutlich Geschmackssache. Im geforderten Kassenbuch-Layout sollten Einnahmen und Ausgabe in zwei Spalten aufgeführt sein und dann im Bericht ein Monatssaldo "unter den Stricht" erhalten. Wie auch immer die Baustelle ist für mich geklärt.
Viele Grüße
David
Hallo David,
Zum Speichern von Gagen und Fahrtkosten:
Da würde ich die Gagen sogar auch noch in eine eigene Tabelle verschieben,
und die mit der Tabelle Auftritt_Teilnehmer verknüpfen.
Diese ändern sich ja nicht nachträglich, so dass bei einer Änderung der
Berechnung(sformel) nur ein neuer DS angelegt wird. Berechnungsparameter
lassen sich ja neben einem Gültigkeitszeitraum auch in der/den Tabellen
festhalten; - denke ich.
Das Macro zu Gagenberechnung hattest du vor dem Hochladen entfernt, so
dass ich dazu nichts weiter sagen kann.
Zur RI:
Da DS normal nicht gelöscht, sondern eher als inaktiv/abgelaufen o.s.w.
markiert werden, setze ich die Löschweitergabe nur sehr sparsam ein, meist nur
während der Entwicklung, und in fremden DB setze ich sie gar nicht. Da musst
du dir überlegen, wie du das handhaben möchtest.
Bezügl. Nebenbaustelle ist es natürlich deine Entscheidung wie du das einrichtest.
gruss ekkehard
Guten Abend,
ich habe soeben eine sehr simple Lösung gefunden.
Über eine Abfrage wir der korrekte KM-Satz jeder Auftritte_ID zugeordnet. Und damit kann dann gerechnet werden.
SELECT A.Auftritt_Kerndaten_ID, MAX( KM.Kilometer_Betrag) AS KM_Betrag_Akt
FROM Kilometergeld AS KM, Auftritte_Ber AS A, Auftritt_Teilnehmer
WHERE (((A.Auftritt_Dat) Between [KM].[Dat_von] And [KM].[Dat_bis]))
GROUP BY A.Auftritt_Kerndaten_ID;
Vielen Dank für die vielen Denkanstöße
David