We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

transpose data using Access Query or macro

keilah
keilah asked
on
Medium Priority
529 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
Comment
Watch Question

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

Author

Commented:
thanks for the feedback let me test and come back to u

Author

Commented:
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
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.

Author

Commented:
ok here is the db......No luck with the Append Query....
db1.mdb
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

Author

Commented:
i am trying...............hold on. i think i have it.

Author

Commented:
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

Author

Commented:
can you tell me what i am doing wrong......
CERTIFIED EXPERT
Top Expert 2016
Commented:
see how this is done using VBA


db1.mdb

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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

Author

Commented:
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.

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2016

Commented:
keilah,

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

Author

Commented:

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.