How do I create an Access query to get multiple row;, one row for each column in a table?

Question

I looked here for a solution, but didn't find anything similar.

Here is my Discounts table:

ID	SilverVAR	GoldVAR	PlatinumVAR
CA2R	20	30	40
GB34	22	25	45
HT4R	25	28	50

I need output that looks like this:

ID	PartnerType	Discount
CA2R	SilverVAR	20
CA2R	GoldVAR	30
CA2R	PlatinumVAR	40
GB34	SilverVAR	22
GB34	GoldVAR	25
GB34	PlatinumVAR	45
HT4R	SilverVAR	25
HT4R	GoldVAR	28
HR4R	PlatinumVAR	50

Is this possible? How do I go about it?

I tried CrossTab but that doesn't seem to be the solution especially if there are more partner type columns across the Discounts table.

Answer 1

Here is an example VBA code you can adapt:

Public Sub MakeNewQry()
    Const CrossTabQryName As String = "qryCrossTab"
    Dim db As Database, fld As Field, strSQL As String
    
    With CurrentDb
        strSQL = ""
        For Each fld In .TableDefs("Discounts").Fields
           If fld.Name Like "?*VAR" Then
             strSQL = IIf(strSQL = "", "", strSQL & vbCrLf & "UNION ALL ") & _
                    "select ID, '" & fld.Name & "' as PartnerType, " & fld.Name & " as Discount from Discounts "
           End If
        Next fld
        Dim qd As QueryDef
        Set qd = New QueryDef
        qd.Name = CrossTabQryName
        qd.sql = strSQL
        
        '---Delete the old query
        On Error Resume Next
        .QueryDefs().Delete (CrossTabQryName)
        On Error GoTo 0
        .QueryDefs().Append qd
        set qd=nothing
    End With
    
    Set fld = Nothing
    Set db = Nothing
End Sub

Answer 2

A CROSSTAB is definitely not appropriate. What you want is the opposite (UNPIVOT).

A UNION query can rearrange fields to normalized structure. There is no designer for UNION, must build in SQLView. There is a limit of 50 SELECT lines. First SELECT determines field names.

SELECT ID, "Silver" AS PartnerType, SilverVAR AS Discount FROM Discounts
UNION SELECT ID, "Gold", GoldVAR FROM Discounts
UNION SELECT ID, "Platinum", PlatinumVAR FROM Discounts;