Search code examples
ms-accessvba

Creating one table from data of another table in Access

This question is regarding MS Access.

What I'm trying to do is that I have one table in Access, and would like to create another table (hopefully automatically through some VBA code) using data from the first table.

Is there any suggestion on how to do this? I'm quite new in VBA and Access so any detailed help would be appreciated (or direction on where to go).

Original Table (table 1 Data):

Original Table (table 1 structure):

Table 2 Data:

Table 2 Structure:

Solution

  • No VBA is needed. A UNION query can rearrange the data. There is no wizard or designer for UNION query, type or copy/paste in SQLView of query builder. First line defines data types and field names. Also, there is a limit of 50 SELECT lines.

    SELECT ID AS IDFromTable1, EmployeeName, Ford AS CarManufacturer FROM Table1
UNION SELECT ID, EmployeeName, Mazda FROM Table1
UNION SELECT ID, EmployeeName, Honda FROM Table1
UNION SELECT ID, EmployeeName, Toyota FROM Table1
UNION SELECT ID, EmployeeName, Audi FROM Table1
UNION SELECT ID, EmployeeName, Volkswagon FROM Table1
UNION SELECT ID, EmployeeName, BMW FROM Table1
UNION SELECT ID, EmployeeName, Benz FROM Table1;