Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: Convert Column Headings into Rows

    Hi. I have a database set up as follows...

    Dept Name, Q1Answer, Q1Comments, Q2Answer, Q2Comments

    The Dept Name column contains names of departments, Q1Answer contains the response to a question about that department (strongly agree, agree, disagree, etc.), and Q1 comments contains comments related to the answer.

    I need to create a query as follows:
    Question, Answer, Comments <-Column Headings
    Q1, Agree, Q1Comments <-Data
    Q2, Disagree, Q2Comments <-Data

    I've tried to research both union queries and crosstab queries, but I don't think they'll work - or I don't understand how to use them. Any help would be appreciated.

    Also, I realize that the currentway the database is set up is not ideal, but this is how it was given to me and I'm trying to make it work without reinventing the wheel. Thanks.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A union query would be the solution. Presuming you want department:

    SELECT [Dept Name], "Q1" AS Question, Q1Answer AS Answer, Q1Comments AS Comments
    FROM TableName
    UNION ALL
    SELECT [Dept Name], "Q2" AS Question, Q2Answer AS Answer, Q2Comments AS Comments
    FROM TableName
    UNION ALL
    ...

    If some might be empty, you can add a WHERE clause to exclude them.
    Paul

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    Thank you SO much. That worked perfectly. It took a little work to get the syntax just right since the example I posted was a very simplified version of the database and I needed to add some iif() functions and a WHERE clause, but I finally got it and it worked great.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help, and welcome to the site by the way!
    Paul

  5. #5
    Join Date
    Jul 2013
    Posts
    4
    Hi, I have similar problem... My database set up are as follows;

    Area, Code, Contractors Name, and series of Dates as a Column Heading e.g. 01-Jan-2013, 02-Jan-2013, 03-Jan-2013....and so on.... The data under each Date Column are numbers.

    I need to create a table that should look like this;

    Area, Code, Contractors Name, Date, Value > Column Headings; The Date data will come from the Heading Columns..

    Is there a way to manipulate this data? Please help!
    Last edited by gkthomas; 07-04-13 at 03:30.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    can you supply details of your table design?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2013
    Posts
    4
    Hi, all data fields are defined as text except the Dates Column Heading defined as Number.

    If you need the exact detail will send it tomorrow when i get to work. Thanks.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you supply DETAILS of your table design?
    column names? column sizes and so on
    ideally some sample data
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2013
    Posts
    4
    Here it is;
    Column Name, Type, Field Size, Format,Indexed,(Decimal Places)
    Area, Text, 255, blank, No
    Camp, Text, 255, blank, No
    Code, Text, 255, blank, Yes (Duplicates OK)
    Contractors, Text, 255, blank, No
    Contractors Category, Text, 255, blank, No
    01-Jan-03, Number, Double, General Number, Auto, No Indexed
    02-Jan-03, Number, Double, Genernal Number, Auto, No Indexed
    etc, etc... with the DATE Column Headings and will increase as the reporting date continues. At this stage the data I have is up to 15-Jun-2013.

    Sample Data:
    Area, Camp, Code, Contractors, Contractors Category, 01-Jan-2013, 02-Jan-2013.....16-Jun-2013
    North, Woleebee, Allocation, ABC Pty LTd, Others, 40,40,40...0,0,0,....20,20,20,....0,0,0
    North,Woleebee,Utilisation,ABC Pty Ltd, Others, 20,20,20....0,0,0,...20,20,20
    Central,Jordan,Allocation,ZZZZ Pty Ltd, Others, 80,80,80,0,0,0,0,10,10,10
    Central,Jordan,Utilisation,ZZZZ Pty Ltd, Others, 40,40,40,0,0,0,0,0,0,0,0,0,0,0

    Hope this makes sense.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you tried a UNION query like post 2?
    Paul

  11. #11
    Join Date
    Jul 2013
    Posts
    4
    Hi Paul, To be honest I am not confident working with Union or Crosstab Query. I have created a Union Query before but with assistance from a colleague. I have an idea on how to get the result I wanted but it will take a lot of manual manipulation using Access and Xcel.

    Any ideas would greatly appreciated. Thanks.

  12. #12
    Join Date
    Jul 2013
    Posts
    3
    To show headers as rows you can use db metadata as shown in result in REDSql . You can use jdbc metadata.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either do this manually
    create the query then edit it n times till evrey bit of data is processed
    or write a VBA proedurte that iterates through the recordset

    but before you do this you need to normalise your data
    create 5 tabels
    insert the camp, contractor, cateegory into a tbale each, and then use a foreign ney
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •