NZ-Funktion. Trotz google nicht die passende Lösung

Question

Hallo.

Mein Problem bei Access:

Ich habe zwei Tabellen mit Beständen.

angenommen ich habe in tbl_a die Artikel 5-20 geführt und in tbl_b die Artikel 1-15 geführt.

Dann brauche ich eine Abfrage, die mir die Differenzen der Artikel 1-20 anzeigt.

Meine Umsetzung:
Beziehungen zwischen Feld Artikel und Stellplatz (beide mit der Verknüpfungseigenschaft 2 (alle aus tbl_a)

In dem Differenzfeld (Diff.) der Abfrage steht folgendes:

Diff: Summe(Nz([tbl_a.Bestand];0)-Nz([tbl_b.Bestand];0))

Wäre aus dem Häuschen, wenn mir weiergeholfen wird.

Gruß
Andreas

Answer 1

Hallo Andreas,

ein nicht so triviales Problen. Den Stellplatz habe ich aus meiner Lösung mal raus gelassen, da ich die Bedeutung für de Berechnung nicht erkenne. Zunächst habe ich zwei Tabellem A1 und A2 mit den Feldern "Artikel" und "Bestand" erstellt (beide Felder long int) und ein wenig gefüllt. Sorry die Spalten der Tabellen/Abfragen bekomme ich nicht gut dargestellt.

A1:
[list]Artikel Bestand
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15[/list]

und A2:
[list]Artikel Bestand
6 1
7 2
8 3
9 4
10 5
11 6
12 7
13 8
14 9
15 10
16 11
17 12
18 13
19 14
20 15[/list]

Als nächstes definiere eine Abfrage "allArtikel" mit dem SQL-Code, der nicht in der Entwurfsansicht, sondern nur in der SQL-Ansicht erstellt werden kann:

SELECT distinct artikel FROM A1
UNION SELECT distinct artikel FROM A2;

Danach die Abfrage "diff" mit dem SQL-Code:

SELECT allArtikel.artikel, A1.Bestand, A2.Bestand, nz(a1.bestand)-nz(a2.bestand) AS diff
FROM (allArtikel LEFT JOIN A2 ON allArtikel.artikel = A2.Artikel)
LEFT JOIN A1 ON allArtikel.artikel = A1.Artikel;

Raus kommt dabei (leeres Feld als # dargestellt):
[list]artikel A1.Bestand A2.Bestand diff
1 1 # 1
2 2 # 2
3 3 # 3
4 4 # 4
5 5 # 5
6 6 1 5
7 7 2 5
8 8 3 5
9 9 4 5
10 10 5 5
11 11 6 5
12 12 7 5
13 13 8 5
14 14 9 5
15 15 10 5
16 # 11 -11
17 # 12 -12
18 # 13 -13
19 # 14 -14
20 # 15 -15[/list]
Übr die Effizienz läßt sich hier vortrefflich streiten ;-)
Habe ich dein Problem überhaupt korrekt erfasst?

Gruß
Ralf

Answer 2

hallo Andreas,

probier mal:

Diff: (Nz([tbl_a]![Bestand];0)-Nz([tbl_b]![Bestand];0))

ist es das, was Du suchst??

gruß marie

Answer 3

Hallo und danke für eure Antworten.

Marie: leider hilft mir dein Tipp nicht. So ähnlich hab ich das schon mehrmals versucht.

ich versuch nochmal mein Problem zu beschreiben (inzwischen ist auch ein Neues dazugekommen....), nicht dass jemand denkt, ich könnt ja gar nix, es gibt paar anfragen, die ich zum Laufen bekommen hab ;-)

Aufgabe 1:
Ich habe zwei Tabellen. Brauche eine Übersicht über alle Einträge aus Tab1 & Tab2; zusätzlich soll die Abfrage die Differenz gegenüberstellen.

Problem:
-Welchen Verknüpfungstyp soll ich wählen? LEFT JOIN; INNER JOIN?
-NULL-Werte werden nicht berechnet
Bestände sollen pro Artikel summiert werden (*)


Besonderheiten:
(*)Ein Artikel hat unterschiedliche Abweichungen (verfügbar-beschädigt, etc)
Das heißt in jeder Tabelle steht der Artikel mehrmals drin
tbl_a-4711 - Abw: 0 - Bestand: 10
tbl_a-4711 - Abw: 1 - Bestand 5
tbl_a4712 - Abw: 0 - Bestand 12

tbl_b4711 - Abw 0 - Bestand 9
tbl_b4711 - Abw 1 - Bestand 6
tbl_b4713 - Abw 0 - Bestand 22

Als Ergebnis soll (Abfr_1) Alle Positionen gegenüberstellen
(d.h. auch anzeigen, das 4712 in tbl_b 0 Bestand hat und 4713 in tb_a mit 0 Bestand drin steht.

Abfr_2 soll den Gesamtbestand eines Artikels anzeigen. Das würde heißen #4711 hätte keine Differenz (wäre nur eine Falschbuchung Abw 0 in 1...)

Für diese beiden ERgebnisse benötige ich bisher 4 Abfragen. Erst die Gegenüberstellung, und danach die Nz Funktion (bezogen auf die Gegenüberstellungs qry.

Für das zweite Problem benutze ich einen neuen Thread.

Grüße
Andreas

Answer 4

Hallo Andreas,

einfach machst du es uns nicht! Folgendes würde helfen:

1. in wiefern past meine Lösung? Du gehst gar nicht darauf ein.
2. du sprichst von 4 Abfragen, die dein Problem lösen. Warum postest du die SQL-Statements nicht?
3. Deine Beschreibung der "Abw"-Problematik kann zumindest ich mit dem kurzen Bsp. nicht verstehen
4. Die Inhalte der Tabellen sind schon mal gut, die möglichen Ergebnismengen zu deinem Problem wären prima.

Fazit: je besser man ein Problem beschreibt, je besser sind die Antworten ...

Gruß
Ralf

Answer 5

Hallo Ralf.

Das Problem deiner 1. Lösung ist, dass die leeren Felder nicht mit Nz funktionieren.

Die Abweichung dient dazu ein und den selben Artikel intern zu unterscheiden (0=beschädigt, 1=verfügbar, 2=gesperrt). Angenommen das System gibt Menge 2 bei 4711 als beschädigt aus, aber gezählt werden 5, dann gibt es hier eine Bestandsdifferenz.

zu 2:
Die 4 Abfragen (bzw. 2 pro Ausgabe) sollten ja mit nur einer erledigt werden können)

SELECT tbl_inventorystock.article, tbl_inventorystock.variance, tbl_inventorystock.code, tbl_inventorystock.inventory_stock AS [Stock(inventory)], NZ([system_stock],0) AS [Stock(system)], ([inventory_stock])-([system_stock]) AS Difference, tbl_inventorystock.date
FROM tbl_inventorystock LEFT JOIN tbl_systemstock ON (tbl_inventorystock.date = tbl_systemstock.date) AND (tbl_inventorystock.code = tbl_systemstock.code) AND (tbl_inventorystock.variance = tbl_systemstock.variance) AND (tbl_inventorystock.article = tbl_systemstock.article)
GROUP BY tbl_inventorystock.article, tbl_inventorystock.variance, tbl_inventorystock.code, tbl_inventorystock.inventory_stock, NZ([system_stock],0), ([inventory_stock])-([system_stock]), tbl_inventorystock.date
HAVING (((tbl_inventorystock.date)=[Auswahldatum]));

Feld "code" steht zum Beispiel für Standort. Das heißt, die Bestände sind einmal detailliert ausgegeben: Abw und Lager, und einmal als Summe (alle Läger+alle Abw)

SELECT qry_difference_by_detail.article, Sum(qry_difference_by_detail.[Stock(inventory)]) AS [SummevonStock(inventory)], Sum(qry_difference_by_detail.[Stock(system)]) AS [SummevonStock(system)], Sum(qry_difference_by_detail.Difference) AS SummevonDifference, qry_difference_by_detail.date
FROM qry_difference_by_detail
GROUP BY qry_difference_by_detail.article, qry_difference_by_detail.date;

zu3:
sollte mittllerweile etwas deutlicher (oder komplizierter) geworden sein ;o)

zu4:
Habe ich oben schon erwähnt.

Als Ergebnis soll die Abfrage mit leeren Feldern rechnen können (also leere Felder sollen den Inhalt "0" bekommen)
Und ich benötige die Differenz jedes Artikels (also alle in tbl_a geführten und alle in tbl_b geführten Artikel), denn es kann vorkommen, dass ich bei 20 geführten Artikel von 1-15 gezählt habe, und im System von 6-20 geführt werden. Daraus folgt eben eine Liste mit 20 Artikeln und deren Differenz. Aber geht doch nur, wenn alle Bestandsfelder gefüllt sind (integer und nicht NULL)

Mir raucht der Kopf.

Gruß
Andreas

Answer 6

Hallo Andreas,

ok, ein neuer Anlauf.

1. Ich habe deine Tabelle nachgebildet und die Werte der Antwort 3 von dir eingefügt. Date mit 1.1.2009 und code mit 1 als Defaultwert. Es kommt mit deiner Abfrage

article variance code Stock(inventory) Stock(system) Difference date
4711 0 1 10 9 1 01.01.2009
4711 1 1 5 6 -1 01.01.2009
4712 0 1 12 0 01.01.2009

heraus. Die Difference-Berechnung habe ich durch

nz([inventory_stock])-nz([system_stock]) AS Difference

ersetzt und bekomme dann

article variance code Stock(inventory) Stock(system) Difference date
4711 0 1 10 9 1 01.01.2009
4711 1 1 5 6 -1 01.01.2009
4712 0 1 12 0 12 01.01.2009

Also 12 bei article 4712 als Differenz statt <null>. Das ist übrigens Maries Vorschlag!

Um das Ganze jetzt mit einer Abfrage zu lösen benötigst du die Abfrage allArtikel, die mit deiner Benamung folgendermaßen aussieht

SELECT distinct article, variance, code, date
FROM tbl_inventorystock
UNION SELECT distinct article, variance, code, date
FROM tbl_systemstock;

danach erzeugst du die Abfrage

SELECT allArtikel.article, allArtikel.variance, allArtikel.code, allArtikel.date, nz([inventory_stock],0) AS StockInventory, nz([system_stock],0) AS StockSystem, [stockinventory]-[StockSystem] AS Difference
FROM (allArtikel LEFT JOIN tbl_inventorystock ON (allArtikel.date = tbl_inventorystock.date) AND (allArtikel.code = tbl_inventorystock.code) AND (allArtikel.variance = tbl_inventorystock.variance) AND (allArtikel.article = tbl_inventorystock.article)) LEFT JOIN tbl_systemstock ON (allArtikel.date = tbl_systemstock.date) AND (allArtikel.code = tbl_systemstock.code) AND (allArtikel.variance = tbl_systemstock.variance) AND (allArtikel.article = tbl_systemstock.article)
WHERE (((allArtikel.date)=[Auswahldatum]));

und bekommst als Ergebnis

article variance code date StockInventory StockSystem Difference
4711 0 1 01.01.2009 10 9 1
4711 1 1 01.01.2009 5 6 -1
4712 0 1 01.01.2009 12 0 12
4713 0 1 01.01.2009 0 22 -22

Hier sind alle Artikel aufgeführt, alle Bestände gefüllt und die Differenz berechnet. Schau es dir an. Falls die Ergebnismenge nicht stimmen sollte, wäre es gut, wenn du die gewünschten Werte definieren könntest. An der zweiten Abfrage für die Summenbildung kommst du nicht drumherum, da man sonst Äpfel mit Birnen vermischt.

Gruß
Ralf