ID als Text exportieren

[BananaDealer]

Hallo liebe Leute,

ich möchte folgendes Problem bei meinem Export von Access nach Excel beheben:

Verschiedene Eigenschaften sind in Accesstabellen aufgelistet (alle innerhalb einer Datei) und in einer anderen Access-Datei per Nachschlage-Assistent für ein Drop-Down hinterlegt (mit verknüpften Tabellen). Die Eigenschaften haben jeweils einen Namen und eine ID (Autowert). Diese ID ist auch der Primärschlüssel. Nun kann ich in Access problemlos die Textwerte anstatt der ID im Dropdown anzeigen lassen. Beim Export nach Excel werden aber die echten Werte, also die ID, exportiert. Gibt es eine Möglichkeit, anstatt der ID den Namen als Text exportieren zu lassen oder die ID in den passenden Text umzuwandeln (in Excel)? Natürlich könnte ich mit einer Schleife o. ä. in Excel VBA eine Konvertierung in die Namen als Text implementieren, bei aber ca. 70 verschiedenen Eigenschaften ist dies sehr umständlich.

Im Folgenden noch mein Code für den Export und das Abrufen von Abfragen (für die Lösung meines Problem evtl. nicht all zu erheblich). Achtung ist etwas länger:

Code: [Auswählen]

Sub AbfrageAccess()
   
   Dim dbe As Object
   Dim dbfile As String
   Dim i As Integer
   Dim par1 As String
   Dim par2 As String
   Dim parD1 As String
   Dim parD2 As String
   Dim marke1 As String
   Dim mess1 As String
   Dim db As Object
   Dim rs As Object
   Dim qdf As Object
   Dim parDate1 As Date
   Dim parDate2 As Date
   Dim p
   
   
   
   Dim x As Integer
   Dim y As Integer
   Dim z As Integer
   Dim Str1 As String
   Dim Str2 As String
   

  '__________________________________Abfrage pro DL__________________________
 
 
 
   par2 = InputBox("Welcher DL?")

   dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

  Set qdf = db.QueryDefs("AuswertungDLundMessung")
    qdf.Parameters![Welcher DL?] = par2

    Set rs = qdf.OpenRecordset()

    Worksheets("Auswertung").cells(4, 1).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close
   Set qdf = Nothing
 
 '_____________Abfrage über alle DL____________________
 
 dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

  Set qdf = db.QueryDefs("AuswertungGesamt")
    qdf.Parameters![Welcher DL?] = "*"

    Set rs = qdf.OpenRecordset()
   
    Worksheets("Auswertung").cells(4, 11).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close
   Set qdf = Nothing
 
 
 '_____________Abfrage Stichprobenmenge____________________
 
 
 dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

 parD1 = InputBox("Welches Starquartal?")
 parD2 = InputBox("Welches Endquartal?")
 marke1 = InputBox("Welche Marke?")

  Set qdf = db.QueryDefs("GesamtStichproben")
   
    qdf.Parameters![Startquartal] = parD1
    qdf.Parameters![Endquartal] = parD2
    qdf.Parameters![Welche Marke?] = marke1
    Set rs = qdf.OpenRecordset()


    Worksheets("Auswertung").cells(21, 19).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close: Set qdf = Nothing
 
 '________________________Abfrage gesamte Menge korrekt/inkorrekt_____________________
 
 'marke1 = InputBox("Welche Marke?")

   dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

  Set qdf = db.QueryDefs("GesamtKorrekt")
    qdf.Parameters![Welche Marke?] = marke1

   
    Set rs = qdf.OpenRecordset()

    Worksheets("Auswertung").cells(18, 21).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close
   Set qdf = Nothing
 
 
 
 '________________________Abfrage DL Menge korrekt/inkorrekt_____________________
 
 'marke1 = InputBox("Welche Marke?")
 'par1 = InputBox("Welcher DL?")

   dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

  Set qdf = db.QueryDefs("DLKorrekt")
    qdf.Parameters![Welcher DL?] = par2
    qdf.Parameters![Welche Marke?] = marke1

   
    Set rs = qdf.OpenRecordset()

    Worksheets("Auswertung").cells(4, 21).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close
   Set qdf = Nothing
   
   
   
   
   
   
   
   '________________________Abfrage Menge Fehler_____________________
 
 'parD1 = InputBox("Welches Starquartal?")
 'parD2 = InputBox("Welches Endquartal?")
 'marke1 = InputBox("Welche Marke?")

   dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)


  Set qdf = db.QueryDefs("FehlerStichprobe")
    qdf.Parameters![Startquartal] = parD1
    qdf.Parameters![Endquartal] = parD2
    qdf.Parameters![Welche Marke?] = marke1
   
    Set rs = qdf.OpenRecordset()

    Worksheets("Auswertung").cells(23, 19).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close
   Set qdf = Nothing

 
 '________________________Auswertung in Excel___________________________________________________________________________________
 
 
ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Add Key:=Range("B4:B1500" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Auswertung").Sort
        .SetRange Range(cells(4, 1), cells(1500, 5))
        .Header = xlGuess
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
 
 ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Add Key:=Range("K4:K1500" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Auswertung").Sort
        .SetRange Range(cells(4, 11), cells(1500, 13))
        .Header = xlGuess
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 

 
 '_______________Einfügen Werte für gewählten DL_________________
 
 z = 0
 x = 0
 
 While x < 5
 
 Do
 
 If z > 500 Then
 
 GoTo DoEnde
 
 End If
 z = z + 1
 
 If Worksheets("Auswertung").cells(z + 3, 3) = "1" Then
 Worksheets("Auswertung").cells(x + 4, 16) = Worksheets("Auswertung").cells(z + 3, 1)
 
 Exit Do
 End If
 Loop
 x = x + 1
 Wend
 
DoEnde:

x = 0
y = 0
z = 0

 For x = 1 To 5
 

 Str1 = Worksheets("Auswertung").cells(x + 3, 16).Value
 
 For y = 1 To 500
 
 Str2 = Worksheets("Auswertung").cells(y + 3, 1).Value

 If StrComp(Str1, Str2) = 0 And Worksheets("Auswertung").cells(y + 3, 3).Value = "1" And IsEmpty(Worksheets("Auswertung").cells(y + 3, 1)) = False Then
 
Worksheets("Auswertung").cells(x + 3, 17) = Worksheets("Auswertung").cells(y + 3, 2).Value
 
 End If
 Next
 Next
 
x = 0
y = 0
z = 0

 
 For x = 1 To 5
 

 Str1 = Worksheets("Auswertung").cells(x + 3, 16).Value
 
 For y = 1 To 500
 
 Str2 = Worksheets("Auswertung").cells(y + 3, 1).Value
 
 If StrComp(Str1, Str2) = 0 And Worksheets("Auswertung").cells(y + 3, 3).Value = "2" And IsEmpty(Worksheets("Auswertung").cells(y + 3, 1)) = False Then
 
Worksheets("Auswertung").cells(x + 3, 18) = Worksheets("Auswertung").cells(y + 3, 2).Value
 
 
 End If
 Next
 Next
 
 
x = 0
y = 0
z = 0

 
 For x = 1 To 5
 

 Str1 = Worksheets("Auswertung").cells(x + 3, 16).Value
 
 For y = 1 To 500
 
 Str2 = Worksheets("Auswertung").cells(y + 3, 1).Value

 If StrComp(Str1, Str2) = 0 And Worksheets("Auswertung").cells(y + 3, 3).Value = "3" And IsEmpty(Worksheets("Auswertung").cells(y + 3, 1)) = False Then
 
Worksheets("Auswertung").cells(x + 3, 19) = Worksheets("Auswertung").cells(y + 3, 2).Value
 
 End If
 Next
 Next
 
'_______________Einfügen Werte über alle DL_________________
 
x = 0
y = 0
z = 0

 
 For x = 1 To 5
 
 Str1 = Worksheets("Auswertung").cells(x + 13, 16).Value
 
 For y = 1 To 500
 
 Str2 = Worksheets("Auswertung").cells(y + 3, 12).Value
 
 If StrComp(Str1, Str2) = 0 And Worksheets("Auswertung").cells(y + 3, 13).Value = "1" And IsEmpty(Worksheets("Auswertung").cells(y + 3, 12)) = False Then
 
Worksheets("Auswertung").cells(x + 13, 17) = Worksheets("Auswertung").cells(y + 3, 11).Value
 
 End If
 Next
 Next

 
 x = 0
y = 0
z = 0

 
 For x = 1 To 5
 

 Str1 = Worksheets("Auswertung").cells(x + 13, 16).Value
 
 For y = 1 To 500
 
 Str2 = Worksheets("Auswertung").cells(y + 3, 12).Value
 
 If StrComp(Str1, Str2) = 0 And Worksheets("Auswertung").cells(y + 3, 13).Value = "2" And IsEmpty(Worksheets("Auswertung").cells(y + 3, 12)) = False Then
 
Worksheets("Auswertung").cells(x + 13, 18) = Worksheets("Auswertung").cells(y + 3, 11).Value
 
 End If
 Next
 Next
 
 x = 0
y = 0
z = 0

 
 For x = 1 To 5
 

 Str1 = Worksheets("Auswertung").cells(x + 13, 16).Value
 
 For y = 1 To 500
 
 Str2 = Worksheets("Auswertung").cells(y + 3, 12).Value
 
 If StrComp(Str1, Str2) = 0 And Worksheets("Auswertung").cells(y + 3, 13).Value = "3" And IsEmpty(Worksheets("Auswertung").cells(y + 3, 12)) = False Then
 
Worksheets("Auswertung").cells(x + 13, 19) = Worksheets("Auswertung").cells(y + 3, 11).Value
 
 End If
 Next
 Next
 
   
End Sub

Vielen Dank und beste Grüße

BananaDealer

[Lachtaube]

Du musst in der zu exportierenden Abfrage die Tabellen mit den Klartextnamen mit der/den anderen Tabelle(n) (bei optionalen Werten als Outer Join) verknüpfen und die Klartextnamen in die Feldliste aufnehmen.

[BananaDealer]

Du musst in der zu exportierenden Abfrage die Tabellen mit den Klartextnamen mit der/den anderen Tabelle(n) (bei optionalen Werten als Outer Join) verknüpfen und die Klartextnamen in die Feldliste aufnehmen.

Hallo Lachtaube, danke für deine Antwort! Leider weiß ich nun nicht so ganz genau, wie ich nach deinem Ratschlag vorgehen muss. Die Texte der Namen sind im Nachschlage-Assistenten anstatt der ID verknüpft. Trotzdem hat das Feld natürlich den Wert der ID und nicht des Textes.

[Lachtaube]

Was ist daran schwierig zu verstehen? Die Klartextnamen stehen doch in den Lookup-Tabellen. Statt

Code: [Auswählen]

SELECT ..., h.NachschlageId
FROM   Haupttabelle As h;verwendest Du

Code: [Auswählen]

SELECT ..., n.KlartextFeldnameAusNachschlagetabelle
FROM   HauptTabelle AS h
       LEFT JOIN Nachschlagetabelle AS n
              ON h.NachschlageId = n.ID;

[BananaDealer]

Was ist daran schwierig zu verstehen? Die Klartextnamen stehen doch in den Lookup-Tabellen. Statt

Code: [Auswählen]

SELECT ..., h.NachschlageId
FROM   Haupttabelle As h;verwendest Du

Code: [Auswählen]

SELECT ..., n.KlartextFeldnameAusNachschlagetabelle
FROM   HauptTabelle AS h
       LEFT JOIN Nachschlagetabelle AS n
              ON h.NachschlageId = n.ID;

Ich habe wie gesagt alles was Access angeht selbst beigebracht und für meine Aufgabe aus Zeitgründen auch nur die Dinge, die ich bis dahin benötigt habe. Daher kenne ich viele auch einfache Dinge noch nicht.   

Die Drop-Downs sind in einer Frontend im Formular, wo ich den Code auch so verändert habe (im Code für die Datensatzherkunft). Nun bekomme ich aber mehrere Parameterabfragen angezeigt, sobald ich Etwas auswählen möchte.

Ausgangscode:

Code: [Auswählen]

SELECT Fachthema.ID_Fehler, Fachthema.Fachthema
FROM Fachthema
ORDER BY Fachthema.Fachthema;

Bin nicht sicher was ich jetzt als Nachschlagetabelle nehme, wenn Fachthema schon die Haupttabelle ist (ausgehend vom Anfangscode) und ob NachschlageID und ID etwas verschiedenes sind.

Code: [Auswählen]

SELECT Fachthema.ID_Fehler, n.Fachthema.Fachthema
FROM Fachthema AS h
LEFT JOIN Fachthema AS n
ON h.Fachthema.ID_Fehler = n.Fachthema.ID_Fehler
ORDER BY Fachthema.Fachthema;


[BananaDealer]

Ich war im falschen Menü.
Also jetzt in der Tabelle, wo alle Daten zusammenkommen die Datensatzherkunft der Eigenschaften bearbeiten. Ich probiere es nochmal.

[BananaDealer]

Ich bekomme immer noch ein Feld der Parameterabfrage. Evtl. habe ich noch falsche Eingaben für
h.NachschlageId, n.ID und die Haupttabelle

[Lachtaube]

Erkläre doch bitte einmal die verwendete Tabellen-Struktur(en) für den Export - sonst reden wir gar aneinander vorbei. Dann zeige bitte auch den bisherigen SQL-Abfragetext und berichte, wo die ID (also der Zahlenwert) ausgegeben wird und was stattdessen dort erscheinen soll und wo diese Information hinterlegt ist.

[BananaDealer]

Also, die gesamte Struktur besteht aus drei Access-Dateien und einer Excel-Datei:

1) Access-Basis-Datei: Tabellen mit den Eigenschaften, welche eine ID und einen Namen als Text haben

2) Access-Frontend: Formular, welches aus Dropdown-Menüs die Auswahl der Eigenschaften aus der Basis-Datei erlaubt (verknüpfte Tabellen)

3) Access-Backend: Enthält die verknüpften Tabellen aus der Basis-Datei und eine Haupttabelle in der alle Datensätze sind (werden durch das Formular gefüttert) sowie verschiedene Abfragen auf die ich gleich eingehe

4) Excel-Datei welche durch Makro die Abfragen aus der Backend ausführt und die Werte aus diesen Abfragen der Backend importiert
Problem: bei diesem Import werden nun die IDs importiert und nicht wie gewünscht der Name als Text

Folgende Abfragen werden vom Excel-Makro importiert, in denen das Problem besteht:

Code: [Auswählen]

SELECT [AuswertungDaten pro DL].Fehlerschwerpunkt, Count([AuswertungDaten pro DL].Fehlerschwerpunkt) AS AnzahlvonFehlerschwerpunkt, [AuswertungDaten pro DL].Messung, [AuswertungDaten pro DL].Dienstleister, [AuswertungDaten pro DL].Quartal
FROM [AuswertungDaten pro DL]
GROUP BY [AuswertungDaten pro DL].Fehlerschwerpunkt, [AuswertungDaten pro DL].Messung, [AuswertungDaten pro DL].Dienstleister, [AuswertungDaten pro DL].Quartal
HAVING ((([AuswertungDaten pro DL].Dienstleister) Like [Welcher DL?]))
ORDER BY Count([AuswertungDaten pro DL].Fehlerschwerpunkt);


UND

Code: [Auswählen]

SELECT Sum(AuswertungDLundMessung.AnzahlvonFehlerschwerpunkt) AS SummevonAnzahlvonFehlerschwerpunkt, AuswertungDLundMessung.Fehlerschwerpunkt, AuswertungDLundMessung.Messung, AuswertungDLundMessung.Quartal
FROM AuswertungDLundMessung
GROUP BY AuswertungDLundMessung.Fehlerschwerpunkt, AuswertungDLundMessung.Messung, AuswertungDLundMessung.Quartal
ORDER BY AuswertungDLundMessung.Messung DESC;

Dazu kommt das Excel-Makro vom Eröffnungspost siehe oben.


Code Nachschlagen in der Haupttabelle der Backend (Beispiel für eine Eigenschaft von mehreren):

Code: [Auswählen]

SELECT Dienstleister.ID_DL, Dienstleister.Dienstleister
FROM Dienstleister;
Vielen Dank!

[Lachtaube]

Du musst die Dienstleister-Tabelle mit der Haupt-Tabelle verknüpfen. Also beide Tabellen in die Abfrage aufnehmen und zwischen Haupt- und Dienstleister-Tabelle durch Drag & Drop über das Dienstleister-Feld und der ID_DL eine Verbindungslinie herstellen und dann das Feld Dienstleister aus der Dienstleister-Tabelle in der Abfrage verwenden.

Die Basistabelle - das dürfte ja wohl kaum die AuswertungDaten pro DL sein - ist natürlich nicht erklärt, weshalb Du das selbst zusammenfrickeln musst.

Hier mal eine Anpassung für die Abfrage:

Code: [Auswählen]

SELECT A.Fehlerschwerpunkt,
       Count( * ) AS AnzahlFehlerschwerpunkte,
       A.Messung,
       A.Dienstleister,
       A.Quartal
FROM   [AuswertungDaten pro DL] AS A
WHERE  (( ( A.Dienstleister ) LIKE [Welcher DL?] ))
GROUP  BY A.Fehlerschwerpunkt, A.Messung, A.Dienstleister, A.Quartal
ORDER  BY 2;
PS: Nachschlagefelder taugen einfach nichts in Tabellen oder Abfragen. Ich würde auch keine Leerzeichen in Objektnamen verwenden.

[BananaDealer]

Du musst die Dienstleister-Tabelle mit der Haupt-Tabelle verknüpfen. Also beide Tabellen in die Abfrage aufnehmen und zwischen Haupt- und Dienstleister-Tabelle durch Drag & Drop über das Dienstleister-Feld und der ID_DL eine Verbindungslinie herstellen und dann das Feld Dienstleister aus der Dienstleister-Tabelle in der Abfrage verwenden.

Die Basistabelle - das dürfte ja wohl kaum die AuswertungDaten pro DL sein - ist natürlich nicht erklärt, weshalb Du das selbst zusammenfrickeln musst.

Hier mal eine Anpassung für die Abfrage:

Code: [Auswählen]

SELECT A.Fehlerschwerpunkt,
       Count( * ) AS AnzahlFehlerschwerpunkte,
       A.Messung,
       A.Dienstleister,
       A.Quartal
FROM   [AuswertungDaten pro DL] AS A
WHERE  (( ( A.Dienstleister ) LIKE [Welcher DL?] ))
GROUP  BY A.Fehlerschwerpunkt, A.Messung, A.Dienstleister, A.Quartal
ORDER  BY 2;
PS: Nachschlagefelder taugen einfach nichts in Tabellen oder Abfragen. Ich würde auch keine Leerzeichen in Objektnamen verwenden.

Ich probiere es mal so.
Die Basis-Datei ist eine andere Access Datei mit 7 einzelnen Tabellen für

Dienstleister
Fachthema        ->Das Fachthema beschreibt den Fehlerschwerpunkt.
IT_System
Marke
Messung
QM-Kategorie
Teamleiter

und jede Tabelle hat zwei Spalten: ID und dazu der Name der Beschreibung: 1 | Erstmessung
Alle Nachschlage-Abfragen beziehen sich auf diese 7 Tabellen.


Diese 7 Tabellen sind in der Access Backend verknüpft, wo dort über das Formular die Tabelle Daten pro DL gefüttert wird. Diese ist dann siehe Codes oben die Grundlage für die Abfragen.

EDIT: Die erste Abfrage wertet aus der Tabelle "AuswertungDaten pro DL aus". In deinem Code ist dann "A." genau diese Tabelle, wo die Daten gezählt werden oder ist "A." die Tabelle, in der die Liste der Eigenschaften liegt? Das Fachthema beschreibt den Fehlerschwerpunkt.

[BananaDealer]

Hier nochmal Screens von der Struktur:

Die Anfügeabfragen erstellen aus der Tabelle "Daten pro DL" die Tabelle "AuswertungDaten pro DL" in der die Fehlerschwerpunkte 1 und 2 in einer Spalten zusammengefügt werden (zum Auswerten).

[Lachtaube]

Bitte keine kompletten Beiträge zitieren.

A ist hier als ein Alias [AuswertungDaten pro DL] AS A definiert, um nicht 1000x das längliche [AuswertungDaten pro DL] zu verwenden. Nicht mehr und nicht weniger.

Hast Du nun versucht, die Abfrage entsprechend meines Hinweises um- oder neuzugestalten?

[BananaDealer]

Entschuldige das Zitieren des ganzen Beitrags. Also die Abfrage funktioniert jetzt, gibt aber immer noch die IDs beim Export aus.

[Lachtaube]

Dann wäre das Schritt 2:

Code: [Auswählen]

SELECT A.Fehlerschwerpunkt,
       Count(*) AS AnzahlFehlerschwerpunkte,
       A.Messung,
       S.Dienstleister,
       A.Quartal
FROM   [AuswertungDaten pro DL] AS A
       INNER JOIN Dienstleister AS D
               ON A.Dienstleister = D.ID_DL
WHERE  (( ( A.Dienstleister ) LIKE [Welcher DL?] ))
GROUP  BY A.Fehlerschwerpunkt, A.Messung, D.Dienstleister, A.Quartal
ORDER  BY 2;