from excel - multiple column to one

[Eitas]

I am trying to convert an excel sheet to access. This is a sheet to track when a task needs to be done and when it was done.

The excel sheet has a column for each task and each date. So if task A needs to be done monthly there are 12 columns for that task.

The issue is not all tasks are needed for each record and from what I know of DB design multiple rows are better than multiple columns. I am looking at converting the data from

name, report Jan, report Feb, report mar, ....

to
Name, type, date

with type being the type of task that has to be done.

Is there a way to quickly change the data from multiple columns to multiple rows.



I hope this makes sense.

Thanks.

PS: What I tried was several append equerries one for each task and for each month. I am hoping there is an easier way.

[alansidman]

I think that you can do this with a series of Union Queries for each of the month columns in your table.

Select table.[Name], table.[type], table.[report Jan] As reportMonth
from table
 
Union
 
Select table.[Name], table.[type], table.[report Feb]
from table
 
Union
Select table.[Name], table.[type], table.[report Mar]
from table
 
etc.

BTW, the term Name is a reserved word in Access and you should use something else or enclose it in square brackets to avoid future issues.


Here is a link on Union Queries

http://www.w3schools.com/sql/sql_union.asp