Ms-access – How to turn each record on the Access table into 6 records

ms access

I've spent all day googled one issue: please help.

I have a table like the one below that I want to expand, turning each single record into 6 records.

TableName:
--------------------------------------------------
|  col1  |   col2   |          col3         |
-------------------------------------------------
|    A1  |  abcd    |                       |
-------------------------------------------------
|    B2  |  efgh    |                       |
--------------------------------------------------
|    C3  |   ijk    |                       |
--------------------------------------------------

It should end up looking something like this:

--------------------------------------------------
|  col1  |   col2   |          col3       |
-------------------------------------------------
|    A1  |  abcd    |         1           |
-------------------------------------------------
|    A1  |  abcd    |         2           |
-------------------------------------------------
|    A1  |  abcd    |         3           |
-------------------------------------------------
|    B2  |  efgh    |         1           |
--------------------------------------------------
|    B2  |  efgh    |         2           |
-------------------------------------------------
|    B2  |  efgh    |         3           |
-------------------------------------------------
|    C3  |   ijk    |         1           |
--------------------------------------------------

And I am at a loss for how to do it, because I am just starting with Access. So far I've made a data macro that should take a record, edit it to add the info in col3, then create 5 more. Like this

Then I made this macro to start at the top of the table, run the data macro, and go to the next record until all 430 have been expanded:

When I go to TableName and try to run the macro, I get

There is no data context in which to perform the action. This error can be caused by using theRunDataMacro action to run a data macro that calls DeleteRecord or EditRecord with no alias specified.

Where have I gone wrong?

A good answer to this question may be to clarify this question enough that somebody with very little Access experience – me – can apply it to this slightly different situation.

Best Answer

CAUTION: Before implementing any solution that duplicates column values into new rows, consider a properly normalized table schema. Normalized tables eliminate duplicated data and have appropriate primary and foreign keys linking multiple tables together (rather than trying to force all the data into one table). Duplicating and saving values into multiple rows of a table creates a headache of having to keep the rows in sync, or later suffer from mismatched data in rows that should contain the same values. It is always possible with normalized tables to produce multiple rows as desired WHEN NEEDED using appropriate JOIN queries

The best approach to producing the expanded rows is to use another (temporary) table. For example, create a table named [TempTable] with the following values:

Col3
----
 1
 2
 3

Now do a CROSS JOIN between the two tables. A cross join produces the Cartesian product of all rows in both tables. This means it returns every possible combination of rows from each table. (For Access [and I think for any other standard SQL relational database system] the default behavior is to always produce the Cartesian product for all rows which are not otherwise constrained by another JOIN.) In Access SQL, CROSS JOINS are accomplished by listing tables without specifying any join type--a comma-separated list of tables:

SELECT DT.Col1, DT.Col2, TT.Col3
FROM DataTable As DT, TempTable As TT
WHERE DT.Col3 Is NULL
ORDER BY DT.Col1, DT.Col2, TT.Col3

That query will simply return the expanded set of rows, but it will not update the original table.

If you insist on saving the new rows to the original table, you can do that with a couple update queries based on the previous query.

Notice that I added the condition WHERE DT.Col3 Is NULL to avoid duplicating rows which have already been processed.

Save the previously listed query as a named query, for example [Expanded].
Add new rows for [Col3] >= 2 using the following INSERT command:

INSERT INTO DataTable ( Col1, Col2, Col3)
SELECT Expanded.Col1, Expanded.Col2, Expanded.Col3
FROM Expanded
WHERE Expanded.Col3 >= 2

Update original rows with 1 -> [Col3] using the following UPDATE command:

UPDATE DataTable SET DataTable.Col3 = 1
WHERE DataTable.Col3 Is Null;

Related Solutions

Ms-Access 2007-2010 SQL – from single row record how to create multiple rows

Easy answer is to do this in a function in the built-in VBA editor in Microsoft Access.

Assuming you have two tables, Source and Dest that both have 3 columns, col1, col2, and col3.

Sub SplitIt()

    Dim rstSource As Recordset
    Dim rstDest As Recordset
    Dim Items() As String

    ' use dbOpenSnapshot to open the source table READ-ONLY
    Set rstSource = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Source;" _
            , dbOpenSnapshot)
    ' use dbOpenDynaset to open the destination table READ-WRITE
    Set rstDest = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Dest;" _
            , dbOpenDynaset)

    With rstSource
        ' .BOF is Beginning of the table
        ' .EOF is End of the table
        ' Checking if both are false means there are records in the
        ' source table
        If Not (.BOF And .EOF) Then
            ' get the first record from the source table
            .MoveFirst
            Do
                ' if col3 is NULL (empty)
                If Nz(!col3, "") = "" Then
                        ' add a new record into the destination table
                        ' with data from the source table for col1 and col2
                        rstDest.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' set col3 to NULL (empty)
                        rstDest!col3 = Null
                        ' save the new record
                        rstDest.Update
                Else ' if col3 IS NOT NULL
                    ' convert col3 into an array of strings
                    Items = split(Nz(!col3, ""), ";")
                    ' loop through the array of strings
                    For a = LBound(Items) To UBound(Items)
                        rstDest.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' col3 is set to the current item in the array
                        rstDest!col3 = Items(a)
                        rstDest.Update
                    Next a
                End If
                ' load the next record from the source table
                .MoveNext
            ' repeat until the end of the source table is reached
            Loop Until .EOF
        End If
        ' close the source table
        .Close
    End With
    ' close the destination table
    rstDest.Close

End Sub

Then run the SplitIt() function from the Immediate Window. Press CTRL-G then type:

SplitIt

and press [Enter]

As an aside; there is an excellent resource on understanding For...Next code at http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx

Ms-access – Splitting 1 record on one table to become two records on another table

See if this work.

 CREATE FUNCTION [dbo].[FN_CommaSeparator] (@array VARCHAR(max))

> RETURNS @Table TABLE (value VARCHAR(max))

AS
BEGIN
    DECLARE @separator_position INT
        ,@array_value VARCHAR(max)

    SET @array = @array + ','

    WHILE PATINDEX('%,%', @array) <> 0
    BEGIN
        SELECT @separator_position = PATINDEX('%,%', @array)

        SELECT @array_value = LEFT(@array, @separator_position - 1)

        INSERT @Table
        VALUES (@array_value)

        SELECT @array = STUFF(@array, 1, @separator_position, '')
    END

    RETURN
END

CREATE TABLE #Temp (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

CREATE TABLE #Temp2 (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

INSERT INTO dbo.#Temp (
    Id
    ,Author
    )
VALUES (
    'S1'
    ,'ABC,BCD'
    )
    ,(
    'S2'
    ,'CDE,DEF'
    )
    ,(
    'S3'
    ,'EFG,FGH'
    )
    ,(
    'S4'
    ,'GHI,HIJ'
    )

DECLARE @Id VARCHAR(50)
    ,@Author VARCHAR(50)

DECLARE ABC CURSOR
FOR
SELECT Id
    ,Author
FROM dbo.#Temp t

OPEN ABC

FETCH NEXT
FROM ABC
INTO @Id
    ,@Author

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.#Temp2 (
        Id
        ,Author
        )
    SELECT @Id
        ,Value
    FROM dbo.FN_CommaSeparator(@Author)

    FETCH NEXT
    FROM ABC
    INTO @Id
        ,@Author
END

DEALLOCATE ABC

SELECT *
FROM dbo.#Temp2 t

DROP TABLE dbo.#Temp

DROP TABLE dbo.#Temp2

Best Answer

Related Solutions

Ms-Access 2007-2010 SQL – from single row record how to create multiple rows

Ms-access – Splitting 1 record on one table to become two records on another table

Related Question