Transpose tables in access365

[mike_bo]

Hello Everybody. I am new here, and I hope I am in the right spot :)

I need some help with transposing a table. We are using multiple Excel tables to keep track of data. I would like to link them to an Access database so I can set up some queries that pulls data from several of the excel tables. These are tables that contain data about parts. Most of them are organized so that each part is in a separate line of the sheet, such as:

Part 1 | weight | length | price | ...

Part 2 | weight | length | price | ...

etc.

There is one table where the Information is transposed, as lines are added frequently, so:

Part 1    | Part 2     | Part 3     | ...

qty        | qty          | qty         | ...

This table is used to keep track of quantity ordered, and each time an order is entered it is entered into a new line to keep a history,

What I want to to is to set up a query to pull this information together. I specify the part, and the information is displayed from the various spreadsheets. Apparently I have to transpose the second table to do that, and I am trying to figure out how I can do that.

I have looked for solutions, and the answer is usually "do that in Excel", but I don't have that option, as I can't change the Excel sheets as they are (they are used by multiple people and groups and have to stay the way they are.

Is there any way in Access to accomplish this?

Thanks.

[#2]

@mike_bo 

Yes you can do this in Access. If it was for me I would end the use of the Excelsheet because you are keeping track of data what is added all the time. Excel is then not the way to go and Access is. 

It is not difficult to set this up but needs some basic skills to create the database. 

First you can not delete the Excel sheets so you need to import the information to the Access database. 

It would be nice if in the Excel sheet is a column where is registered a partnumber so you can identify every part as unique. If not then you have another challenge :) 

Well I would suggest a imported table based on the sheet where are the parts. A connected table to the excelsheet I would use to query the new parts so you don't have to check it manualy. 

Second I would use the same construction for the transactions. One table with the transactions imported from the excelsheet  and second one connected to the excelsheet. Also here is the need to identify the part so the partnumber should be unique in the table. 

A form with the parts and a subform with the transactions would give you then the result you want. 

There are some skills required to achieve this but a 10-12 hour online course would do the trick I think. 

Greetings,

Marcel