Access 2013 Multiple Columns into Rows

Question

Ok I am using Access 2013 and have a continuous form using a flat table with the following fields:

OrderDate \ Ref \ ItemSize1 \ ItemSize 2 \ ItemSize 3 \ Qty 1 \ Qty 2 \ Qty 3

So eg data would look this:

09/06/15  \ PO123 \ ABC_S   \     ABC_M   \    ABC_L   \  2    \ 6     \ 8
09/06/15 \ PO123 \ XYZ_8     \   XYZ_10   \    XYZ_12  \ 3     \ 2     \  7

However, I'm building a file for export that is needed in this format;

OrderDate \ Ref \ Item \ Qty

So the result data would look like this:

09/06/15 \ PO123 \ ABC_S \ 2
09/06/15 \ PO123 \ ABC_M \ 6
09/06/12 \ PO123 \ ABC_L \ 8

and so on.... Points to note:

• Item Size 1 uses the qty from qty 1, item size 2 uses qty etc

• I am only interested where items sizes have a qty. So if ItemSize 2 had no qty in Qty 2, I would need to miss it out

I have tried a Union query but for some reason it does not sort the size. The result file needs to have the items in order of size (ItemSize1, ItemSize2 etc)

What other way is there in Access to achieve this?

Thanks for any help.

Michael

Answer 1

I totally agree with krish, your solution is to make the data structure more resilient.

The real Solution: (Can be improved a lot)

First create the two tables, Products and Invoice.

Products
````````
productID - AutoNumber (PK)
productCategory - Text
productSize - Text

Invoice
```````
invoiceID - AutoNumber (PK)
invoiceDate - DateT/Time
productID - Number (FK)
invoiceQuantity - Number

Now the relationship between Products and Invoice is One to Many, where one product can have many Invoices. Example, one Tee Shirt for one sizes/color could have many orders.

Now the Products table will have the information of the products that you have available in your Database. Some sample would be.

productID   |   productCategory |   productSize
------------+-------------------+---------------
1           |   Tee Shirt       |   S
2           |   Tee Shirt       |   M
3           |   Polo            |   L
4           |   Vest            |   S
5           |   Vest            |   M

Now the Invoice table should have the order for a particular product of particular size.

invoiceID   |   invoiceDate |   productID   |   invoiceQuantity
------------+---------------+---------------+--------------------
1           |   09/06/2015  |   1           |   10
2           |   09/06/2015  |   2           |   5
3           |   09/06/2015  |   3           |   10
4           |   09/06/2015  |   5           |   3

Now, once the data is transformed into the right structure, your Query would simply be.

SELECT
    productCategory,
    productSize,
    invoiceDate,
    invoiceQuantity
FROM
    Products 
    INNER JOIN
    Invoice
    ON 
    Products.productID = Invoice.productID

Your result set would be,

productCategory |   productSize |   invoiceDate |   invoiceQuantity
----------------+---------------+---------------+--------------------
Tee Shirt       |   S           |   09/06/2015  |   10
Tee Shirt       |   M           |   09/06/2015  |   5
Polo            |   L           |   09/06/2015  |   10
Vest            |   M           |   09/06/2015  |   3

Now, you should be able to get your head around, how simple data restructuring could make your queries so simple and easy to implement.

The temporary fix

Although it pains for me to tell you you should restructure your tables, does not mean that you cannot achieve what you want. Although the solution would work, and might even seem OK. It actually is not. There is too much memory involved, efficiency is poor. You create and drop records, constantly upsize your DB. so many disadvantages, but to hold things together.

Public Sub createTempForm()
    Dim sqlStr As String
    Dim tmpRS As DAO.Recordset, tmpRS2 As DAO.Recordset, tmpDB As DAO.Database

    Set tmpDB = CurrentDB()
    sqlStr = "SELECT OrderDate, Ref, ItemSize1, ItemSize2, ItemSize3, Qty1, Qty2, Qty3 " & _
             "FROM yourTableName"

    Set tmpRS = tmpDB.OpenRecordset(sqlStr)

    tmpDB.Execute "DELETE * FROM tmpTbl"

    'Make sure this table - tmpTbl, is created (just the skeleton, data will be added every time you run this query.)
    Set tmpRS2 = tmpDB.OpenRecordset("tmpTbl")

    Do While Not tmpRS.EOF
        'Go through all records to create the data you wish to see. 
        With tmpRS2
            'This will add as many records to the tmpTbl
            For iCtr = 1 to 3
                If Len(tmpRS.Fields("ItemSize" & iCtr) & vbNullString) > 0 Then
                    'Create a record only if the fields are not null.
                    .AddNew
                    .Fields("OrderDate") = tmpRS.Fields("OrderDate")
                    .Fields("Ref") = tmpRS.Fields("Ref")
                    .Fields("Item") = .Fields("ItemSize" & iCtr)
                    .Fields("Qty") = .Fields("Qty" & iCtr)
                    .Update
                End If
            Next
            tmpRS.MoveNext
        End With
    Loop

    Set tmpRS = Nothing
    Set tmpDB = Nothing

    'This report should be based on the tmpTbl. 
    DoCmd.OpenReport "yourReportName"

    'If you prefer to Export the table as Excel file, you are welcome to do so. Saves designing report. 
    'DoCmd.Outputto acOutputTable, "tmpTbl", acFormatXLS, "C:\testData.xls"
End Sub

Now the reason I have used VBA as opposed to luk2302's UNION query is because, with a little modification, the above query could actually be used to transform your flat table data into the structure you should have.

So have a think about it, I am sure you will never ever regret it. Good luck !

Answer 2

You might be able to do something like the following:

SELECT UnionQueries.val FROM (
  SELECT tmpTEST.Ref as ref, tmpTEST.S1 as val, 1 as col FROM tmpTEST UNION 
  SELECT tmpTEST.Ref as ref, tmpTEST.S2 as val, 2 as col FROM tmpTEST UNION 
  SELECT tmpTEST.Ref as ref, tmpTEST.S3 as val, 3 as col FROM tmpTEST UNION 
  SELECT tmpTEST.Ref as ref, tmpTEST.S4 as val, 4 as col FROM tmpTEST) AS UnionQueries 
ORDER BY UnionQueries.ref, UnionQueries.col ASC