How can I turn each record on my Access table into 6 records?

Question

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.

Answer 1

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.

1. Save the previously listed query as a named query, for example [Expanded].
2. 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

---

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

---

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