asked on

transpose data using Access Query or macro

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

TextReport

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

keilah

ASKER

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

keilah

ASKER

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

TextReport

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.

keilah

ASKER

ok here is the db......No luck with the Append Query....
db1.mdb

TextReport

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

keilah

ASKER

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

keilah

ASKER

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

keilah

ASKER

can you tell me what i am doing wrong......

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keilah

ASKER

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.

keilah

ASKER

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?

Rey Obrero (Capricorn1)

keilah,

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

keilah

ASKER

Hi Capricorn1.....

how would i do the following

https://www.experts-exchange.com/questions/24125543/query-to-show-one-row-of-data-for-mulitple-enteries.html

keilah

ASKER

Hi Capricorn1.....

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

https://www.experts-exchange.com/questions/24146859/Transpose-of-Data-Access-Problem.html