How to Create Automatic Dynamic Union Query

[Mr Key]

Hi all once again!!!!
I have a table of upto 30-fields that supposed to be Converted to a table of 5-Columns by UnionQuery. The field names are:

CustomerID FirstName LastName Products1 Products2 Product3 ...Products28
   1       APO       Denty     2           3        7      ... 4
   2       Adezii    Neo       4           2        3      ... 0
   ..      ...       ...       ..         ..         ..   ........
   ..      ...       ...       ..         ..         ..   ........
 

By using a Union Query I can Convert this table to five Columns which contains CustomerID, Firstname, LastName and Products as follows

CustomerID Firstname LastName Products Quantity
     1       APO       Denty  Products1    2
     1       APO       Denty  Products2    3
     1       APO       Denty  Products3    7
     ..      ...       ...    ..           ..    
     1       APO       Denty  Products28   4
     2       Adezii    Neo    Products1    4  
     2       Adezii    Neo    Products2    2
     2       Adezii    Neo    Products2    3
     ..      ...       ...    ..           ..   
     2       Adezii    Neo    Products28   0
     ..      ...       ...    ..           ..   
     ..      ...       ...    ..           .. 
     ..      ...       ...    ..           ..     
 

The problem is I have to write this Union Query Expression Manualy inorder for it to be in this formart.
I have more than 30Tables of this kind and new tables everyday.
Should there be any means of making it automatic please help!!!!!
(Note!!, FieldNames will remain constant for whaterver the convertions)
Please!!!!

[Stewart Ross]

Hi. What you ask cannot be achieved in a generalised form in SQL, but can be done if you use VBA code for recordset processing.

The function below can be called as many times as required to process each source table and output the results to a named destination table. For the first of the tables processed you need to call the function with its argument ClearPreviousValues set to True, to delete any previous version of the destination table. For all other calls you have this argument set False.

You would need to call this function from code on a form somewhere, say in the OnClick event of a command button.

The function returns True if the extract of products was successful, and false if not.

A typical call to the function looks like this:

blResult = fExtractProductQuantities("Name of First Input Table", "Name of Output Table", 4, 28, True)
blResult = fExtractProductQuantities("Name of Second Input Table", "Name of Output Table", 4, 28, False)

The function itself is shown below. You would need to place it in a public code module (one which is shown in the Modules tab), or create a new module if there is no existing module you can use.

Public Function fExtractProductQuantities(ByVal SourceTableName As String, _
                                          ByVal DestinationTableName As String, _
                                          ByVal FirstProductFieldNo As Integer, _
                                          ByVal NoOfProducts As Long, _
                                          ByVal ClearPreviousValues As Boolean) As Boolean
'This function extracts individual product quantities from a non-normal source table and inserts
'these as individual rows into a specified destination table.
'
'Although field names are noted as fixed for the application in which this function is to be used
'no assumptions about names are made. Instead, the fields are referred to by counting the left-most
'field in the source table as field 1, the next left as field 2, and the final field as fieldN where
'N is the number of columns in the source table.
 
'Assumptions
'1. The first field through to the field before FirstProductFieldNo are to be repeated on each row
'   inserted in the destination table
'2. The first field is a numeric customer ID, not a text field
'3. The fields between the FirstProductNo column and the LastProductNo column contain numeric values
'   representing the quantity of that product. The name of the field is the name of the product
'   or if not, a synonym for it.
'4. There are no fields to the right of the LastProductNo column which need to be output.
'
'IN:  SourceTableName:      Name of the source table containing the repeated product rows
'     DestinationTableName: Name of the destination table into which the result rows will be inserted
'     FirstProductNo:       Column number of the first product field, taking the left-most field to be column 1 of the table
'     NoOfProducts:         Number of product columns in sequence in each row
'     ClearPreviousValues:  If this is set to True then the previous values in the destination table are deleted.
'                           If False, the values are appended to those already in the table.
'
'     All arguments are required values which must be supplied when the function is called.
'
'Out: function returns True if rows were inserted into the destination table, False if not.
'
'Example source data
'CustomerID FirstName LastName Products1 Products2 Product3 ...Products28
'   1       APO       Denty     2           3        7      ... 4
'
'Example destination data
'CustomerID Firstname LastName Products Quantity
'     1       APO       Denty  Products1    2
'     1       APO       Denty  Products2    3
'     ..      ...       ...    ..           ..
'     1       APO       Denty  Products28   4
'
'As the DAO object library is required a reference to the DAO object library must be available
'If the function does not compile select Tools, References and check that there is a tick against
'the Microsoft DAO 3.6 or similar object library.
    Dim intCurrentField As Integer
    Dim intFieldCount As Integer
    Dim intProductCount As Integer
    Dim rsSource As DAO.Recordset
    Dim dbThisDB As DAO.Database
    Dim strSQLCreate As String
    Dim strSQLInsert As String
    Set dbThisDB = CurrentDb
    Set rsSource = dbThisDB.OpenRecordset(SourceTableName)
    If rsSource.RecordCount > 0 Then
        On Error Resume Next
        If ClearPreviousValues Then
            dbThisDB.Execute "DROP TABLE [" & DestinationTableName & "]"
        End If
        strSQLCreate = "Create Table [" & DestinationTableName & "] ("
        For intFieldCount = 0 To FirstProductFieldNo - 2
            strSQLCreate = strSQLCreate & rsSource.Fields(intFieldCount).Name & IIf(intFieldCount = 0, " INT ,", " VARCHAR(255), ")
        Next
        strSQLCreate = strSQLCreate & "Product VARCHAR(255), Quantity INT);"
        dbThisDB.Execute strSQLCreate
        On Error GoTo errEncountered
        Do While Not rsSource.EOF
            For intProductCount = 1 To NoOfProducts
                strSQLInsert = "INSERT INTO [" & DestinationTableName & "] Values (" & rsSource.Fields(0) & ", "
                For intFieldCount = 1 To FirstProductFieldNo - 2
                    strSQLInsert = strSQLInsert & "'" & rsSource.Fields(intFieldCount) & "', "
                Next intFieldCount
                intCurrentField = intProductCount + FirstProductFieldNo - 2
                strSQLInsert = strSQLInsert & "'" & rsSource.Fields(intCurrentField).Name & "', "
                strSQLInsert = strSQLInsert & rsSource.Fields(intCurrentField) & ");"
                dbThisDB.Execute strSQLInsert
            Next intProductCount
            rsSource.MoveNext
        Loop
        rsSource.Close
        fExtractProductQuantities = True
    End If
    Exit Function
errEncountered:
    fExtractProductQuantities = False
End Function

I attach a text file export of a test run on a single table. I have also tested this with multiple table imports.

-Stewart