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.

I think writing a VBA module to build the SQL dynamically is the way to go. — tinazmu, Apr 05 '23 at 22:36
Thanks for the reply. I will consider this. It may be possible to use VBA to build the SQL using the SQL hints provided below by user "June7". — Gary B, Apr 06 '23 at 23:08
Yes, VBA can open recordset of table, loop through fields, build UNION SQL statement and modify saved query object with DAO QueryDefs OR loop records and fields and save data in a 'temp' table. — June7, Apr 06 '23 at 23:10

score 2 · Answer 1 · answered Apr 05 '23 at 23:15

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;

answered Apr 05 '23 at 23:15

June7

19,874
8
24
34

Thank you very much! This looks like it will do the trick. – Gary B Apr 06 '23 at 23:06
Hope so. If answer resolves issue, please mark accepted. – June7 Apr 06 '23 at 23:11

score 0 · Accepted Answer · answered Apr 07 '23 at 08:34

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

Thank you very much. Although I'm pretty good with VBA for Excel, I don't have a lot of experience with VBA for Access, so this is a big help. Thanks again! — Gary B, Apr 08 '23 at 16:52

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

2 Answers2

Linked