Solved Moving table headers into fields (1 Viewer)

sroot

Registered User.
Local time
Today, 02:25
Joined
Mar 18, 2013
Messages
53
Hello everyone! i have a table in my database that is pulled from an automated excel sheet that gets pulled online for our supplies. The issue i have is that the table has each header as one of the supplies so it will have 6x6 box then it will have 8x8 box etc... and have the qtys under them. I would like all the supplies listed on the left side in the fields and have the qtys next to that. So basically instead of 80 columns, just have 2 with the item and qty and have 80 rows instead. Is there any way to do this? Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:25
Joined
Feb 19, 2013
Messages
16,553
always helps to provide some example data and the outcome required.

but by the sound of it this will be something easier to do in excel to transform the data before you import it

The alternative in access is to use a union query - one union select for each column. Depending on what the actual outcome is you require, perhaps use vba to loop through each column and append data to a new or existing table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:25
Joined
Oct 29, 2018
Messages
21,358
Hi. I agree. Look up Power Query or Get and Transform Data in Excel.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:25
Joined
Feb 28, 2001
Messages
27,001
There is also the alternative that you open an Excel application object and just read it by columns in a loop. If you do it this way, be sure to close the workbook and the Excel app when done. The nice thing about Excel is that it behaves symmetrically. I.e., you can read one ROW at a time and pick out the cells - because the columns are read as Rows( n ).Cells(m); or you can read one COLUMN at a time and pick out the cells - because the rows are read as Columns(m).Cells( n ). And either one gets you cell(n,m).
 
Last edited:

Users who are viewing this thread

Top Bottom