UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Transpose Columns Into Pertinent Rows, Access 2013    
 
   
PhobiaBlu
post Dec 9 2018, 06:37 PM
Post#1



Posts: 28
Joined: 25-June 12



Hi forum,
importing an xlsx file (CurvesXlsx) with over 150 columns in a db ; I need to transpose columns data and pertinent value like in the second attached screenshot (CurvesAccess).
1000 tks in advance for any help cus I'm blind about crazy.gif
Attached File(s)
Attached File  CurvesXlsx.png ( 42.35K )Number of downloads: 12
Attached File  CurvesAccess.png ( 43.04K )Number of downloads: 4
 
Go to the top of the page
 
PhobiaBlu
post Dec 9 2018, 06:45 PM
Post#2



Posts: 28
Joined: 25-June 12



Here my db
Attached File(s)
Attached File  TransposeColumn.zip ( 30.84K )Number of downloads: 9
 
Go to the top of the page
 
June7
post Dec 9 2018, 09:59 PM
Post#3



Posts: 648
Joined: 25-January 16



First thing to do is complete Column A so there are no blanks. The data must be repeated.

Then set a link to the worksheet.

Options to rearrange the data:

1. UNION queries. There is a limit of 50 SELECT lines in a UNION so this could require 3 or 4 UNION queries, use the UNION as source for INSERT action or just copy/paste records, example of first 2 lines using table in db:

SELECT txtGroup AS GrpName, txtGroup_ AS Status, CDate(43255) AS RecDate, [43255] AS Imp FROM SCurveImport
UNION SELECT txtGroup, txtGroup_, CDate(43262), [43262] FROM SCurveImport;

This must be done in query builder SQL View. Copy/paste the second line and change the field name referenced until you have 50 lines built.

2. VBA code opening and manipulating recordset to write records to table.
This post has been edited by June7: Dec 9 2018, 10:19 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
RJD
post Dec 9 2018, 10:33 PM
Post#4


UtterAccess VIP
Posts: 9,723
Joined: 25-October 10
From: Gulf South USA


Hi: Attached is a way to do this with VBA, using ordinal positions and looping through records. This is just a start and you will have to adapt it, but it uses your data written to another table. See the new module and see if this does what you want. I noted that you had some repeating records, so you will have to deal with that as well.

HTH
Joe
Attached File(s)
Attached File  TransposeColumn_Rev1.zip ( 33.96K )Number of downloads: 16
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
isladogs
post Dec 10 2018, 04:39 AM
Post#5


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


QUOTE
UNION queries. There is a limit of 50 SELECT lines in a UNION

Not quite correct. See Access specifications
You can have up tp 50 joins in a UNION query though each may comprise more than one line
However, if written as a SQL statement, you may hit the maximum length allowed before that.
This post has been edited by isladogs: Dec 10 2018, 04:39 AM

--------------------
Go to the top of the page
 
PhobiaBlu
post Dec 10 2018, 05:15 PM
Post#6



Posts: 28
Joined: 25-June 12



165 Columns and the relative value are are perfectly transpose in 261 records and it works in a snap!
Work will be checked tomorrow in office carefully.
Database will be posted soon as possible in the forum, but without sensitive data.
Mr RJD I shall say SPLENDID, and it's all. applause.gif
Go to the top of the page
 
RJD
post Dec 11 2018, 12:07 AM
Post#7


UtterAccess VIP
Posts: 9,723
Joined: 25-October 10
From: Gulf South USA


Glad that worked well for you. I'm a bit delayed responding as I was traveling today. Do let us know if it checks out well and if we can be of further assistance.

Regards,
Joe

from phone

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Dec 11 2018, 12:18 AM
Post#8



Posts: 3,163
Joined: 27-February 09



UNIONs are brutal slow. Better off appending the pieces to a new table and index it.
Go to the top of the page
 
June7
post Dec 11 2018, 01:26 AM
Post#9



Posts: 648
Joined: 25-January 16



@isladogs, not sure what your point is. Perhaps I should have said "50 SELECT statements". Each SELECT can of course be a statement that has JOIN clauses as well as WHERE. And yes, each SELECT might stretch over multiple lines.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MadPiet
post Dec 11 2018, 01:38 AM
Post#10



Posts: 3,163
Joined: 27-February 09



There's a limit to the length of the SQL Statement being stored. If you do a seriously huge SQL statement, Access can't save it, because the statement is simply too big. Like trying to store a 256 character string in a variable that can only hold 255 characters.

Besides, if you store the data in a nicely normalized table, you can index it... and it should improve the speed of your select queries
Go to the top of the page
 
June7
post Dec 11 2018, 02:13 AM
Post#11



Posts: 648
Joined: 25-January 16



Agreed, but I do have several UNION queries that perform nicely.

64,000 characters would be a very very long statement, hope I never see one big_grin.gif .

RJD beat me to building the VBA option.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MadPiet
post Dec 11 2018, 07:33 AM
Post#12



Posts: 3,163
Joined: 27-February 09



UNION queries are okay on really small datasets, but once they get big, performance is horrible. Just say no.
Go to the top of the page
 
PhobiaBlu
post Feb 8 2019, 02:22 PM
Post#13



Posts: 28
Joined: 25-June 12



Hi Mr Joe. Was my wish post the db but is now over 800.000 records and very huge to be posted!.
Your function it's perfect and open many roads and opportunities to the application.
Thanks again to you and all of you because your advices was/are gold for me.
Go to the top of the page
 
RJD
post Feb 8 2019, 03:04 PM
Post#14


UtterAccess VIP
Posts: 9,723
Joined: 25-October 10
From: Gulf South USA


You are very welcome, from all of us.

Continued success with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 04:28 AM