[AccessD] Turn a Table

Hi Chris

Sorry, Transpose it is, no transform.

The functions from Shamil should be all you need.

/gustav

>>> cclenright at yahoo.com 24-04-2008 14:57 >>>
Hi Gustav,

I am using Excel 2003 and while it has Transpose, which converts a Row to a Column, or vice versa, I have no Transform (that I can find in Help)..

Crosstab Queries I spent two days on ..lol ... and the problem is it only works for one col, one or more rows and one value ... (unless I totally misunderstood all the examples I found) ..

Chris


----- Original Message ----
From: Gustav Brock <Gustav at cactus.dk>
To: accessd at databaseadvisors.com 
Sent: Thursday, April 24, 2008 11:04:47 AM
Subject: Re: [AccessD] Turn a Table

Hi Chris

A quick pair of hints as I'm busy doing real work:

In Excel you have Transform to "rotate" your table.
In Access you have the crosstab query to do something similar.

/gustav

>>> cclenright at yahoo.com 24-04-2008 11:24 >>>
Hi Group

Could someone please tell me if there is a simple way to solve this problem ...
I started a small project from each end (stupid!) and have ended up with a problem in the middle that I can't seem to solve.
I managed to import the sheets from an Excel workbook into Access and have also got the export back to Excel for upload to the web working fine but have ended up with this situation.

The imported/export tables (reduced for example) are like this..

tblTasks
Task
John
Jane
Bill
Mary
Simon
etc

and produces a table like:

Task                John                Jane                Bill                  Mary                Simon
Task 1              2                      1                      4                      3                      0
Task 2              1                      0                      1                      2                      4
Task 3              0                      4                      0                      1                      1
Etc

However, my table for the Form that data is entered on populates a table that can be filtered by name (and date* not shown) and is:

tblNames
Name
Task 1
Task 2
Task 3
*.(25 Tasks)

and this table is like this:

Name              Task1              Task2              Task3              (25 tasks*.)
John                2                      1                      0
Jane                1                      0                      4
Bill                  4                      1                      0
Mary                3                      2                      1
Simon              0                      4                      1

My problem is either to find a way of converting tblNames to tblTasks (spent 3 days trying this ) OR find a way of using tblTasks as the input table for my forms so that the individuals can enter their daily work against tasks...

Can any one help please? I have to try and get this live by Monday morning and don't want to spend the weekend on it ... :)

Regards

Chris