Materialbestand berechnen

[Luckner]

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

[Sinspin]

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.

[Luckner]

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

[Blup]

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:

select    b.* 
from      rollenlager a
left join materialrollen b on (b.rollennr = a.rollennr) and (b.materialnr = a.materialnr)

Sollte alle Elemente aus Materialrollen liefern, die tatsächlich vorhanden sind.

[dummzeuch]

Zitat:

Zitat von Luckner (Beitrag 1501656)

Eine Stored Procedure fände ich auch gut, aber Access ist auch nicht meine Ecke und deshalb keine Ahnung ob und wie.

In Access schreibt man "stored Procedures" in VBA. Nichts, was man wirklich machen will.

[Delphi.Narium]

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:

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

Ein

SQL-Code:

select * from v_quadratmeter

sollte Dir dann jederzeit den aktuellen Bestand zur Zeit der Abfrage liefern können.

[Luckner]

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

[Luckner]

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.

[Delphi.Narium]

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:

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;

Würde mich schwer wundern, wenn das nicht funktioniert.

[Luckner]

OK, werde ich probieren. Danke.

Luckner

[Delphi.Narium]

Habe es gerade nochmal an 'ner eigene DB ausprobiert.

Es kann sein, dass man die Zeile

Delphi-Quellcode:

Query.SQL.Add('order by b.rollennr, b.materialnr');

bei Access weglassen muss. Das ist aber kein Beinbruch, dann macht man das Order by halt erst bei der Abfrage per

SQL-Code:

select * from v_quadratmeter order by rollennr, materialnr

[Luckner]

Hallo Delphi.Narium

Code:

Query.SQL.Add('left join materialrollen b on (b.rollennr = a.rollennr) and (b.materialnr = a.materialnr)');

Die Zeile kann ich so nicht richtig nachvolziehen, denn in der Tabelle a (Rollenlager) gibt es nicht das Feld materialnr.

In meiner Abfrage (jetzt mit den original Bezeichnungen) habe die die Abfrage so eingetragen.

Code:

SELECT Materialrollen.Rollennummer, Materialrollen.[Mat-Nr], SUM(Materialrollen.[Arb-Breite]  *  Materialrollen.lfm) as quadratmeter
FROM Materialrollen LEFT JOIN Rollenlager ON (Materialrollen.Rollennummer = Rollenlager.Rollennummer)

noch ohne GROUP BY usw. und bekomme jetzt einhen Fehler, den ich nicht verstehe:

"Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'Rollennummer' nicht als Teil einer Aggregatfunktion einschließt"

Wenn ich jedoch

Code:

, SUM(Materialrollen.[Arb-Breite]  *  Materialrollen.lfm) as quadratmeter

weglasse, dann kann ich die Funktion aufrufen. Also ist an der Summen-Funktion etwas faul. Bitte um Hilfe.

Gruß, Luckner

[Delphi.Narium]

Zitat:

"Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'Rollennummer' nicht als Teil einer Aggregatfunktion einschließt"

heißt nichts weiter, als dass das Group By fehlt.

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

[Luckner]

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

[Luckner]

Code:

  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;

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?

Danke, Luckner

[Delphi.Narium]

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]

[Luckner]

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

[Delphi.Narium]

Wenn ich datenbankunabhängige Hilfe zu SQL benötige, fange ich immer hier an: W3Schools - SQL Tutorial

[jobo]

Zitat:

Zitat von Luckner (Beitrag 1501876)

.. weitergebracht. Jetzt werde ich mich mit den Join's beschäftigen, weil so richtig habe ich die Funktionsweise eines Join's nicht verstanden.

JOIN und seine Varianten, z.B. LEFT JOIN ist eine spezifische Form von WHERE. Sie ist standardisiert und funktioniert so für verschiedene DB Hersteller identisch (nicht alle haben den vollen Standard bzw. dessen Besonderheiten umgesetzt, alle haben aber Eigenheiten jenseits des Standards, klar).

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.

https://dbfiddle.uk/?rdbms=sqlserver...86707996fcf359

[Luckner]

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

[Luckner]

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:

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

Bekomme jetzt den Fehler: Fehler bei der Authentifizierung.

Habe ich jetzt die eckigen Klammern fasch, oder die 2.-te JOIN-Anweisung ist hier falsch.

Gruß, Luckner

[DeddyH]

Wenn Du Aggregatfunktionen wie SUM benutzt, gehören alle Nicht-Aggregat-Felder der Ergebnismenge ins GROUP BY, also auch [Material-Stamm].[Lieferanten-Nr].

[Luckner]

Sorry, mein Fehler. Habe den Connections String vergessen einzugeben

Mit

Code:

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]

bekomme ich jetzt einen Syntaxfehler (fehlender Operator zwischen '(Materialrollen.Rollennummer = Rollenlager.Rollennummer)
LEFT JOIN [Material-Stamm] ON ([Material-Stamm].[Mat-Nr] = Materialrollen.[Mat-Nr])'

Luckner

[Delphi.Narium]

Sieht eigentlich richtig aus, aber derda sagt was anderes: Multiple LEFT JOINs in MS Access Database (was mir bisher nicht bekannt war und mir fehlt die Möglichkeit es zu testen).

Wenn ich's halbwegs recht überblicke, könnte dabei sowas rauskommen:

SQL-Code:

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]

MS-Access <-> SQL-Standard: Zwei fremde Welten treffen aufeinander ;-)
Es ist immer wieder für eine Überraschung gut :-(

[jobo]

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.

[Luckner]

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

[jobo]

Zitat:

Zitat von Luckner (Beitrag 1502067)

die Tabellennamen sind nicht auf meinem Mist gewachsen. .. Ich versuche nur einige Auswertungen noch zu bewerkstellingen.

Ich kenne das, alte Anwendung, die niemand mehr haben will (also Entwickler), aber zäh sind. Das geht nicht gegen Dich, man kann sich selten gegen sowas wehren, außer man ist der Chef. Ich hab nur die Gelegenheit genutzt, es mal wieder los zu werden. Vielleicht bekommt es ja noch jemand mit.

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.

[mytbo]

Zitat:

Zitat von jobo (Beitrag 1502069)

Es gibt noch einige andere solche Online DB, die alle ihre Vor und Nachteile haben.

Wäre super, wenn du die Links hier posten könntest, einschließlich einer kurzen Beschreibung über die von dir empfundenen Vor- und Nachteile. Dafür würde es bestimmt einige :thumb: geben.

Bis bald...
Thomas

[jobo]

OT
Hab ich hier ausgekoppelt:
https://www.delphipraxis.net/209971-...ksammlung.html

[mytbo]

Zitat:

Zitat von jobo (Beitrag 1502106)

Hab ich hier ausgekoppelt:
https://www.delphipraxis.net/209971-...ksammlung.html

Dafür von mir ein :thumb:

Bis bald...
Thomas

[Luckner]

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