# Access question - "trasposing" (sort of)

**URL:** <https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892>\
**Category:** Factual Questions\
**Created:** [May 10, 2010, 8:33am UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892 "2010-05-10T08:33:50Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [May 10, 2010, 8:33am UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892/1 "2010-05-10T08:33:50Z")

</div>

Hi all

I have a table which looks something as follows:

```auto

Name	F1	F2	F3	F4	F5	F6
a	Life04 Mat14 			
b	Des01 Life02 Mat14 Vis01 Mat18 Phy12

```

Which I wish to convert to something like this:

```auto

Name	Field	
a	Life04
a	Mat14 	
b	Des01
b	Life02 	
b	Mat14 	
b	Vis01 	
b	Mat18 	
b	Phy12

```

I have done this quite easily through a series of make table and append queries, but every time the data gets updated, I have to run all of them again. Is there an easier (and more dynamic) way to get the info I seek?

Thanks  
Grim

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [May 10, 2010, 9:13am UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892/2 "2010-05-10T09:13:15Z")

</div>

It sounds a bit like the opposite of a crosstab query.

This might help (link to an ‘uncrosstab’ function I wrote a long time ago in a galaxy far away):

> **[Solution: Kind of 'UnCrosstab' code](https://www.access-programmers.co.uk/forums/threads/solution-kind-of-uncrosstab-code.28110/#post-897374)**
>
> I've often wanted code that would take a table with any number of fields, say:
> 
> Store,SalesJan,SalesFeb,SalesMar,SalesApr
> 1,100,150,170,120
> 2,50,75,90,80
> 
> And convert it to a format like:
> 
> Store, Month,...

---

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [May 10, 2010, 9:17am UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892/3 "2010-05-10T09:17:34Z")

</div>

This thread might give you some ideas:

[**Excel Gurus: Transpose help needed.**](http://boards.straightdope.com/sdmb/showthread.php?t=561235)

---

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [May 10, 2010, 10:48am UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892/4 "2010-05-10T10:48:40Z")

</div>

> [@Mangetout](#):
>
> It sounds a bit like the opposite of a crosstab query.
> 
> This might help (link to an ‘uncrosstab’ function I wrote a long time ago in a galaxy far away):  
> [Solution: Kind of 'UnCrosstab' code | Access World Forums](http://www.access-programmers.co.uk/forums/showthread.php?p=897374)

Thank you - that’s exactly what I want to do - still have to press a button, but that’s a whole lot better than 12!!

Grim

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [May 10, 2010, 12:48pm UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892/5 "2010-05-10T12:48:38Z")

</div>

> [@grimpixie](#):
>
> Thank you - that’s exactly what I want to do - still have to press a button, but that’s a whole lot better than 12!!
> 
> Grim

By the way, is there any reason that you can’t keep the data in the ‘after’ format, possibly with an ‘fnumber’ field to indicate which column each value should fall in, and whenever you want to show it cross-tabbed, you can run a crosstab query?

Is that a limitation of the way that the data is updated?

Your table as it is does not seem to be well ‘normalized’, in that the fields are repeaters. That is part of why you’re having trouble with this process.

Just a thought.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [May 10, 2010, 1:55pm UTC](https://boards.straightdope.com/t/access-question-trasposing-sort-of/538892/6 "2010-05-10T13:55:29Z")

</div>

That’s a really good point - It’s a difficult problem precisely because of the non-normalized data.