Help with a Select Into query

[Nedlog]

Hi,

In Access 2003 I need to make a new table from an existing table. Each
record in the existing table has information on costs for a project
over 3 years. The new table must have a record for each project for
each year. ie One record in the existing table will convert to three
records in the new table.

The existing table has the following columns:

Code Type Year1 Year2 Year3 Y1_Cost Y2_Cost Y3_Cost
---------------------------------------------------------------
DB1073 C1 01/02 02/03 03/04 150 221 85

So the record above would get converted to:

Code Type Year Cost Project_Year
--------------------------------------------
DB1073 C1 01/02 150 1
DB1073 C1 02/03 221 2
DB1073 C1 03/04 85 3


Can this be done with a Select Into query? If not, can someone please
explain how to go about it.

Many thanks,
D

 

[Allen Browne]

Try something like this:

SELECT INTO Table2, ( [Code Type], Project_Year, [Year Cost] )
SELECT [Code Type], Year1, Y1_Cost FROM [Table1]
UNION ALL
SELECT [Code Type], Year2, Y2_Cost FROM [Table1]
UNION ALL
SELECT [Code Type], Year3, Y3_Cost FROM [Table1];

Access will not be able to display the UNION query grapically, but you can:
- Create a query based on just Year1 and Y1_Cost.
- Turn it into an Append query (Append on Query menu.)
- Switch to SQL View (View menu.)
- Add the UNION ALL and repeat the SELECT part, changing the field names.