How do I move data in columns (XL) to rows in Access table   

Hi 
I have an Excel 2003 spreadsheet with columns-- Name, Date, Rating, Date1, 
Rating1, Date2, Rating2, Date3,Rating3   -->etc up to -->Date7, Rating7.

In Access 2003 how can I import the above spreadsheet & end up with a table 
with Name, Date, Rating - with the:

Date field of the table containing the data from the Date, Date1, Date2, 
Date3 etc columns of the spreadsheet for that name. 
Rating field of the table containing the data from the Rating, Rating1, 
Rating2, Rating3 etc columns of the spreadsheet for that name.
The name in the s'sheet row can be repeated for each row of the table.


Thanks for any assistance
Ross

date: Thu, 3 Jul 2008 06:07:00 -0700   author:   Ross08

Re: How do I move data in columns (XL) to rows in Access table   

Ross,

Have a look at the Excel to Access Converter Utility which will do it
for you. Incidentally, change the names of the Date and Name fields to
something else when you create the table as those are reserved words
in Access.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Excel%20to%20Access%20Converter%20Utility%20program'

HTH

Peter Hibbs.

On Thu, 3 Jul 2008 06:07:00 -0700, Ross08
 wrote:

>Hi 
>I have an Excel 2003 spreadsheet with columns-- Name, Date, Rating, Date1, 
>Rating1, Date2, Rating2, Date3,Rating3   -->etc up to -->Date7, Rating7.
>
>In Access 2003 how can I import the above spreadsheet & end up with a table 
>with Name, Date, Rating - with the:
>
>Date field of the table containing the data from the Date, Date1, Date2, 
>Date3 etc columns of the spreadsheet for that name. 
>Rating field of the table containing the data from the Rating, Rating1, 
>Rating2, Rating3 etc columns of the spreadsheet for that name.
>The name in the s'sheet row can be repeated for each row of the table.
>
>
>Thanks for any assistance
>Ross
>

date: Thu, 03 Jul 2008 15:07:23 +0100   author:   Peter Hibbs _SPAM

RE: How do I move data in columns (XL) to rows in Access table   

7 queries.  One for each Date/Rating combination.
One other thing.  For a true relational database design, you should have two 
tables.  One for the name and the other for the dates and ratings.
Use an Autonumber primary key for the name table and instead of the name in 
the data/rating table, store the name primary key as a foreign key.
-- 
Dave Hargis, Microsoft Access MVP


"Ross08" wrote:

> Hi 
> I have an Excel 2003 spreadsheet with columns-- Name, Date, Rating, Date1, 
> Rating1, Date2, Rating2, Date3,Rating3   -->etc up to -->Date7, Rating7.
> 
> In Access 2003 how can I import the above spreadsheet & end up with a table 
> with Name, Date, Rating - with the:
> 
> Date field of the table containing the data from the Date, Date1, Date2, 
> Date3 etc columns of the spreadsheet for that name. 
> Rating field of the table containing the data from the Rating, Rating1, 
> Rating2, Rating3 etc columns of the spreadsheet for that name.
> The name in the s'sheet row can be repeated for each row of the table.
> 
> 
> Thanks for any assistance
> Ross
>

date: Thu, 3 Jul 2008 07:30:01 -0700   author:   Klatuu

RE: How do I move data in columns (XL) to rows in Access table   

Thanks for the help

Ross

"Ross08" wrote:

> Hi 
> I have an Excel 2003 spreadsheet with columns-- Name, Date, Rating, Date1, 
> Rating1, Date2, Rating2, Date3,Rating3   -->etc up to -->Date7, Rating7.
> 
> In Access 2003 how can I import the above spreadsheet & end up with a table 
> with Name, Date, Rating - with the:
> 
> Date field of the table containing the data from the Date, Date1, Date2, 
> Date3 etc columns of the spreadsheet for that name. 
> Rating field of the table containing the data from the Rating, Rating1, 
> Rating2, Rating3 etc columns of the spreadsheet for that name.
> The name in the s'sheet row can be repeated for each row of the table.
> 
> 
> Thanks for any assistance
> Ross
>

date: Thu, 3 Jul 2008 19:45:01 -0700   author:   Ross08