[AccessD] Turn a Table

Thanks Shamil...

...... I will ... But I have to work out how it all works ... but I will get there ..

Thanks

Chris



----- Original Message ----
From: Shamil Salakhetdinov <shamil at smsconsulting.spb.ru>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Thursday, April 24, 2008 7:36:20 PM
Subject: Re: [AccessD] Turn a Table

Chris,

Then use VBA solution as I've proposed already below:

1) load tblNames using e.g. ADO's GetRows
(http://www.triadcenters.com/caspdoc/Ch11_ADO94.html )
2) transpose loaded variant array with using this code
http://www.visualbasic.happycodings.com/Applications-VBA/code30.html 
3) save transposed array into tblTasks row by row...

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Chris Enright
Sent: Thursday, April 24, 2008 7:59 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Turn a Table

Shamil,

Once set up it would be a daily exercise. Always the same tables and same
amount of rows and columns.
But only this one of project ... I hope ...:)

Chris

----- Original Message ----
From: Shamil Salakhetdinov <shamil at smsconsulting.spb.ru>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Thursday, April 24, 2008 4:04:25 PM
Subject: Re: [AccessD] Turn a Table

Chris,

Do you need to do data rows and columns transposing on regular basis or just
one time?

If just one time then as Gustav noted MS Excel's Paste Special -> Transpose
could do the trick, and then attach transposed Excel table and use MS Access
append query...

The above procedure could be also automated of course but such automation
makes sense if you have to do data rows and columns transposing on regular
basis...

HTH

--
Shamil


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Chris Enright
Sent: Thursday, April 24, 2008 5:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Turn a Table

Thanks Shamil,

Think I might have bitten off moew than I can chew here ..

Looks like I'm back to automating Excel to do the job ... but I hate using
Excel to do a job that should really be a database, and a simple one.

It is just this interface between Access and Excel and trying to "rotate"
the way they each use data..


Thanks anyway ... I am playing with those two bits of code ... if only to
learn how they work ..


Chris



----- Original Message ----
From: Shamil Salakhetdinov <shamil at smsconsulting.spb.ru>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Thursday, April 24, 2008 11:41:50 AM
Subject: Re: [AccessD] Turn a Table

Hi Chris,

This VBA code could help you - "Transposing a 2d array"

http://www.visualbasic.happycodings.com/Applications-VBA/code30.html 

"to find a way of converting tblNames to tblTasks":

1) load tblNames using e.g. ADO's GetRows
(http://www.triadcenters.com/caspdoc/Ch11_ADO94.html)
2) transpose loaded variant array with the code referred above
3) save transposed array into tblTasks row by row...

HTH.

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Chris Enright
Sent: Thursday, April 24, 2008 1:25 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Turn a Table

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




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


      ____________________________________________________________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ