Data in columns to rows using query with VBA or SQL

[MartinJ]

I have a lot of data stored in a table, I have written a query to extract the data I need it looks like this Headings

Ensemble	EId	Ensemble Area	Serv Label1	SId1	LSN1	Serv Label2	SId2	LSN2	Serv Label3	SId3	LSN3																																		
Archive N East	C192	North East England	Capital	C371	196	REAL RADIO	C3A8	188	Panjab Radio	CDC9	1A0						
Archive N West	C197	North West England	Capital	C770	196	REAL RADIO	C3AE	188	Real Radio XS	CCB2	1A5

I then need to insert this into another table that looks like this

Ensemble	EId	Ensemble Area	   Serv Label	 SId	LSN
Archive N East	C192	North East England Capital	 C371	196
Archive N East	C192	North East England REAL RADIO    C3A8	188
Archive N East	C192	North East England Panjab Radio	 CDC9	1A0
Archive N West	C197	North West England Capital	 C770	196
Archive N West	C197	North West England REAL RADIO	 C3AE	188
Archive N West	C197	North West England Real Radio XS CCB2	1A5

I actually have 20 Service labels, SID's and LSn's but the above shows what is required I'd be grateful for any help. I'm using MS Access 2007 on Win7 64 bit

[Seth Schrock]

As you can see from looking at your post, the formatting doesn't get saved in the normal section of the post. Try using the [CODE/] button and then put your data inside the code tags. This will preserve the text formatting and make it easier for us to understand what you are trying to do.

[jimatqsi]

MartinJ,
It seems to me you may want to study how to make an "append query". You'll use the query editor, right-click in the top half and choose query type Append. The input to this query could be the table itself or the query you wrote to extract the data. The append to table will be the the table you are trying to put the data into.

Jim

[MartinJ]

Hi Seth,
Thanks for the hint I was trying to be clever by cutting my data down.
Here is the query I want to use

Ensemble	EId	Ensemble Area	Serv Label1	SId1	LSN1	Serv Label2	SId2	LSN2	Serv Label3	SId3	LSN3
Aberdeen	C19A	Aberdeen	Northsound 1	C8B1		Northsound 2	C1C9		Waves Radio	C6B6	
Archive N East	C192	North East England	Capital	C371	196	REAL RADIO	C3A8	188	Panjab Radio	CDC9	1A0
Archive N West	C197	North West England	Capital	C770	196	REAL RADIO	C3AE	188	Real Radio XS	CCB2	1A5

Hope this makes it clearer.

{{Poster's Edit Comment:
Last edited by MartinJ; 54 Minutes Ago at 11:01 AM. Reason: Having looked at the post it hasn't made any difference I can attach an excel sheet if that makes it better }}

[jimatqsi]

The closing tag should have a / in it.
[/CODE]

If you follow Seth's advice and you use the [CODE/] button provided, it will form the tags for you automatically.

JIm

[MartinJ]

Update

Have attached Excel output of the query and how the final table would look. This should help, many thanks
Martin

[MartinJ]

Actual SQL query code is

SELECT [DAB Service Details].Ensemble
   , [DAB Service Details].EId
   , [DAB Service Details].[Ensemble Area]
   , [DAB Service Details].[Serv Label1]
   , [DAB Service Details].SId1
   , [DAB Service Details].LSN1
   , [DAB Service Details].[Serv Label2]
   , [DAB Service Details].SId2
   , [DAB Service Details].LSN2
   , [DAB Service Details].[Serv Label3]
   , [DAB Service Details].SId3
   , [DAB Service Details].LSN3
   , [DAB Service Details].[Serv Label4]
   , [DAB Service Details].SId4
   , [DAB Service Details].LSN4
   , [DAB Service Details].[Serv Label5]
   , [DAB Service Details].SId5
   , [DAB Service Details].LSN5
   , [DAB Service Details].[Serv Label6]
   , [DAB Service Details].SId6
   , [DAB Service Details].LSN6
   , [DAB Service Details].[Serv Label7]
   , [DAB Service Details].SId7
   , [DAB Service Details].LSN7
   , [DAB Service Details].[Serv Label8]
   , [DAB Service Details].SId8
   , [DAB Service Details].LSN8
   , [DAB Service Details].[Serv Label9]
   , [DAB Service Details].SId9
   , [DAB Service Details].LSN9
   , [DAB Service Details].[Serv Label10]
   , [DAB Service Details].SId10
   , [DAB Service Details].LSN10
   , [DAB Service Details].[Serv Label11]
   , [DAB Service Details].SId11
   , [DAB Service Details].LSN11
   , [DAB Service Details].[Serv Label12]
   , [DAB Service Details].SId12
   , [DAB Service Details].LSN12
   , [DAB Service Details].[Serv Label13]
   , [DAB Service Details].SId13
   , [DAB Service Details].LSN13
   , [DAB Service Details].[Serv Label14]
   , [DAB Service Details].SId14
   , [DAB Service Details].LSN14
   , [DAB Service Details].[Serv Label15]
   , [DAB Service Details].SId15
   , [DAB Service Details].LSN15
   , [DAB Service Details].[Serv Label16]
   , [DAB Service Details].SId16
   , [DAB Service Details].LSN16
   , [DAB Service Details].[Serv Label17]
   , [DAB Service Details].SId17
   , [DAB Service Details].LSN17
   , [DAB Service Details].[Serv Label18]
   , [DAB Service Details].SId18
   , [DAB Service Details].LSN18
   , [DAB Service Details].[Serv Label19]
   , [DAB Service Details].SId19
   , [DAB Service Details].LSN19
   , [DAB Service Details].[Serv Label20]
   , [DAB Service Details].SId20
   , [DAB Service Details].LSN20
FROM [DAB Service Details]
WHERE ((([DAB Service Details].Ensemble) 
      Not Like "ofcom*"));

[zmbd]

MartinJ
See how you have some of this data repeated over and over again, like 'Archive N East" and "C192" etc...?

Ensemble	EId	Ensemble Area	   Serv Label	 SId	LSN
Archive N East	C192	North East England Capital	 C371	196
Archive N East	C192	North East England REAL RADIO    C3A8	188
Archive N East	C192	North East England Panjab Radio	 CDC9	1A0
Archive N West	C197	North West England Capital	 C770	196
Archive N West	C197	North West England REAL RADIO	 C3AE	188
Archive N West	C197	North West England Real Radio XS CCB2	1A5

It Looks like you could take:
Ensemble EId Ensemble Area

to a tbl_Ensemble

[Ensemble_pk] [Ensemble_eid] [Ensemble_name]  [Ensemble_area]
       1           C192       Archive N East   North East England
       2           C197       Archive N West   North East England

not sure, from your data; however, this "ensemble" table might even be normalized further.

Then you have what looks like services:
Tbl_serv

[Serv_PK]   [Serv_Label]
   1           Capital
   2           REAL RADIO
   3           Panjab Radio
   4           Real Radio

Using the two tables above to recreate your origninal table
Then your table becomes:
tbl_newdata

[PK]    [fk_Ensemble]   [fk_Serv]   [SID]   [LSN]
1           1              1         C371    196
2           1              2         C3A8    188
3           1              3         CDC9    1A0
4           2              1         C770    196
5           2              2         C3AE    188
6           2              4         CCB2    1A5

This is alot more compact for data storage and say in Service "Panjab Radio" is purchased by some other company and it renames it to "Rapping and Jabbing Radio" you only need to change the one entry in tbl_serv

Also, take a look at how much simpler the query to find all of "Ensemble" for say
"C192 Archive N East North East England" you only need a simple WHERE ([fk_Ensemble]=1);
(now if that "C192" was unique then you might beable to use that as a primary key; however, the numerics are much less memory intensive and if you use the Autonumber and that "C192" becomes "DX194" in the future, then you do not have to re-write your queries. - the PK should never have any meaning except to provide a unique record id.)

Because I don't know your data it's hard to give you an example of normalization; however, you should take a look at: >> Database Normalization and Table Structures.
As you say you have some "20 Service labels, SID's and LSn's " to work with, it might be very well worth your time to normalize things now.