1

I've looked at a number of SQL transpose threads here and all of them deal with restructuring data and/or data summaries. I have a somewhat common situation that doesn't match any of those circumstances. Please keep in mind this is in MS Access, and using a linked table (so, no updating permitted and there's no key or id).

I have the linked table. There are only two long rows of data. Say, it's a listing of unique Business Units (BUs) and corresponding annual sales.

linked_table


     F1     F2     F3     F4    F5  ...
     -----------------------------
row1 BU11   BU123  BU223  BU2   BU432...
row2 $2345  $0     $50.50 $234  $567.22

I need this data set transposed somehow into this:

new_table or new_query

BU       Sales
---------------
BU11     $2345
BU123    $0
BU223    $50.50
BU2      $234
BU342    $567.22
...

Again, there are only two long rows of data in a linked Access table. I've only found a very tedious and ugly way to do about this, and was wondering if there's a better way to do this.

STEP 1 I've separated the two rows of data into 2 separate queries

headers_qry
     F1     F2     F3     F4    F5...
     ------------------------------
row1 BU11   BU123  BU223  BU2   BU432...

data_qry
     F1     F2     F3     F4    F5...
     ------------------------------
row1 $2345  $0     $50.50 $234  $567.22...

Then I've hardcoded a dummy "common key" into both data sets

headers_qry
     F1     F2     F3     F4    F5...       KEY
     ----------------------------------------------
row1 BU11   BU123  BU223  BU2   BU432...    UNIQUE123

data_qry
     F1     F2     F3     F4    F5...       KEY
     -----------------------------------------------
row1 $2345  $0     $50.50 $234  $567.22...  UNIQUE123

Then I've added a join on between the two queries on that common key and manually paired up Header and Data pairs through hundreds of UNION statements. Ouch!!!!

SELECT 
headers.F1 AS BU_Number,
data.F1 AS BU_Sales 
FROM header_data_join_qry UNION ALL
SELECT 
headers.F2 AS BU_Number,
data.F2 AS BU_Sales 
FROM header_data_join_qry UNION ALL
SELECT 
...
headers.F100 AS BU_Number,
data.F100 AS BU_Sales 
FROM header_data_join_qry UNION ALL;

Not only is this tedious and ugly, MS ACCESS can't handle these multiple UNION ALL queries and once I've reached around 100 it started giving the SQL statement too complex error.

Is there a way to handle this better, considering limitations of the linked source table and the MS ACCESS SQL environment? Thanks so much!

Cam Call
  • 23
  • 2
  • You got up to 100? I thought 50 was limit of SELECT lines in UNION. Only alternative I know is VBA writing records to temp table - table is permanent, data is temporary. – June7 Sep 27 '17 at 03:12
  • Have a look at the third answer [here](https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010). Maybe it will help you – cha Sep 27 '17 at 04:36
  • I got to a 100 by separating into 2 sub queries essentially. After that I'd UNION the two as well. That raises ACCESS tolerance above the total of 50... – Cam Call Sep 27 '17 at 11:58

1 Answers1

1

You can try the following thing.

From the starting position where you separated the two rows of data, no common key needed (since they're both one row).

SELECT DLookUp("F" & Number, "headers_qry") AS BU, DLookUp("F" & Number, "data_qry") As Sales
FROM (
SELECT DISTINCT Abs(Ones.ID Mod 10) + Abs(Tens.ID Mod 10) * 10 + Abs(Hundreds.ID Mod 10)*100 As Number 
FROM MSysObjects As Ones, MSysObjects As Tens, MSysObjects As Hundreds
) As NumbersQuery
WHERE Number BETWEEN 1 And 200

Essentially, this query has 2 parts:

  1. A subquery that returns every number between 0 and 999 (technique adapted from Gustav)
  2. An outer query which returns the specified column number from both your queries, and filters the subquery.

The BETWEEN 1 And 200 can be adapted to fit your sample

If higher numbers are needed (0 to 9999) the subquery can also be adapted

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • This is fyre. Thanks! – Cam Call Sep 27 '17 at 11:59
  • @CamCall If this has answered your question, please mark the answer as accepted. Read [here](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) about how it works, and what the advantages are. – Erik A Sep 27 '17 at 12:07
  • I must say tho it taken 10 minutes running in unresponsive state before it returned records. – Cam Call Sep 27 '17 at 12:28
  • because I have no reputation (lol) I can't upvote or accept answers. Thanks for your help tho! – Cam Call Sep 27 '17 at 12:29
  • The unresponsive state for a long time is normal. Both `DLookUp` and the subquery generating the numbers are quite heavy operations, and take time. You can optimize it by using a table with numbers instead of that subquery, but using `DLookUp` hundreds of times will still take time (it gets called twice every row). – Erik A Sep 27 '17 at 12:32
  • Makes sense. Thanks – Cam Call Sep 27 '17 at 12:37