SELECT Feld zusammensetzen

[Boris]

Hallo, ich möchte folgendes erreichen und habe darüber nichts in Büchern und im Web gefunden:

In einer ID-Tabelle steht für jede ID u.a. eine Jahreszahl (ID.Years). In einer Data-Tabelle existieren für jede ID die Spalten Data.Year1, Data.Year2, DataYear3.

Nun möchte ich in einer Abfrage erreichen, dass für jede ID aus der ID-Tabelle der entsprechende Jahreswert (1, 2 oder 3) aus der Data-Tabelle angezeigt wird. Das Zusammensetzen eines Feldes aus Text und einem anderen Feld funktioniert aber leider nicht, z.B. so:

SELECT ID.ID, ID.Years, "Data.Year" & ID.Years as Ergebnis FROM ID LEFT JOIN Data ON ID.ID = Data.ID

Beispiel:
ID=1, Years=2 -> Ausgabe des Wertes in Data.Years2 in einer Ergebnispalte
ID=2, Years=1 -> Ausgabe des Wertes in Data.Years1 in einer Ergebnispalte

Bin um jeden Tipp dankbar.

Viele Grüße
Boris

[DF6GL]

Hallo,

ohne nach der Konzeption zu fragen:


Select ID.ID, ID.Years, Choose([years],[Year1],[year2],[year3]) as xxx From Data inner join ID on Data.ID = ID.ID

[Boris]

Hallo,
es gibt so gut wie keine Konzeption, da ich Zahlen, die über ein externes Programm in fest vorgegebene Access-Tabellen exportiert werden, auswerten möchte, und diese sind katastrophal aufgebaut. So muss ich aus der Tabelle Data in denen pro ID für verschiedene Typen Jahreswerte (Year1 bis Year30) stehen zum einen eine Summe aus allen Jahren bilden für Typ A und zum anderen den Wert des in ID.Years stehenden Jahres für Typ B ermitteln und daraus dann die Summe bilden (SummeA + WertB). Das ganze sieht jetzt so aus (das mit Choose funktioniert leider nur bis Jahr 28 , hätte vielleicht erwähnen sollen, dass es nicht nur 3 Jahre sind):

SELECT ID.ID, ID.Years, 
Data.Y1+Data.Y2+Data.Y3+Data.Y4+Data.Y5+Data.Y6+Data.Y7+Data.Y8+Data.Y9+Data.Y10+Data.Y11+Data.Y12+Data.Y13+Data.Y14+Data.Y15+Data.Y16+Data.Y17+Data.Y18+Data.Y19+Data.Y20+Data.Y21+Data.Y22+Data.Y23+Data.Y24+Data.Y25+Data.Y26+Data.Y27+Data.Y28+Data.Y29+Data.Y30 AS SummeA, Choose(ID.Years,Data_1.Y1,Data_1.Y2,Data_1.Y3,Data_1.Y4,Data_1.Y5,Data_1.Y6,Data_1.Y7,Data_1.Y8,Data_1.Y9,Data_1.Y10,Data_1.Y11,Data_1.Y12,Data_1.Y13,Data_1.Y14,Data_1.Y15,Data_1.Y16,Data_1.Y17,Data_1.Y18,Data_1.Y19,Data_1.Y20,Data_1.Y21,Data_1.Y22,Data_1.Y23,Data_1.Y24,Data_1.Y25,Data_1.Y26,Data_1.Y27,Data_1.Y28) AS WertB, SummeA+WertB AS Total
FROM (ID LEFT JOIN Data ON ID.ID = Data.ID) LEFT JOIN Data AS Data_1 ON ID.ID = Data_1.ID
WHERE (Data.Type)="A") AND (Data_1.Type)="B")

Habe nicht wirklich eine Ahnung von SQL und Access und frage mich deshalb, ob dies eine gute Lösung ist oder ob man da nicht lieber Hilfsabfragen dazwischen schalten sollte (wie würde sich das auf die Performance auswirken?). Und leider weiß ich auch nicht, wie ich das mit Choose + 30 Werten löse...? Die Möglichkeit den Feldnamen zusammen zu setzen besteht anscheinend nicht (Analog zu INDIRECT in Excel)??

Viele Grüße

[Shai]

Doch, die Lösung zum Zusammensetzen von Feldern existiert. Warum hast Du denn das eine Feld in Anführungsstriche gesetzt?
Ich versuche gerade, mir das vorzustellen, was Du da an Daten hast.
Tabelle ID: Spalte ID und Years
Tabelle Data, Spalten ID, Year1, Year2, Year3

Also z.B.
ID. Years
1  1
2  2
3  3

ID, Year1, Year2, Year3
1    1000  1500  2000
2    2000  1300  2000
3    1500  1900  1400

Mal abgesehen davon, dass ich die Struktur nicht gut finde, folgender Vorschlag:
SELECT ID.ID, Data.Year1 & ID.Years as DataYear WHERE ID.Years=1
UNION
SELECT ID.ID, Data.Year2 & ID.Years as DataYear WHERE ID.Years=2
UNION
SELECT ID.ID, Data.Year2 & ID.Years as DataYear WHERE ID.Years=3

Union Abfragen lassen sich nur von Hand in der SQL-Ansicht eingeben.

Was ich bei der Struktur der Daten nicht verstehe:
Warum überhaupt 2 Tabellen?
Warum nicht: Tabelle Years: Felder ID, Years, Year1, Year2, Year3

[Boris]

Hallo Shai,

vielen Dank für die Antwort. Zum besseren Verständnis erkläre ich nochmal die Struktur: Ich habe 2 nicht veränderbare vorgegebene Tabellen ID und Data. Auf diese soll eine Abfrage aufsetzen und folgendes Ergebnis liefern:

Tabelle ID:
ID  Years
1     14
2     12
3     30

Tabelle Data:
ID   Type    Y1    Y2  usw.  Y30
1        A    100  150           180
1        B    170  120           130
2        A    120  140           110
2        B    100  200           300
3        A      90  190           230
3        B    100  200           150

Die Abfrage soll ergeben:
ID  ID.Years   SummeA    WertB
1        14           5480        120
2        12           3380        210
3        30           6140        230

Bsp ID 1: SummeA ist die Summe von Y1 bis Y14 (Type A) und WertB ist der Wert von Y14 (Type B).

Meine aktuelle Lösung sieht so aus: IIf(ID.Years>=1,Data.Y1,0)+IIf(ID.Years>=2,Data.Y2,0)+ usw + IIf(ID.Years>=30,Data.Y30,0) USW AS SummeA, IIf(ID.Years=1,Data.Y1,0)+IIf(ID.Years=2,Data.Y2,0)+ usw + IIf(ID.Years=30,Data.Y30,0) AS WertB

Bin überfordert, finde diese Lösung nicht gerade optimal.

Das Feld habe ich als Beispiel zur Indirect-Funktion in Excel in Anführungsstriche gesetzt. In Excel würde INDIRECT("Data.Y" & ID.Years) als Ergbnis den Bezug Data.Y1 ergeben...

Viele Grüße

[oma]

Hallo Boris,

du könntest die Felder in einer kleinen Funktion zusammenrechen u. diese Funktion in einer Abfrage qryData einsetzte:

Code [Auswählen] Erweitern

SELECT ID.ID, ID.Years, Data.Type, Data.Y1, Data.Y2, Data.Y3, Data.Y4, Data.Y5, Data.Y6,
YearsSumme([ID.ID]) AS SummeA
FROM ID INNER JOIN Data ON ID.ID = Data.ID;

mit der Funktion:

Code [Auswählen] Erweitern

Public Function YearsSumme(kk As Long) As Long
Dim db As Database, rs As Recordset
Dim j As Long, k As Long, Felder(29)

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from qryData Where ID=" & kk)

Felder(0) = rs!Y1
Felder(1) = rs!Y2
Felder(2) = rs!Y3
Felder(3) = rs!Y4
Felder(4) = rs!Y5
Felder(5) = rs!Y6
...
j = rs!Years


For k = 0 To j - 1
    YearsSumme = YearsSumme + Felder(k)
Next
End Function


Gruß Oma

[oma]

Hallo Boris,

du wolltest ja auch noch WertB haben, Mit qryData:

Code [Auswählen] Erweitern

SELECT ID.ID, ID.Years, Type, Y1, Y2, Y3, Y4, Y5, Y6, ....,
IIf([Type]='A',SummeA([ID.ID]),WertB(ID.ID)) AS Berechnung
FROM ID INNER JOIN Data ON ID.ID = Data.ID;

Und:

Code [Auswählen] Erweitern


Option Compare Database
Option Explicit
Dim db As Database, rs As Recordset
Dim Felder(29)
_______________________________________
Public Function SummeA(kk As Long) As Long
Dim j As Long, i As Long, k as Long

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from qryData Where ID=" & kk)

For i = 0 To 29
   Felder(i) = rs("Y" & i + 1)
Next
j = rs!Years

For k = 0 To j - 1
   SummeA = SummeA + Felder(k)
Next
End Function

als auch im gleichem Modul

Code [Auswählen] Erweitern

Public Function WertB(kk As Long) As Long
Dim j As Long, i As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from qryData Where ID=" & kk & " And Type='B'")

For i = 0 To 29
   Felder(i) = rs("Y" & i + 1)
Next

j = rs!Years - 1
   WertB = Felder(j)
End Function

Probiers mal so!

Gruß Oma