Access question - "trasposing" (sort of)

Factual Questions
1

Hi all

I have a table which looks something as follows:


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:


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

2

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):

3

This thread might give you some ideas:

Excel Gurus: Transpose help needed.

4

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

5

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.

6

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