469,312 Members | 2,476 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

How do I convert column data into row data using microsoft access?

Can this conversion be done using Microsoft Access?
If so how? Could this be done using TRANSFORM and PIVOT SQL statements?
The Old Format columns are NO and URL
The New Format Columns are No, User1URL and User2URL
Old Format
16 http:/16_User1.html
16 http:/16_User2.html
18 http:/18_User1.html
18 http:/18_User2.html

New Format
NO User1URL User2URL
16 http:/16_User1.html http:/16_User2.html
18 http:/18_User1.html http:/18_User2.html
Sep 16 '10 #1
7 10762
1,134 Expert 1GB
Its called a crosstab query in access

Goto the queries tab and click new. You should see a crosstab query wizard option

Choose that and follow the prompts, you should find yours fairly simple and intuitive to create
Sep 17 '10 #2
The crosstab wizard generates 4 columns of URL information, because each of the abbreviated 4 URL's are variables that has a common element being User1 or User2 buried within it. I was hoping to generate a column of URL information for User1 and a column of URL information for User2 which would be the specified column headings. The crossab wizard does not allow this.
Sep 17 '10 #3
1,134 Expert 1GB
You need to make a query off your table
The query needs to extract the User1,user2 info from the url into a third field so that you get this.
Use cominations of the left() right() and or mid() functions to get it
Expand|Select|Wrap|Line Numbers
  1. NO   USR      URL
  2. 16   User1    http:/16_User1.html
  3. 16   User2    http:/16_User2.html
  4. 18   User1    http:/18_User1.html
  5. 18   User2    http:/18_User2.html
Now you create the crosstab off that with
NO as the row headings
USR as the column headings
and URL for the values

I did it with a mockup of your data and it works with the output thus
Expand|Select|Wrap|Line Numbers
  1.     User1                 User2 
  2. 16  http:/16_User1.html   http:/16_User2.html 
  3. 18  http:/18_User1.html   http:/18_User2.html 
Sep 19 '10 #4
1,134 Expert 1GB
Here is the query I did for the mockup of your data
Expand|Select|Wrap|Line Numbers
  1. SELECT [NO], Left(Right([url],10),5) AS Usr,[url]
  2. FROM TheTable
If the character length of the userID in the url is variable then you could use the InStr() fuction and find the positions of the _ and the . and then get the characters between them.
Sep 20 '10 #5
Thanks for your prompt reply. I didn't phrase my question very well! I also have a term labeled AreaSupport that I did not include in my original problem that does not work with your response. If you could give me the query to delimit the value with the underscore preceding and the period following , I would really appreciate it!
Sep 20 '10 #6
1,134 Expert 1GB
Expand|Select|Wrap|Line Numbers
  3. SELECT NO, 
  4.    Mid([url],InStr([url],"_")+1,InStr([url],".")-InStr([url],"_")-1) AS USR, 
  5.    [url]
  6. FROM TheTable
Sep 20 '10 #7
You have solved all my problems for me! (At least my programming problems)
Sep 21 '10 #8

Post your reply

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

Similar topics

3 posts views Thread by Victoria Holowchak | last post: by
reply views Thread by Michael SL | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.