Abfrage 'drehen'

[Hondo]

Hallo,
habe folgende Situation: 2 Tabellen dbo_booking und dbo_booking_rooms 1:n verbunden Über Felder ID->RoomID

Code [Auswählen] Erweitern

SELECT dbo_booking_rooms.BookingID, dbo_rooms.room_name
FROM dbo_rooms INNER JOIN dbo_booking_rooms ON dbo_rooms.ID = dbo_booking_rooms.RoomID;
Abfrage liefert Buchungsnummer und Zimmernummer(n):

BookingID	room_name
17	Zimmer 101
21	Zimmer 201
18	Zimmer 203
18	Zimmer 204

Für eine Ansicht bräuchte ich eine neue Abfrage die mir folgende Daten liefert:

BookingID	room_name1	room_name2	room_name3	room_name4	room_name5
17	Zimmer 101
21		Zimmer 201
18				Zimmer 203	Zimmer 204

Ich möchte das aber als Abfrage machen und nicht eine neue Tabelle erstellen. Doch irgendwie find ich grad den Dreh nicht dass es mir die Felder füllt.
Gruß Andreas

[Hondo]

Hallo,
habe mich mangels besseren Wissens doch für eine Tabellenlösung entschieden, und schreibe die Daten in eine temp. Datenbank/temp. Tabelle die eingebunden wird. Dazu habe ich eine Lösung von Norbert Uba verwendet und etwas abgeändert: http://www.office-loesung.de/ftopic161417_0_0_asc.php

Code [Auswählen] Erweitern

Public Sub TransposeRooms()
    Dim strSQL As String
    Dim mBookingID As Long

    On Error GoTo TransposeRooms_Error

    If TmpTableInTmpDb("tmp_RoomNames", "dbo_RoomNames", "dbo_RoomNames") Then

        strSQL = "SELECT dbo_booking_rooms.BookingID, dbo_rooms.ID, dbo_rooms.room_name " & _
                 "FROM dbo_rooms INNER JOIN dbo_booking_rooms ON dbo_rooms.ID = dbo_booking_rooms.RoomID " & _
                 "ORDER BY dbo_booking_rooms.BookingID, dbo_rooms.room_name"

        With CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
            Do While Not .EOF
                If mBookingID <> !BookingID Then
                    CurrentDb.Execute "Insert into dbo_RoomNames (bookingID, roomname" & !ID & ") Values (" & !BookingID & ", '" & !room_name & "')"
                Else
                    CurrentDb.Execute "Update dbo_RoomNames set roomname" & !ID & "='" & !room_name & "' Where bookingID=" & mBookingID
                End If
                mBookingID = !BookingID
                .MoveNext
            Loop
        End With
    End If

    On Error GoTo 0
    Exit Sub

TransposeRooms_Error:
    'ErrHandler
End Sub

Funktioniert ganz gut, vor Aufruf der benötigten Ansicht wird jetzt immer TransposeRooms aufgerufen.
Gruß Andreas

PS: falls das doch mittels Query gehen sollte würde mich das schon interessieren.

[ebs17]

Die Darstellung lässt sich über eine Kreuztabellenabfrage erzeugen. In der Datenbasis dazu müsste man die Zuordnung roomX-Zimmername vornehmen. Das Wie ist nach Deiner Darstellung nicht so offensichtlich.

[PhilS]

Da du scheinbar einen SQL Server als Backend verwendest, hier eine T-SQL Lösung."room_data" ist dabei durch deine Ausgangsabfrage zu ersetzen.

Code (TSQL) [Auswählen] Erweitern

SELECT *
FROM   
(
    SELECT    BookingID
            ,room_name
            ,'room_name' + convert(varchar,ROW_NUMBER ( )
                                          OVER ( ORDER BY room_name ASC) ) AS Header
        FROM room_data
     ) rd 
PIVOT 
( 
MIN (room_name) 
FOR Header IN 
( [room_name1],[room_name2],[room_name3],[room_name4],[room_name5] ) 
) AS pvt 
ORDER BY pvt.BookingID;
Was hier fehlt ist die Zuordnung von room_name zu deinen room_name1, 2, 3 Überschriften. Ich habe einfach mal durchnummeriert, aber das weicht bei deinem room_name3 ab.

[Hondo]

Hallo Phil,
nein, ist eine MySQL-Datenbank - die aber für die Entwicklung des Access-Frontends momentan in ein Access-Backend portiert wurde.
Soll auch später im Betrieb auf MySQL laufen plus eine Hand von Tabellen die nur für das Access-Frontend benötigt werden.
Später soll auch ein PHP-Frontend dazu kommen.
Daher kann ich leider mit deiner Lösung nicht viel anfangen.
Aber danke dafür.

Die ID-Nummern der Zimmer stehen in dbo_booking_rooms.RoomID aus denen sich der neue Feldname bilden könnte.

Gruß Andreas

Btw: arbeite jetzt seit längerem wieder mal mit Ivercy, was macht man denn da mit dem Access-Backend? Die BackEnd-Links sind ja im Frontend eingecheckt, genügt das?

[PhilS]

nein, ist eine MySQL-Datenbank - die aber für die Entwicklung des Access-Frontends momentan in ein Access-Backend portiert wurde.
Soll auch später im Betrieb auf MySQL laufen plus eine Hand von Tabellen die nur für das Access-Frontend benötigt werden.
Später soll auch ein PHP-Frontend dazu kommen.

Ich halte es nicht für die beste Idee, für die Entwicklung das MySQL-Backend durch eine Access-BE zu ersetzen.
1. Können dir dabei einige Probleme entgehen, die du später in der Produktivumgebung haben könntest.
2. Bist du dann gezwungen den kleinsten gemeinsamen Nenner beider Systeme zu verwenden, anstatt die Features eines System wirklich ausnutzen zu können.

Aber davon mal abgesehen...
Du solltest in Access mit einer Kreuztabellenabfrage ein ähnliches Ergebnis erzielen können.
Auch mit Standard-SQL lässt sich eine Kreuztabelle erstellen. Ein Beispiel: CROSS TAB QUERY IN MYSQL
Der zweite Ansatz wäre wahrscheinlich besser, wenn du das Ergebnis sowohl in Access als auch PHP verwenden willst.

[PhilS]

Btw: arbeite jetzt seit längerem wieder mal mit Ivercy, was macht man denn da mit dem Access-Backend? Die BackEnd-Links sind ja im Frontend eingecheckt, genügt das?

Ich meine nein. - Die Links enthalten ausser dem Tabellennamen keine greifbare Information.
Leider lassen sich Access-Backends nicht gut in einer Quellcodeverwaltung ablegen.Für SQL Server Projekt legen wir die Erstellungs-/Änderungscripte ab. In Access hast du erstmal nur die Tabellen und Daten in der Binärdatei. Wenn du das trennen willst, bedeutet das zusätzlichen Aufwand. - Konkrete Erfahrungen habe ich damit allerdings nicht, weil wir schon lange keine Access-Backends verwenden.

[Hondo]

Hallo Phil

Ich halte es nicht für die beste Idee, für die Entwicklung das MySQL-Backend durch eine Access-BE zu ersetzen.
1. Können dir dabei einige Probleme entgehen, die du später in der Produktivumgebung haben könntest.
2. Bist du dann gezwungen den kleinsten gemeinsamen Nenner beider Systeme zu verwenden, anstatt die Features eines System wirklich ausnutzen zu können.

Hast du eine Ahnung wie mühsam es es mit eingebundenem MySQL via ODBC zu arbeiten im Entwurf?
Ständig bricht die ODBC-Verbindung ab wenn man mit Form im Entwurf mit einer Datenherkunft arbeitet. Da hab ich absolut kein Bock mehr drauf.
Webspace mit Windows-Server ist mir momentan zu teuer - hab auch noch nie mit php damit entwickelt.
Das mit der Kreuztabellenabfrage sehe ich mir noch an, danke dafür.

Gruß Andreas

[PhilS]

Hast du eine Ahnung wie mühsam es es mit eingebundenem MySQL via ODBC zu arbeiten im Entwurf?
Ständig bricht die ODBC-Verbindung ab wenn man mit Form im Entwurf mit einer Datenherkunft arbeitet. Da hab ich absolut kein Bock mehr drauf.
Webspace mit Windows-Server ist mir momentan zu teuer - hab auch noch nie mit php damit entwickelt.

Mit MySql habe ich schon eine ganze Weile nicht mehr (als Access-Backend) gearbeitet. An derartige Probleme kann ich mich aber nicht erinnern. Mit Microsoft SQL Server als ODBC-Backend arbeite ich ständig und habe keine Probleme.

Ich würde einen Umstieg auf einem Windows Server für das WebHosting nicht empfehlen, wenn es nicht weitere Gründe dafür gibt. Übrigens, Microsoft SQL Server läuft seit SQL2017 auch auf Linux. ;-)
Die günstigste, stabile und zuverlässige Hosting-Möglichkeit mit Windows ist übrigens der Virtual Server von HostEurope.

[Hondo]

Hallo,
bez. der Ausgangsfrage hat sich das erledigt, habe das jetzt anderst gelöst.
Gruß Andreas