Split Record Query

[Flippa]

Hi all,



I have a table that lookes like the "from" table in the attachement and would like to run a query to arange the data to look like the "to" table in the attachment.Attachment 5531

As you can see I have added a field for year and cost and created a record for each instance from the original table where there was money spent in a year.

Can you give me some advice to achieve this?

I suspect that it will require some VB code.

Thanks

Flip

[pbaldy]

No code, a UNION query:

SELECT AssetID, Description, BudgetSource, 2011 AS TheYear, [2011] AS Cost
FROM TableName
WHERE [2011] Is Not Null
UNION ALL
SELECT AssetID, Description, BudgetSource, 2012 AS TheYear, [2012] AS Cost
FROM TableName
WHERE [2012] Is Not Null

Note that "Year" is not a good field name, as it can conflict with the Year() function.