Transpose linked Excel table in Access365

[mike_b]

I am sure this has been asked many times, but I can only find extremely specific answers or "do that in Excel", none of which helps me, unfortunately. I need to pull the data from various linked Excel sheets that several people feed data into, and that I cannot change.



So, here is the problem:

Excel table 1 looks like this:

Order | Delivered | Item1 | Item2 | Item3 | ...
-----------------------------------------------------------------------
OR1 | 1/1/18 | 2 4
OR2 | 5/3/19 | 7 3

...


This table tracks orders for specific items.


Excel Table 2 looks like this:


Item | Qty | weight | description 1 | description 2 | ...
-------------------------------------------------------------------------------------
Item1 | 4 | 5 | text1a | text1b |
Item2 | 27 | 2 | text2a | text2b |
...

This table tracks stock levels and item descriptions.


I want to pull these tables into Access, and then run a query that shows me all information available for an item. Basically everything that is in in a row for an item in Excel table 2 and everything that is in a column from table 1. Table 1 is also sparse, so I want to eliminate those row where I don't have information for the items.

But I can't find any information how I can simply read a table from excel, and then transpose rows and columns.

Anybody here who can give me a pointer?

Thanks.

[mike_b]

oops, looks like the formatting is not what I thought ...

Table 1 looks like this:



Table 2 like this:

[ssanfu]

Welcome to the forum...


I'm not sure I am understanding what you are wanting to do....

If you have Excel Worksheet1 like this:



you want to import the data into Access Table1 like this?

ORDER	DELIVERED	ITEM	QTY
OR1	1/1/2018	Item2	2
OR1	1/1/2018	Item3	4
OR2	5/3/2018	Item1	7
OR2	5/3/2018	Item3	3

Are the "Orders" really named "OR1", "OR2",...?
Are the "Items" really named like "Item2", "Item1",...
Referring to "Worksheet1", how many "ITEMS" can be in a row?



Are "Worksheet1" and "Worksheet2" related? If so, what field is the linking field?

[mike_b]

Hi Steve,

No, the orders have a longer name that I didn't want to type out. They are unique, though ...
Same with the Items. They have some cryptic, but unique names.
Table 1 is an Excel sheet that contains data about the items. At the moment there are roughly 800 lines.

I'd like to see the Table 1 transposed like this:



What I want to do in the end is to create a form where I can select an item from a drop down list, and it shows me the data for the item from table 2 plus the sum of Qty from the transposed table for the item, and the last entry for the item in the transposed table. (This will show me how many of the item we have in stock, and when the next shipment is expected to come in).

[CJ_London]

this is an ongoing thread that seems to be asking much the same question - transpose columns in excel to rows in access

https://www.accessforums.net/showthr...212#post437212

[mike_b]

Thanks, Ajax, will take a closer look in the next couple of days. Looks promising ...

[ssanfu]

The link Ajax posted does look promising.

Since you said

<snip> I need to pull the data from various linked Excel sheets that several people feed data into,<snip>

This doesn't sound like a one time thing.
How often do people update the Excel worksheets?
Do they delete all data in the worksheet, then add new data or do they just keep adding to existing worksheet?

[mike_b]

Yes, that is correct. Not a one-time thing. The Excel tables are live and several people could make changes (although in reality it is only one person, and it happens every few days or so). They could delete, add or change information at any time The biggest changes happen when a new item is introduced, of course, but it is also possible that they add another column to a spreadsheet. You can imaging how long it takes to wade through 3 relatively large Excel sheets (there is one more, but that already has the right format) to find the right information. I am just looking for a better way to handle that ...