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

[Ken Jones]

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
NO URL
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

[Delerna]

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

[Ken Jones]

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.

[Delerna]

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

NO   USR      URL
16   User1    http:/16_User1.html
16   User2    http:/16_User2.html
18   User1    http:/18_User1.html
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

    User1                 User2 
16  http:/16_User1.html   http:/16_User2.html 
18  http:/18_User1.html   http:/18_User2.html 
 

[Delerna]

Here is the query I did for the mockup of your data

SELECT [NO], Left(Right([url],10),5) AS Usr,[url]
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.

[Ken Jones]

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!

[Delerna]

 
 
SELECT NO, 
   Mid([url],InStr([url],"_")+1,InStr([url],".")-InStr([url],"_")-1) AS USR, 
   [url]
FROM TheTable
 
 

[Ken Jones]

You have solved all my problems for me! (At least my programming problems)