Ich habe aber keinen blassen Schimmer, wie ich das umstellen soll...
Hier ist nochmal die komplette Abfrage:
SELECT ID,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_9
WHERE (FeldDefinitionenID = 146) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS letzt_Aend_am,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_8
WHERE (FeldDefinitionenID = 147) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS beauftr_von,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_7
WHERE (FeldDefinitionenID = 152) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Aktual_beauftr_am,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_6
WHERE (FeldDefinitionenID = 153) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Aktual_beauftr_von,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_5
WHERE (FeldDefinitionenID = 154) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Fehler_gemeld_am,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_4
WHERE (FeldDefinitionenID = 148) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Endfassg_Eing_am,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_3
WHERE (FeldDefinitionenID = 155) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Plan_freig_am,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_2
WHERE (FeldDefinitionenID = 158) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Plan_freig_von,
(SELECT Wert
FROM dbo.TBL_INVENTAR_DATEN AS TBL_INVENTAR_DATEN_1
WHERE (FeldDefinitionenID = 156) AND (InventarID = dbo.TBL_INVENTAR.ID)) AS Bemerkungen
FROM dbo.TBL_INVENTAR
Und für die "Werte" bei den FelddefinitionenID 147,153 und 156 soll eben nicht der "Wert" in der Spalte ausgegeben werden sondern der dazugehörige Nachname aus der Tabelle dbo.TBL_KONTAKTE!