By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

Data in columns to rows using query with VBA or SQL

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. Ensemble    EId    Ensemble Area    Serv Label1    SId1    LSN1    Serv Label2    SId2    LSN2    Serv Label3    SId3    LSN3                                                                                                                                        
  2. Archive N East    C192    North East England    Capital    C371    196    REAL RADIO    C3A8    188    Panjab Radio    CDC9    1A0                        
  3. Archive N West    C197    North West England    Capital    C770    196    REAL RADIO    C3AE    188    Real Radio XS    CCB2    1A5                                                                                                            
  4.  
I then need to insert this into another table that looks like this
Expand|Select|Wrap|Line Numbers
  1. Ensemble    EId    Ensemble Area       Serv Label     SId    LSN
  2. Archive N East    C192    North East England Capital     C371    196
  3. Archive N East    C192    North East England REAL RADIO    C3A8    188
  4. Archive N East    C192    North East England Panjab Radio     CDC9    1A0
  5. Archive N West    C197    North West England Capital     C770    196
  6. Archive N West    C197    North West England REAL RADIO     C3AE    188
  7. Archive N West    C197    North West England Real Radio XS CCB2    1A5
  8.  
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
May 6 '14 #1

✓ answered by zmbd

MartinJ
See how you have some of this data repeated over and over again, like 'Archive N East" and "C192" etc...?
Expand|Select|Wrap|Line Numbers
  1. Ensemble    EId    Ensemble Area       Serv Label     SId    LSN
  2. Archive N East    C192    North East England Capital     C371    196
  3. Archive N East    C192    North East England REAL RADIO    C3A8    188
  4. Archive N East    C192    North East England Panjab Radio     CDC9    1A0
  5. Archive N West    C197    North West England Capital     C770    196
  6. Archive N West    C197    North West England REAL RADIO     C3AE    188
  7. Archive N West    C197    North West England Real Radio XS CCB2    1A5
  8.  
It Looks like you could take:
Ensemble EId Ensemble Area

to a tbl_Ensemble
Expand|Select|Wrap|Line Numbers
  1. [Ensemble_pk] [Ensemble_eid] [Ensemble_name]  [Ensemble_area]
  2.        1           C192       Archive N East   North East England
  3.        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
Expand|Select|Wrap|Line Numbers
  1. [Serv_PK]   [Serv_Label]
  2.    1           Capital
  3.    2           REAL RADIO
  4.    3           Panjab Radio
  5.    4           Real Radio
Using the two tables above to recreate your origninal table
Then your table becomes:
tbl_newdata
Expand|Select|Wrap|Line Numbers
  1. [PK]    [fk_Ensemble]   [fk_Serv]   [SID]   [LSN]
  2. 1           1              1         C371    196
  3. 2           1              2         C3A8    188
  4. 3           1              3         CDC9    1A0
  5. 4           2              1         C770    196
  6. 5           2              2         C3AE    188
  7. 6           2              4         CCB2    1A5
  8.  
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.

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 6 '14 #2

Expert 100+
P: 1,240
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
May 6 '14 #3

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. Ensemble    EId    Ensemble Area    Serv Label1    SId1    LSN1    Serv Label2    SId2    LSN2    Serv Label3    SId3    LSN3
  2. Aberdeen    C19A    Aberdeen    Northsound 1    C8B1        Northsound 2    C1C9        Waves Radio    C6B6    
  3. Archive N East    C192    North East England    Capital    C371    196    REAL RADIO    C3A8    188    Panjab Radio    CDC9    1A0
  4. Archive N West    C197    North West England    Capital    C770    196    REAL RADIO    C3AE    188    Real Radio XS    CCB2    1A5
  5.  
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 }}
May 6 '14 #4

Expert 100+
P: 1,240
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
May 6 '14 #5

P: 4
Have attached Excel output of the query and how the final table would look. This should help, many thanks
Martin
Attached Files
File Type: xls Qry_DAB_Service.xls (33.0 KB, 284 views)
File Type: xls SidSort_Table.xls (20.5 KB, 263 views)
May 6 '14 #6

P: 4
Actual SQL query code is
Expand|Select|Wrap|Line Numbers
  1. SELECT [DAB Service Details].Ensemble
  2.    , [DAB Service Details].EId
  3.    , [DAB Service Details].[Ensemble Area]
  4.    , [DAB Service Details].[Serv Label1]
  5.    , [DAB Service Details].SId1
  6.    , [DAB Service Details].LSN1
  7.    , [DAB Service Details].[Serv Label2]
  8.    , [DAB Service Details].SId2
  9.    , [DAB Service Details].LSN2
  10.    , [DAB Service Details].[Serv Label3]
  11.    , [DAB Service Details].SId3
  12.    , [DAB Service Details].LSN3
  13.    , [DAB Service Details].[Serv Label4]
  14.    , [DAB Service Details].SId4
  15.    , [DAB Service Details].LSN4
  16.    , [DAB Service Details].[Serv Label5]
  17.    , [DAB Service Details].SId5
  18.    , [DAB Service Details].LSN5
  19.    , [DAB Service Details].[Serv Label6]
  20.    , [DAB Service Details].SId6
  21.    , [DAB Service Details].LSN6
  22.    , [DAB Service Details].[Serv Label7]
  23.    , [DAB Service Details].SId7
  24.    , [DAB Service Details].LSN7
  25.    , [DAB Service Details].[Serv Label8]
  26.    , [DAB Service Details].SId8
  27.    , [DAB Service Details].LSN8
  28.    , [DAB Service Details].[Serv Label9]
  29.    , [DAB Service Details].SId9
  30.    , [DAB Service Details].LSN9
  31.    , [DAB Service Details].[Serv Label10]
  32.    , [DAB Service Details].SId10
  33.    , [DAB Service Details].LSN10
  34.    , [DAB Service Details].[Serv Label11]
  35.    , [DAB Service Details].SId11
  36.    , [DAB Service Details].LSN11
  37.    , [DAB Service Details].[Serv Label12]
  38.    , [DAB Service Details].SId12
  39.    , [DAB Service Details].LSN12
  40.    , [DAB Service Details].[Serv Label13]
  41.    , [DAB Service Details].SId13
  42.    , [DAB Service Details].LSN13
  43.    , [DAB Service Details].[Serv Label14]
  44.    , [DAB Service Details].SId14
  45.    , [DAB Service Details].LSN14
  46.    , [DAB Service Details].[Serv Label15]
  47.    , [DAB Service Details].SId15
  48.    , [DAB Service Details].LSN15
  49.    , [DAB Service Details].[Serv Label16]
  50.    , [DAB Service Details].SId16
  51.    , [DAB Service Details].LSN16
  52.    , [DAB Service Details].[Serv Label17]
  53.    , [DAB Service Details].SId17
  54.    , [DAB Service Details].LSN17
  55.    , [DAB Service Details].[Serv Label18]
  56.    , [DAB Service Details].SId18
  57.    , [DAB Service Details].LSN18
  58.    , [DAB Service Details].[Serv Label19]
  59.    , [DAB Service Details].SId19
  60.    , [DAB Service Details].LSN19
  61.    , [DAB Service Details].[Serv Label20]
  62.    , [DAB Service Details].SId20
  63.    , [DAB Service Details].LSN20
  64. FROM [DAB Service Details]
  65. WHERE ((([DAB Service Details].Ensemble) 
  66.       Not Like "ofcom*"));
  67.  
May 6 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
MartinJ
See how you have some of this data repeated over and over again, like 'Archive N East" and "C192" etc...?
Expand|Select|Wrap|Line Numbers
  1. Ensemble    EId    Ensemble Area       Serv Label     SId    LSN
  2. Archive N East    C192    North East England Capital     C371    196
  3. Archive N East    C192    North East England REAL RADIO    C3A8    188
  4. Archive N East    C192    North East England Panjab Radio     CDC9    1A0
  5. Archive N West    C197    North West England Capital     C770    196
  6. Archive N West    C197    North West England REAL RADIO     C3AE    188
  7. Archive N West    C197    North West England Real Radio XS CCB2    1A5
  8.  
It Looks like you could take:
Ensemble EId Ensemble Area

to a tbl_Ensemble
Expand|Select|Wrap|Line Numbers
  1. [Ensemble_pk] [Ensemble_eid] [Ensemble_name]  [Ensemble_area]
  2.        1           C192       Archive N East   North East England
  3.        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
Expand|Select|Wrap|Line Numbers
  1. [Serv_PK]   [Serv_Label]
  2.    1           Capital
  3.    2           REAL RADIO
  4.    3           Panjab Radio
  5.    4           Real Radio
Using the two tables above to recreate your origninal table
Then your table becomes:
tbl_newdata
Expand|Select|Wrap|Line Numbers
  1. [PK]    [fk_Ensemble]   [fk_Serv]   [SID]   [LSN]
  2. 1           1              1         C371    196
  3. 2           1              2         C3A8    188
  4. 3           1              3         CDC9    1A0
  5. 4           2              1         C770    196
  6. 5           2              2         C3AE    188
  7. 6           2              4         CCB2    1A5
  8.  
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.
May 6 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.