Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

transpose data using Access Query or macro

Posted on 2009-02-08
16
Medium Priority
?
516 Views
Last Modified: 2012-05-06
Hi Experts
Sheet 2 = end result

Need to transpose the car data in columns J:U and also transpose the data in columns A:E (see excel workbook) also see db file uploaded with initial table in MS Access database, using MS Access. So the data reads as follows (see sheet 2 excel workbook demo file - showning end result.)

For all the car data the end result has to be like sheet 2 of the excel workbook, but i need to transpose the data using MS Access.



Book1.xls
db1.mdb
0
Comment
Question by:keilah
  • 10
  • 4
  • 2
16 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 23584762
Either import or link the spreadsheet then create append queries that append the all the firlds you need, the first one selecing Mirrow as Final Data, the secon selecting Wipers and so on.

Cheers, Andrew
INSERT INTO MyTable
     ( [Car ID]
     , [Car Name]
     , [Reg ID]
     , [Other]
     , [Batch ID]
     , [Batch Name]
     , [other 1]
     , [other 2]
     , [other 3]
     , [Final Data]
     )
SELECT [Car ID]
     , [Car Name]
     , [Reg ID]
     , [Other]
     , [Batch ID]
     , [Batch Name]
     , [other 1]
     , [other 2]
     , [other 3]
     , [Mirrow] AS [Final Data]
FROM MyExcelTable
WHERE [Mirrow] IS NOT NULL
AND   [Mirrow] <> 0

Open in new window

0
 

Author Comment

by:keilah
ID: 23584774
thanks for the feedback let me test and come back to u
0
 

Author Comment

by:keilah
ID: 23584786
Hi Andrew

Just tried the code cannot get it to work.........help can you kindly add the code to the db i uploaded? Not sure what i am doing wrong
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:TextReport
ID: 23584796
Rather than using the SQL view, just create a new query, select the data from the Excel File and change it to an append query, then you can make the change in the query design for each of the subsequent fields.
Cheers, Andrew
PS I haven't tried to download the MDB file as it is reporting to be ZERO bytes in size.
0
 

Author Comment

by:keilah
ID: 23584811
ok here is the db......No luck with the Append Query....
db1.mdb
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23584845
You really need to try and get the append to work for yourself, there is no advantage gained by getting someone else to do your work for you.
Please have a go and advise what is not working.
Cheers, Andrew
0
 

Author Comment

by:keilah
ID: 23584849
i am trying...............hold on. i think i have it.
0
 

Author Comment

by:keilah
ID: 23584873
ok i am stuck at the stage where i am trying to transpose the data fo each car. Not sure how to do this, see attached db...with the append query result going to sheet 3......not sure on how appply the finishing touch.
db1.mdb
0
 

Author Comment

by:keilah
ID: 23585373
can you tell me what i am doing wrong......
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1400 total points
ID: 23585396
see how this is done using VBA


db1.mdb
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 600 total points
ID: 23585431
From what I can see it is putting the data into Sheet3, you have not told it to exclude ZERO's and BLANKS though and I suspect once you have appended the data into sheet3 that you do not know what the Final Data relates to.

Can you please expand more on the actual problem.

Cheers, Andrew
0
 

Author Comment

by:keilah
ID: 23588025
Hi capricorn1

thanks for the feedback works prefectly, just one silly question can i amend the vba accordingly to replace the column name withthe actual columns name this section

                rs1![Car ID] = rs![Car ID]
                rs1![Car Name] = rs![Car Name]
                rs1![Reg ID] = rs![Reg ID]
                rs1!Other = rs!Other
                rs1![Batch ID] = rs![Batch ID]
                rs1![Batch Name] = rs![Batch Name]
                rs1![other 1] = rs![other 1]
                rs1![other 2] = rs![other 2]
                rs1![other 3] = rs![other 3]
                rs1![end result] = rs.Fields(fldArr(j))

and i am guess the code should work as normal.
0
 

Author Comment

by:keilah
ID: 23588032
Hi Andrew

The db file that capricorn1 has done is what i was looking for. Still would like to learn if this can be done using your Append Method?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23589628
keilah,

yes of course.. just make sure you don't change rs.Fields(fldArr(j))
0
 

Author Comment

by:keilah
ID: 23589854
0
 

Author Comment

by:keilah
ID: 23650642
Hi Capricorn1.....

I am getting a problem with the transpose on the following question it relates to this question modified...


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24146859.html
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question