How can I turn each record on my Access table into 6 records?
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] &gt;= 2 using the following INSERT command:




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





Update original rows with 1 -&gt; [Col3] using the following UPDATE command:




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