how to convert excel like spreadsheet into data base form

[Guest]

I have an excel spreadsheet with data arranged as follows:

My column headings are -
Name Item Sales(Jan) Sales (Feb) Sales (Mar) Sales (Apr) ----> Sales
(Dec)

I want to convert this to the following database format -
Name Item Month Sale

How do i do this?

 

[Ken Snell [MVP]]

Import the data into a temporary table "as is" from the EXCEL sheet.

Then create a UNION query that normalizes the data the way you want it to be
in the permanent table:

SELECT [Name], [Item], [Sales(Jan)]
FROM TempTableName
UNION ALL
SELECT [Name], [Item], [Sales(Feb)]
FROM TempTableName
UNION ALL
SELECT [Name], [Item], [Sales(Mar)]
FROM TempTableName
(etc.)
UNION ALL
SELECT [Name], [Item], [Sales(Dec)]
FROM TempTableName;

Then create an append query that uses the union query as its source of data
to copy the data to your permanent table.

Note: don't use Item, Name, etc. as the names of fields. They are reserved
words in ACCESS, and can create serious confusion for ACCESS and Jet. See
this Knowledge Base article for more information:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335