PDA

Vollständige Version anzeigen : Komplexer Ausdruck


Wolfgang Biedermann
24.10.2011, 16:59
Hallo zusammen,

in meine DB werden aus einer csv-Datei Daten importiert. Es gibt einen sog. "Buchungsgrund" (VA), bestehend aus Zahlen, den ich mittels einer Abfrage in "Schrift" umwandeln will, also z. B.:

VA = 200 -> Anzeige im Bericht: "NEU"
VA = 300 -> Anzeige im Bericht: "BESTAND"
VA = 330 -> Anzeige im Bericht: "BESTAND ALT"
VA = 350 -> Anzeige im Bericht: "BESTAND NEU"

usw. usw.

Das Problem ist, das es ungefähr 50 verschiedene "VA-Gründe" gibt, und in einer Abfrage

"=Wenn([VA]="200;"NEU";Wenn([VA]="300;"BESTAND";xxx;usw."

gibt Access auf von wegen "Ausdruck zu komplex".

Wie "umgehe" ich das am besten?

Danke.

Gruß
:) Wolfgang

zra8886
24.10.2011, 17:09
Hallo Wolfgang:

du legst dir eine Tabelle tbl_Buchungsgrund an:

<b><em>Tbl_Buchungsgrund</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >ID</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >NR</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >200</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Neu</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >300</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >330</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand&nbsp;alt</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >350</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand&nbsp;neu</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.42</a><br/><br/>

Über deine Abfrage kannst du dann deine "Import.csv"-Tabelle mit dieser verknüpfen.

mfg
Roland

FW
24.10.2011, 17:15
... wieviele Zahlen/Texte werden denn insgesamt unterschieden?

zra8886
24.10.2011, 17:16
... wieviele Zahlen/Texte werden denn insgesamt unterschieden?

...Das Problem ist, das es ungefähr 50 verschiedene "VA-Gründe" gibt,...

mfg
Roland

FW
24.10.2011, 17:20
... dann hast Du also 49 geschachtelte WENN-Funktionen?

zra8886
24.10.2011, 17:25
Hallo FW,

;) ich nicht, aber der Wolfgang (siehe #1)

wenn ich nicht gemeint war, Beitrag einfach ingorieren.
Danke

mfg
Roland

Wolfgang Biedermann
24.10.2011, 17:28
Hallo Roland, hallo FW,

danke für die schnellen Antworten und Hilfen.

@ FW (ich bin gemeint, oder?)

... dann hast Du also 49 geschachtelte WENN-Funktionen?

Ja, genau, und ich denke, dass der Vorschlag von Roland OK ist, oder?

Gruß

:) Wolfgang

FW
24.10.2011, 17:31
... das ist ok, alternativ könntest Du auch eine VB-Funktion schreiben (SELECT CASE), aber eine Tabelle ist wartbarer...

zra8886
24.10.2011, 17:37
Hallo Wolfgang,

(:) und ganz speziell für diejenigen, die ein Beispiel benötigen; also ich denke mal keiner von euch Beiden)
1:

<b><em>tbl_csvImport</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >ID</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >VA</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >weitereFelder</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >200</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >text</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >200</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >text1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >300</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >text2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >330</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >5</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >350</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text4</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >6</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >400</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text5</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.42</a><br/><br/>
2:

<b><em>tbl_Buchungsgrund</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >ID</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >Nr</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >Beschreibung</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >200</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Neu</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >300</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >330</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand&nbsp;alt</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >350</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand&nbsp;neu</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.42</a><br/><br/>
3:
Abfrage:
SELECT tbl_csvImport.VA, tbl_Buchungsgrund.Beschreibung, tbl_csvImport.weitereFelder
FROM tbl_csvImport LEFT JOIN tbl_Buchungsgrund ON tbl_csvImport.VA = tbl_Buchungsgrund.Nr;

Ergebnis:

<b><em>abfr_Buchungsgrundzuweisen</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:117px;" /><col style="width:117px;" /><col style="width:117px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >VA</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >Beschreibung</td><td style="color:#000000; background-color:#C0C0C0; text-align:center; " >weitereFelder</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >200</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Neu</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >text</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >200</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Neu</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >text1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >300</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >text2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >330</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand&nbsp;alt</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >350</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Bestand&nbsp;neu</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text4</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >400</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Text5</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.42</a><br/><br/>
Bei der 400 erscheint noch nichts, weil 400 noch nicht in der Tabelle "tbl_Buchungsgrund" eingetragen ist.

Weitere Felder kannst du ja selbst noch dazunehmen.
mfg
Roland