472,779 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,779 software developers and data experts.

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 11256
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

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

Similar topics

by: Victoria Holowchak | last post by:
I was hoping that Microsoft Access 2002 would allow users to see all the columns of an Oracle database table that contained more than 255 columns. I noticed that my Oracle8 ODBC driver is only at...
by: Michael SL | last post by:
I am trying to get the Table Column definitions from a Microsoft Access 2000 database (*.mdb). Using VB with System.Windows.Forms.Form I do the following Dim OpenDBDialog As New OpenFileDialo...
by: Hannie | last post by:
I am currently doing my project using VB.NET I have a database which contains all my data that the user key in. The problem is, when i click the retrieve button on the form, the details that the...
by: dhussong | last post by:
I am attempting to use Visual Studio 2005 (Visual Basic) and the Enterprise Library 2.0 Data Access Application Block with a Microsoft Access database. I know the names of my Access databases but I...
by: glenn | last post by:
Hi folks, Have read a bit on this topic but so far no positive results. I have the following table named 'discussions': id discussionNumber from to status 1 1 ...
by: Steven TK | last post by:
Hi everyone, I wonder who can help me on the filter the Start Date and End Date. I still cannot manage to filter it. Eg. When the user click the StartDate(comboBox as 16/7/07), the Start...
by: mcVBNet10 | last post by:
I am working on a project in VB.Net using access as backend. But I wanted to take advantage of access reporting power. Therefore I was trying to see if I could open an Access Report (Built inside of...
by: BharathP | last post by:
Hi, I need to transfer data of one of the table in DB2 to Microsoft Access. I tried using DBOP data source using Configuration assistant in DB2 and SQL passthrough in Microsoft Access. I think I'm...
by: BharathP | last post by:
Hi, I need to transfer data of one of the table in DB2 to Microsoft Access. I tried using DBOP data source using Configuration assistant in DB2 and SQL passthrough in Microsoft Access. I think I'm...
by: ncsthbell | last post by:
I created a database using full blown access 2007. I have put it out for users to grab and test using Runtime Access 2007. They have entered data and now I need to go back into a table and change a...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.