Export values to Excel MS Access

Question

I have a table in MS Access having 4 rows with four columns A, B, C, D

I want to export this records into an Excel sheet as follows,
Expected Output :
8 rows in Excel
How Excel should be like
4 rows will be queried from table
A clone of each row but with Column A prefixed with text 'Dummy'
Other column values for the cloned rows are constant
Example :

• Table :

• How Excel to be exported :

Each row has been cloned with only column A prefixed with text and rest of the column values are constant

I have few options to achieve in form through VBA and would like to hear which one would be optimised way

1. -Write a Query to select table values,
   -Create a temp table (clone the structure of existing table)
   -Loop through the Record-set which has queried values
   -Fill the Temp table's first column value with Record-set's A column value with prefixed harcoded text and other column values are hardcoded as well
   -append two table and export into Excel

2. -Query from table and export into Excel
   -Loop through table and fill Excel cell A6 with tables field(1) with prefix

3. -Query from table and export into Excel
   -Append the excel with hardcoded values for additional 4 rows (not sure append is possible)

Please suggest which way is possible and happy to hear other options I miss.(Never used MS Access before and this is my first hands on. Based on tutorials I got through, I come with above options)

Answer 1

I would suggest writing a query to select the data then union to that query the second dataset you want. Would look something like

SELECT A, B, C, D FROM tble
UNION ALL
SELECT "PREFIX " & A, "IDE", TRUE, FALSE FROM table

Then output the result of this query to excel.