|
Transponieren von Tabellen und Abfragen
|
| Autor |
Nachricht |
stpimi
Moderator Access
Verfasst am:
21. Jul 2005, 09:44
Rufname:
Wohnort: Graz
|
|
| |
|
Manchmal wird gewünscht, für einen Bericht die Datensätze nebeneinander und die Spalten untereinander zu haben. Dazu habe ich in einem Newsletter von www.smarttools.de vom Mai 2004 folgende VBA-Lösung mit Hilfstabelle gefunden:
| Zitat: | Transponieren von Tabellen und Abfragen
Versionen: Access 97, 2000, 2002/XP und 2003
Fuer viele Auswertungen waere es sinnvoll, Tabellen oder Abfragen zu transponieren, so dass die Feldnamen zeilenweise
und die Feldinhalte spaltenweise ausgegeben werden. Access selbst stellt dafür leider keine interne Funktion bereit,
aber mit Hilfe einer kleinen VBA-Funktion, die wir Ihnen in diesem Tipp der Woche vorstellen, laesst sich diese Aufgabe
schnell erledigen:- Legen Sie ein neues Modul an und speichern Sie es unter dem Namen "modTranspose".
- Waehlen Sie das Menue EXTRAS-VERWEISE an und setzen Sie einen Verweis auf "Microsoft DAO 3.5x Object Library"
(Access 97) beziehungsweise auf "Microsoft DAO 3.6x Object Library" (ab Access 2000).
- Geben Sie die folgende Funktion ein:
| Code: | Function TransposeTable(strSourceObj As String, _
strTranspTable As String) As Boolean
Dim db As DAO.Database
Dim rsBasis As DAO.Recordset
Dim rsTranspose As DAO.Recordset
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim I As Integer, J As Integer
Dim intNumRecs As Integer
Dim intNumFields As Integer
TransposeTable = False 'Default-Rueckmeldung
'Recordset für Basis-Tabelle initialisieren
Set db = CurrentDb()
Set rsBasis = db.OpenRecordset(strSourceObj)
rsBasis.MoveLast
'1. Spalte für Feldnamen
intNumRecs = rsBasis.RecordCount
If intNumRecs > 254 Then
Beep
MsgBox "Tabelle " & strSourceObj & " beinhaltet mehr als 254 " & _
"Datensaetze...", vbOKOnly + vbExclamation, "!!! Problem !!!"
rsBasis.Close
Exit Function
End If
intNumFields = rsBasis.Fields.Count - 1
For I = 0 To intNumFields
If rsBasis.Fields(I).Type = dbMemo Or _
rsBasis.Fields(I).Type = dbLongBinary Then
Beep
MsgBox "Tabelle " & strSourceObj & " beinhaltet spezielle " & _
"Felder, " & "die nicht komplett übernommen werden " & _
"können...", vbOKOnly + vbInformation, "!!! Hinweis !!!"
End If
Next I
'Ziel-Tabelle anlegen/loeschen...
DoCmd.Hourglass True
DoEvents
On Error Resume Next
Set tdfNewDef = db.TableDefs(strTranspTable)
If Err = 0 Then 'Tabelle ist vorhanden, loeschen
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, strTranspTable
DoCmd.SetWarnings True
End If
Set tdfNewDef = db.CreateTableDef(strTranspTable)
For I = 0 To rsBasis.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(I + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next I
Err = 0
db.TableDefs.Append tdfNewDef
If Err <> 0 Then
DoCmd.Hourglass False
Beep
MsgBox "Tabelle " & strTranspTable & " konnte nicht angelegt " & _
"werden..." & vbCrLf & vbCrLf & "Fehler: " & _
CStr(Err.Number) & "/" & Err.Description, _
vbOKOnly + vbExclamation, "!!! Probblem !!!"
rsBasis.Close
Exit Function
End If
'Recordset für Ziel-Tabelle initialisieren
'1. Spalte mit Feldnamen fuellen
Set rsTranspose = db.OpenRecordset(strTranspTable)
For I = 0 To rsBasis.Fields.Count - 1
With rsTranspose
.AddNew
.Fields(0) = rsBasis.Fields(I).Name
.Update
End With
Next I
'ab 2. Spalte mit Daten fuellen
rsBasis.MoveFirst
rsTranspose.MoveFirst
For J = 0 To rsBasis.Fields.Count - 1
For I = 1 To rsTranspose.Fields.Count - 1
With rsTranspose
.Edit
.Fields(I) = rsBasis.Fields(J)
rsBasis.MoveNext
.Update
End With
Next I
rsBasis.MoveFirst
rsTranspose.MoveNext
Next J
'und fertig...
TransposeTable = True
rsBasis.Close
rsTranspose.Close
DoCmd.Hourglass False
End Function |
- Speichern Sie das Modul.
Die Funktion "TransposeTable()" erwartet als ersten Parameter den Namen einer Tabelle oder einer Abfrage und als
zweiten Parameter den Namen einer Tabelle, in der das transponierte Ergebnis abgelegt werden soll.
Das Funktionsergebnis ist "True", wenn die Tabelle/Abfrage erfolgreich in die angegebene Zieltabelle geschrieben wurde.
Andernfalls wird "False" zurueckgemeldet.
Zur Arbeitsweise der Funktion "TransposeTable()": Da beim Transponieren die normalerweise zeilenweise angeordneten
Datensaetze spaltenweise ausgeben werden und somit pro Datensatz jeweils ein Feld noetig ist, können nur Tabellen
oder Abfragen transponiert werden, die maximal 254 Datensaetze liefern. Ein Feld ist standardmaessig für die
Feldnamen in der 1. Spalte reserviert, so dass nur besagte 254 Datensaetze transponiert werden können. Grund für
diese Einschraenkung ist Access, das pro Tabelle nicht mehr als 255 Felder erlaubt. Spezialfelder wie Memo- oder OLE-
Felder können nicht transponiert werden, da eine Transponierung in der Ziel-Tabelle nur Felder vom Datentyp
"Text" zulaesst.
Im ersten Schritt pruefen wir also die Quell-Tabelle/-Abfrage daraufhin, ob sie mehr als 255 Datensaetze umfasst
und ob sie spezielle Felder (Memo, OLE) beinhaltet, die nicht transponiert werden können. Wenn Sie eine Tabelle
oder Abfrage mit mehr als 254 Datensaetzen im Ergebnis zu transponieren versuchen, zeigt die Funktion eine
entsprechende Fehlermeldung an und bricht den Vorgang ab.
Reichen 254 Datensaetze für Ihre Anforderungen nicht aus, müssen Sie gegebenenfalls den Datenbestand über mehrere
Abfragen und den Einsatz entsprechender Kriterien auf jeweils maximal 254 Datensaetze aufteilen und jede Abfrage
einzeln transponieren. Auf Spezialfelder macht die Funktion mit einer entsprechenden Warnmeldung aufmerksam und
ignoriert diese Felder beim Transponieren. Moechten Sie die Warnmeldung vermeiden, erstellen Sie eine Abfrage, die nur
die "normalen" Felder (Text, Zahl, Datum/Zeit, Ja/Nein) der Tabelle auswaehlt.
Anschliessend geht es daran, die Ziel-Tabelle anzulegen. Ist diese vorhanden, wird sie zunaechst automatisch geloescht
und komplett neu angelegt, um sicherzustellen, dass sie korrekte Ergebnisse aufnehmen kann. Um die sonst zeilenweise
angeordneten Datensaetze spaltenweise ausgeben zu können, definieren wir danach für die Ziel-Tabelle zunaechst ein
zusaetzliches Feld, das die Feldnamen in der 1. Spalte anzeigt. Anschliessend wird pro Datensatz ein weiteres Feld
angelegt. Ueber die TableDefs-Auflistung der Datenbank fügen wir dann die Definition der für die spaltenweise
Wiedergabe der Datensaetze notwendigen Felder hinzu.
Anschliessend legen wir pro Feld einen Datensatz für die spaltenweise Ausgabe an und übertragen dabei die Feldnamen
des Ausgangs-Objektes in die 1. Spalte der Ziel-Tabelle. In einer Schleife fuellen wir dann schliesslich die Datensaetze
ab der 2. Spalte mit den Daten aus den Datensaetzen des Basis-Objektes.
Bitte beachten Sie beim Einsatz dieser Loesung noch folgendes: Wenn die Ziel-Tabelle vorhanden ist, wird sie
über die Funktion "TransposeTable()" automatisch ohne Rueckfrage geloescht! Sollten Sie die Inhalte noch
benoetigen, benennen Sie sie vorher einfach im Datenbankfenster um. Die Transponierung kann je nach Umfang
des Datenbestandes geraume Zeit in Anspruch nehmen. Es können durchaus einige Minuten vergehen, bis alle Daten umgesetzt sind! |
_________________
Lg, Michael
Dein Feedback hilft auch anderen - vergiß es nicht!
|
|
Willi Wipp
Moderator
Verfasst am:
21. Apr 2006, 09:59
Rufname:
Wohnort: Raum Wiesbaden
|
| |
| |
|
Hi stpimi,
schoene Loesung!
Nachfragen zum Thema bitte hier Transponieren von Tabellen und Abfragen {Nachgefragt} stellen.
_________________
Eine kurze Rueckmeldung waere nett
SL Willi Wipp
(Anleitung fuer das Anhaengen von Dateien: Klicke links auf [www], Gaeste muessen sich dafuer anmelden)
|
|
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen
|
| |
|
|
| Verwandte Themen |
| Forum / Themen |
Antworten |
Autor |
Aufrufe |
Letzter Beitrag |
 |
Access Tabellen & Abfragen: "vlookup" Funktion in Access Abfragen |
4 |
MP_Trixi |
223 |
09. März 2010, 10:59
MP_Trixi  |
|
Access Programmierung / VBA: Tabellen verknüpfen mit kontrollbutton |
25 |
Snowflake |
420 |
15. Mai 2009, 10:51
Snowflake |
|
Access Tabellen & Abfragen: Anzahl(*)-Abfragen zusammenfügen |
3 |
Janblablub |
108 |
18. Apr 2009, 11:54
Willi Wipp |
|
Access Formulare: Datenherkunft: Gleichzeitige Angabe von 2 Tabellen |
2 |
Ugeen |
109 |
04. Dez 2008, 13:27
Ugeen |
|
Access Hilfe: Personendatenbank erstellen - Aufbau, Beziehungen, Abfragen |
1 |
eineFrage |
1053 |
03. Nov 2008, 11:09
Marmeladenglas |
|
Access Tabellen & Abfragen: Werte aus zwei Abfragen zusammen zählen |
11 |
SunShine080 |
413 |
12. Sep 2008, 22:19
Willi Wipp |
|
Access Programmierung / VBA: Tabellenname darf nicht den vorhandenen Tabellen entsprechen |
3 |
Spongi81 |
321 |
25. Sep 2007, 16:44
Willi Wipp |
|
Access Tabellen & Abfragen: Daten aus Pivot-Tabelle abfragen |
1 |
Kall |
408 |
02. Mai 2007, 16:22
Willi Wipp |
|
Access Programmierung / VBA: Zwei aufeinander aufbauende Abfragen in VBA einbinden. |
1 |
Pride |
518 |
04. Apr 2007, 21:16
Nouba |
|
Access Programmierung / VBA: Kopieren von Datensätzen + in Zieldatensatz springen |
18 |
Tutench |
1221 |
04. März 2007, 01:47
Willi Wipp |
|
Access Formulare: Formular erstellen, Daten aus zwei Tabellen |
1 |
anfängerin |
2454 |
19. Okt 2006, 07:44
stpimi |
|
Access Programmierung / VBA: aktuellen Datensatz abfragen |
6 |
schaedel |
3584 |
13. Jul 2006, 14:40
schaedel |
| |
|
