![]() |
Datenbank: Access • Version: 2003 • Zugriff über: ADOQUERY
Materialbestand berechnen
Hallo,
meine Aufgabe ist einen Materialbestand in einem Lager mit einigen Lagerplätzen in qm zu berechnen. Vorab einige Infos. Das Lager hat zig Plätze mit einer Lagerplatznummer. Nicht alle Lagerplätze sind belegt. Auf den belegten Plätzen liegen 1 bis x Materialrollen. Jede Materialrolle hat eine Rollennr. Es können verschiedene Materiallien auf einem Lagerplatz liegen. Eine Materialrolle hat eine Breite von x cm und eine Länge von x Metern. Es gibt 3 relevaten Tabellen in MS-Access (leider). Tabelle Rollenlager besteht aus 2 Feldern: 1)Rollennr und 2) Lagerplatznr. Tabelle Materialrollen: Rollennr, Materialnr, Rollenbreite, Rollenlänge und weitere Angaben. In der Tabelle Rollenlager sind nur die Rollen eingetragen, die tatsächlich sich auf diesem Platz befinden. Rollen, die für die Produktion oder anderweiteig verwendet wurden, werden automatisch gelöscht. In Tabelle Materialrollen sind jedoch alle Rollen eingetragen seit Aufzeichnung. Meine Idee ist erst eine neue Tabelle in Firebird zu erstellen z.B. Materialaktuell mit den Feldern Materialnr, Materialflaeche. In einer While-Schleife für jede Materialrollennr aus der Tabelle Rollenlager die entsprechende Materialnr aus der Tabelle Materialrollen herausselectieren, die Fläche berechnen und samt Materialnr und Fläche in die neue Tabelle einfügen. Habe ich, bevor ich mich da ranmache, irgendetwas nicht bedacht? Oder gibt es andere, bessere Ideen? Danke, vorab, für die Unterstützung. Gruß, Luckner |
AW: Materialbestand berechnen
Hallo, muss es unbedingt eine Tabelle sein? Live, also via View oder als Stored Procedure würde ich besser finden.
Ich habe keiner Erfahrung mit Access aber SQL unterstützt in Abfragen Sum() womit sich Summen bilden lassen ohne das selber machen zu müssen. |
AW: Materialbestand berechnen
Es muß keine Tabelle sein. Eine Stored Procedure fände ich auch gut, aber Access ist auch nicht meine Ecke und deshalb keine Ahnung ob und wie. Am Ende sollte es auf dem Papier stehen.
Luckner |
AW: Materialbestand berechnen
Du schreibts es gibt 3 Tabellen, zählst aber nur Rollenlager und Materialrollen auf!
Es stellt sich die Frage nach der Beziehung zwischen den Tabellen. rollennr + materialnr scheint der natürliche Schlüssel zu sein, der jede Position in Materialrollen eindeutig bestimmt. Deshalb ist dieser Schlüssel auch in Rollenlager eingetragen, um die tatsächlich vorhandenen Materialrollen zu identifizieren.
SQL-Code:
Sollte alle Elemente aus Materialrollen liefern, die tatsächlich vorhanden sind.
select b.*
from rollenlager a left join materialrollen b on (b.rollennr = a.rollennr) and (b.materialnr = a.materialnr) |
AW: Materialbestand berechnen
Zitat:
|
AW: Materialbestand berechnen
Klingt mir schwer nach: Wir schreiben eine View. Um das zu vereinfachen, wäre es nett, wenn Du uns den Teil der Tabellendefinitionen zur Verfügungstellen könntest, der entweder als Schlüssel / Fremdschlüssel oder für die Ermittlung des gewünschten Ergebnisses erforderlich erscheint.
Verstehe ich das richtig: Materialrollen enthält alles, was es je gegeben hat? Rollenlager enthält alles, was es gerade im Moment der Auswertung tatsächlich im Lager gibt? Blups Ansatz ist hier richtig, fraglich ist halt nur, was die dritte Tabelle enthält. Da müsste man dann mal schauen, inwieweit sie zur Verwendung kommen kann / muss und wie granular das Ergebnis sein soll. Grob könnte man (mal aus dem Bauch heraus) Blups Idee so erweitern:
SQL-Code:
Ein
create view v_quadratmeter as
select b.rollennr, b.materialnr, sum(b.breite * b.laenge) as quadratmeter from rollenlager a left join materialrollen b on (b.rollennr = a.rollennr) and (b.materialnr = a.materialnr) group by b.rollennr, b.materialnr order by b.rollennr, b.materialnr
SQL-Code:
sollte Dir dann jederzeit den aktuellen Bestand zur Zeit der Abfrage liefern können.
select * from v_quadratmeter
|
AW: Materialbestand berechnen
Es gibt eigtentlich nur die 2 genannten Tabellen. Die 3. ist nicht so wichtig. Es stehen nur die Materialnummern und die entsprechenden Preise und Lieferanten. Die Preise werden jedoch nut gelegentlich gepflegt und deshalb wird man den Materialwert des Lagers mit den entsprechenden Rechnungen berechnen.
Delphi.Narium, es stimmt so, wie Du es beschrieben hast mit den Tabellen. Werden Dein select-Vorschlag gleich ausprobieren. Und das Ergebnis hier melden. Vielen Dank an Alle. Luckner |
AW: Materialbestand berechnen
Da ich jedoch nicht weiß, wie eine View in Access erstellt wird (in VBA will ich mich nicht einarbeiten, auch weil die Software in den nächste Jahren auf Firebird laufen soll) werde ich eine Tabelle in Firebird erstellen und ein select für die beiden Access-Tabellen erstellen.
Luckner. |
AW: Materialbestand berechnen
Kann man in Access kein Create View ausführen? Muss man da alles irgendwie über die Oberfäche zusammendaddeln?
Im Zweifelsfalle einfach in Delphi eine Query mit dem entsprechenden SQL versorgen und per ExecSQL ausführen. Mach' ich immer so, auch mit Accessdatenbanken. Klappt seit Jahrzehnten. Accessdatenbanken nutze ich immer ohne das zugehörige Programm.
Delphi-Quellcode:
Würde mich schwer wundern, wenn das nicht funktioniert.
Query.Close;
Query.SQL.Clear; Query.SQL.Add('create view v_quadratmeter as'); Query.SQL.Add('select b.rollennr, b.materialnr, sum(b.breite * b.laenge) as quadratmeter'); Query.SQL.Add('from rollenlager a'); Query.SQL.Add('left join materialrollen b on (b.rollennr = a.rollennr) and (b.materialnr = a.materialnr)'); Query.SQL.Add('group by b.rollennr, b.materialnr'); Query.SQL.Add('order by b.rollennr, b.materialnr'); Query.ExecSQL; |
AW: Materialbestand berechnen
OK, werde ich probieren. Danke.
Luckner |
AW: Materialbestand berechnen
Habe es gerade nochmal an 'ner eigene DB ausprobiert.
Es kann sein, dass man die Zeile
Delphi-Quellcode:
bei Access weglassen muss. Das ist aber kein Beinbruch, dann macht man das Order by halt erst bei der Abfrage per
Query.SQL.Add('order by b.rollennr, b.materialnr');
SQL-Code:
select * from v_quadratmeter order by rollennr, materialnr
|
AW: Materialbestand berechnen
Hallo Delphi.Narium
Code:
Die Zeile kann ich so nicht richtig nachvolziehen, denn in der Tabelle a (Rollenlager) gibt es nicht das Feld materialnr.
Query.SQL.Add('left join materialrollen b on (b.rollennr = a.rollennr) and (b.materialnr = a.materialnr)');
In meiner Abfrage (jetzt mit den original Bezeichnungen) habe die die Abfrage so eingetragen.
Code:
noch ohne GROUP BY usw. und bekomme jetzt einhen Fehler, den ich nicht verstehe:
SELECT Materialrollen.Rollennummer, Materialrollen.[Mat-Nr], SUM(Materialrollen.[Arb-Breite] * Materialrollen.lfm) as quadratmeter
FROM Materialrollen LEFT JOIN Rollenlager ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer) "Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'Rollennummer' nicht als Teil einer Aggregatfunktion einschließt" Wenn ich jedoch
Code:
weglasse, dann kann ich die Funktion aufrufen. Also ist an der Summen-Funktion etwas faul. Bitte um Hilfe.
, SUM(Materialrollen.[Arb-Breite] * Materialrollen.lfm) as quadratmeter
Gruß, Luckner |
AW: Materialbestand berechnen
Zitat:
Aggregatfunktionen, wie Sum, Count, ..., die zusätzlich zu nicht aggregierten Spalten in einem SQL vorkommen, funktionieren nur dann, wenn alle Spalten, die nicht aggregiert werden, im Group By enthalten sind.
SQL-Code:
SELECT
Materialrollen.Rollennummer, -- nicht aggregiert Materialrollen.[Mat-Nr], -- nicht aggregiert SUM(Materialrollen.[Arb-Breite] * Materialrollen.lfm) as quadratmeter -- aggregiert FROM Materialrollen LEFT JOIN Rollenlager ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer) group by Materialrollen.Rollennummer, -- muss, da nicht aggregiert, ins Group By Materialrollen.[Mat-Nr] -- muss, da nicht aggregiert, ins Group By |
AW: Materialbestand berechnen
Vielen Dank Delphi.Narium,
hatte nicht gedacht, dass am Sonntag irgend Jemand im Forum aktiv ist. Vielen Dank. Werde mir die JOIN-Verbindungen und entsprechende Kriterien unbedingt reinziehen. Gruß, Luckner |
AW: Materialbestand berechnen
Code:
Jetzt bekommen ich in der Tabelle jede Rolle mit der berechneten Fläche angezeigt. D.h. für ein Material mit 10 Rollen, wird diese Angabe 10-mal in der Tabelle angezeigt. Kann ich die jetzt pro Materialrollen.[Mat-Nr] addieren zu einer Summe?
Datamodule1.ADOQueryMaterialGesamt.Active := False;
Datamodule1.ADOQueryMaterialGesamt.SQL.Clear; Datamodule1.ADOQueryMaterialGesamt.SQL.Add('SELECT Materialrollen.Rollennummer, Materialrollen.[Mat-Nr], SUM((Materialrollen.[Arb-Breite]/1000) * Materialrollen.lfm) as quadratmeter'); Datamodule1.ADOQueryMaterialGesamt.SQL.Add(' from Rollenlager'); Datamodule1.ADOQueryMaterialGesamt.SQL.Add(' LEFT JOIN Materialrollen ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer)'); Datamodule1.ADOQueryMaterialGesamt.SQL.Add(' GROUP BY Materialrollen.[Mat-Nr], Materialrollen.Rollennummer'); Datamodule1.ADOQueryMaterialGesamt.SQL.Add(' ORDER BY Materialrollen.[Mat-Nr]'); Datamodule1.ADOQueryMaterialGesamt.Open; Danke, Luckner |
AW: Materialbestand berechnen
Ja, indem Du die Spalte Materialrollen.Rollennummer in der Auswahl (hinter dem Select) und im Group By weglässt.
SQL-Code:
SELECT Materialrollen.[Mat-Nr], SUM((Materialrollen.[Arb-Breite]/1000) * Materialrollen.lfm) as quadratmeter
from Rollenlager LEFT JOIN Materialrollen ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer) GROUP BY Materialrollen.[Mat-Nr] ORDER BY Materialrollen.[Mat-Nr] |
AW: Materialbestand berechnen
Hallo Delphi.Narium,
vielen Dank. Es hat mich wirklich weitergebracht. Jetzt werde ich mich mit den Join's beschäftigen, weil so richtig habe ich die Funktionsweise eines Join's nicht verstanden. Aber es es gibt im Netz so einige Beispiele und die werde ich mir reinziehen. Gruß, Luckner |
AW: Materialbestand berechnen
Wenn ich datenbankunabhängige Hilfe zu SQL benötige, fange ich immer hier an:
![]() |
AW: Materialbestand berechnen
Zitat:
JOIN dient explizit der Verknüpfung von 2 Datenquellen, während WHERE das teilweise auch kann, hauptsächlich aber eine Filterfunktion darstellt. (Mit der Filterfunktion wird auch die Tabellenverknüpfung erreicht) JOIN ist moderner und hat verschiedene Vorteile. Nicht unbedingt offensichtlich für Leute, die es nicht kennen: JOIN erhöht die Lesbarkeit eines Select Statements. Man kann direkt lesen, welche Tabellen verknüpft sind (und wie) und so auch leichter bemerken, wenn eine Verknüpfung fehlt oder falsch ist. Alles was nicht JOIN ist, kommt ins WHERE und filtert "nur noch" das Gesamtergebnis. Das ist bei komplexen Statements naturgemäß besonders hilfreich. JOIN ist (oft) mächtiger als WHERE, von FULL JOIN, über LEFT, RIGHT bis FULL OUTER kann man schon sehr viel abbilden. Es gibt weitere .. JOIN hilft der Abfrage Optimierung (je nach Hersteller). Bedeutet nichts anderes, als das damit auch die DB im SQL Statement besser erkennen kann, wie sie am besten an eine Abfrage rangeht. Nach der Empfehlung von Delphi.Narium würde ich noch ergänzen, dass es beim SQL lernen sehr hilfreich ist, selbst mit einer DB kleine Beispiele auszuprobieren, die auch optisch / im Kopf leicht nachvollziehbar sind. Geht sogar online mit vielen DB. ![]() |
AW: Materialbestand berechnen
Hallo Delphi.Narium, hallo jobo,
danke für die Links. Werde mich mit beiden beschäftigt. Den Link von jobo finde ich genial, weil man dort gleich einige selection's ausprobieren kann. Wird mir richtig helfen. Vielen Dank. Gruß, Luckner |
AW: Materialbestand berechnen
Jetzt versuche ich die Materialnummer einer Lieferanten-Nr aus einer 3. Tabelle zu zuordnen. in der Tabelle 'Material-Stamm' gibt es die Felder 'Material-Stamm.Mat-Nr' und
'Material-Stamm.Lieferanten-Nr'. Meine Erweiterung der Select-Anweisung wäre folgende:
Code:
Bekomme jetzt den Fehler: Fehler bei der Authentifizierung.
SELECT Materialrollen.Rollennummer, Materialrollen.[Mat-Nr], SUM(Materialrollen.[Arb-Breite] * Materialrollen.lfm) as quadratmeter, [Material-Stamm].[Lieferanten-Nr]
FROM Materialrollen LEFT JOIN Rollenlager ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer) LEFT JOIN [Material-Stamm] ON ([Material-Stamm].[Mat-Nr] = Materialrollen.[Mat-Nr]) GROUP BY Materialrollen.[Mat-Nr], Materialrollen.Rollennummer Habe ich jetzt die eckigen Klammern fasch, oder die 2.-te JOIN-Anweisung ist hier falsch. Gruß, Luckner |
AW: Materialbestand berechnen
Wenn Du Aggregatfunktionen wie SUM benutzt, gehören alle Nicht-Aggregat-Felder der Ergebnismenge ins GROUP BY, also auch [Material-Stamm].[Lieferanten-Nr].
|
AW: Materialbestand berechnen
Sorry, mein Fehler. Habe den Connections String vergessen einzugeben
Mit
Code:
bekomme ich jetzt einen Syntaxfehler (fehlender Operator zwischen '(Materialrollen.Rollennummer = Rollenlager.Rollennummer)
SELECT Materialrollen.Rollennummer, Materialrollen.[Mat-Nr], SUM(Materialrollen.[Arb-Breite] * Materialrollen.lfm) as quadratmeter, [Material-Stamm].[Lieferanten-Nr]
FROM Materialrollen LEFT JOIN Rollenlager ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer) LEFT JOIN [Material-Stamm] ON ([Material-Stamm].[Mat-Nr] = Materialrollen.[Mat-Nr]) GROUP BY Materialrollen.[Mat-Nr], Materialrollen.Rollennummer, [Material-Stamm].[Lieferanten-Nr] LEFT JOIN [Material-Stamm] ON ([Material-Stamm].[Mat-Nr] = Materialrollen.[Mat-Nr])' Luckner |
AW: Materialbestand berechnen
Sieht eigentlich richtig aus, aber derda sagt was anderes:
![]() Wenn ich's halbwegs recht überblicke, könnte dabei sowas rauskommen:
SQL-Code:
MS-Access <-> SQL-Standard: Zwei fremde Welten treffen aufeinander ;-)
SELECT
Materialrollen.Rollennummer, Materialrollen.[Mat-Nr], SUM(Materialrollen.[Arb-Breite] * Materialrollen.lfm) as quadratmeter, [Material-Stamm].[Lieferanten-Nr] FROM ( Materialrollen LEFT JOIN Rollenlager ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer) ) LEFT JOIN [Material-Stamm] ON ([Material-Stamm].[Mat-Nr] = Materialrollen.[Mat-Nr]) GROUP BY Materialrollen.[Mat-Nr], Materialrollen.Rollennummer, [Material-Stamm].[Lieferanten-Nr] Es ist immer wieder für eine Überraschung gut :-( |
AW: Materialbestand berechnen
Alles weglassen, was überflüssig ist und Fehler eingrenzen.
Die Klammern in "join.. on (..) " können sowieso weg. (Außer vielleicht bei Access ..:roll:) Alle Felder in Select Clause raus, ersetzten durch 1 oder so. Passend dazu Group By raus. usw. Jeweils ausprobieren, wie sich die Fehlermeldung ändert. dann: Die eckigen Klammern sind k... ein Spaß! Zur Lesbarkeit wenigstens die Tabellennamen mit Alias ersetzen, ab da Aliase verwenden, wo möglich. Du verwendest "sogar" Mischbetrieb, mit und ohne Klammern. Dabei können doofe Sachen passieren. Eine Tabelle, die mit Klammerung erzeugt wurde, aber eigentlich, dank brauchbarer Schreibweise auch ohne funktionieren würde, funktioniert in manchen Systemen dann nur in der exakten Klammerschreibweise. Großzügige Handhabung von Groß- und Kleinschreibweise gibt es dann nicht mehr. Folge: Feld/ Tabelle nicht erkannt, Abfrage Parser macht die Grätsche. Passende Fehlermeldung Glückssache. Auch hier gilt: Keine Ahnung, was Access dazu sagt. Auch wenn es hier zu spät ist. Meine dringende Empfehlung: ANSI Zeichensatz (nur Buchstaben, u.U. Zahlen) ohne Leerzeichen für Datenbankobjekte. Das spart viel Ärger und bringt viel Übersichtlichkeit. OT:Was ich auch nicht verstehe, ist dann der Kontrast zwischen schicken Umlauten und Sinn entstellenenden Abkürzungen, nur um z.B. 4 Buchstaben zu sparen. Ebenso Fremdsprach Mischmash. |
AW: Materialbestand berechnen
Hallo jobo,
die Tabellennamen sind nicht auf meinem Mist gewachsen. Ich habe sowieso keine Ahnung von Access. Es ist eine alte, aber noch funktionsfähige, Datenbank. Ich versuche nur einige Auswertungen noch zu bewerkstellingen. Irgendwann soll diese Anwendung auf einem SQL-Server laufen. Werde einige Sachen ausprobieren. Wenn nicht, dann muß ich mich in einem Access-Forum anmelden :? Danke und schönes WE. Luckner |
AW: Materialbestand berechnen
Zitat:
Ach und grad erst gesehen, dbfiddle ist für Foren super, notfalls auch privat, wenn man grad kein XY DB da hat. Es gibt noch einige andere solche Online DB, die alle ihre Vor und Nachteile haben. Oder z.B. auch Online SQL Formatierer, falls das eigene Tool es nicht kann. Ich persönlich finde das sehr hilfreich, formatierte SQL Statements zu lesen, spätestens, wenn sie nicht tun, was sie sollen. Ist ja im Programmcode auch nicht anders. |
AW: Materialbestand berechnen
Zitat:
Bis bald... Thomas |
AW: Materialbestand berechnen
|
AW: Materialbestand berechnen
Zitat:
Bis bald... Thomas |
AW: Materialbestand berechnen
Ich habe es herausgefunden:
SELECT Materialrollen.[Mat-Nr], SUM((Materialrollen.[Arb-Breite]/1000) * Materialrollen.lfm) as quadratmeter, [Material-Stamm].[Lieferanten-Nr] FROM (Rollenlager LEFT JOIN Materialrollen ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer)) LEFT JOIN [Material-Stamm] ON ([Material-Stamm].[Mat-Nr] = Materialrollen.[Mat-Nr]) GROUP BY Materialrollen.[Mat-Nr], [Material-Stamm].[Lieferanten-Nr] ORDER BY Materialrollen.[Mat-Nr] und es scheint zu funktionieren. MS Access will unbedingt die JOINs, wenn mehrere vorhanden sind, in Klammern gesetzt haben.. Wußte ich nicht und bekam dann immer Syntax-Fehler. Habe es in einem Access-Forum gefunden. Also, falls Jemand sich mal dafür interessiert. Gruß, Luckner |
Alle Zeitangaben in WEZ +1. Es ist jetzt 00:22 Uhr. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz