0

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

part of data macro

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:

regular macro

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.

  • Aside from the critical errors in the macros, the basic flow does not match what you intend to do. You repeat the SubMacro for each record in the table, but you only insert one new record each time. There is nothing which adds multiple new records for a single source record. You would need to run the macro many times, but then you would need to manually update the macro values and add filter criteria, etc. In summary, something is critically wrong with the logic of the macros and it is too difficult to explain how to fix it. There are better, alternative approaches. – C Perkins Nov 22 '17 at 16:31
  • I did not take a screenshot of the full macro. There are 4 more CreateRecords down below. – Exodus212 Nov 22 '17 at 16:33
  • I wish you only the best in learning Access, but there are just too many details to "clarify". That is not to say that other questions / answers could not be improved to help someone learning like you, but clarifying such an answer for a newbie would essentially require writing an entire tutorial for Access. Such general tutorials already exist. It's impossible to guess which details you know and which you don't. What you're asking is not a basic task and so requires some advanced knowledge. I suggest testing and debugging each small part of your macros separately before trying the whole thing – C Perkins Nov 22 '17 at 16:46
  • Although Access macros include commands which can navigate open table views, such a DataSheet view cannot be manipulated like other programmable datasets (e.g. DAO.Recordset using VBA). Even after calling GoToRecord on an "open table", if you subsequently call RunDataMacro it is not provided any context for editing records. In other words, the data macro has no idea what record the datasheet view is accessing so it fails. If you insist on using macros, you should consider the ForEachRecord command within the data macro. Otherwise, i can't say much for macros and suggest using SQL instead. – C Perkins Nov 23 '17 at 7:24
0

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

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

UPDATE DataTable SET DataTable.Col3 = 1
WHERE DataTable.Col3 Is Null;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.