SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,023
    Mentioned
    211 Post(s)
    Tagged
    1 Thread(s)

    SLQ Query... Values seens as column names

    I need to do a query which I suspect it is not that difficult but obvioulsy I'm not searching for the right terms because I can't find the information anywhere in the world.

    I'm using Access (because that's what I've been asked to) and I have a table that with four fields. I need to select two of those and show the values of one of them as the name of the column.

    Let me give you an example. These fields have the following structure:

    Code:
    jobPosition  WorkerName
    Salesman    Andrew Smith
    Manager     Susan Roberts
    Director      James Stwart
    The output I need is
    Code:
    Salesman                  Manager                  Director
    Andrew Smith            Susan Roberts           James Stwart
    Any thoughts? I would have thought that a TRANSFORM would do... except that you need to use aggregate functions and a file header (which I don't want)

    Thansk in advance.

    edit: It may be good to add that this query will be used to combine this table with a second table which has more information.

    One of the fields in this table I've shown is a foreign key. If I do a regular inner join, I would get three files for every record of that table... something that I dont' want.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately there is no TRANSFORM or PIVOT function which will do this. There is a good tutorial here http://www.artfulsoftware.com/infotree/queries.php#78

  3. #3
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Your request has more to do with reports. Trying to bend queries to output formatted reporting results is usually counter productive.

    I'd look for Access reporting and pivot/transpose: http://stackoverflow.com/questions/1...in-access-2003

  4. #4
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,439
    Mentioned
    58 Post(s)
    Tagged
    0 Thread(s)
    If you know in advance what all the job names are, and if there is relatively few of them, I suppose you could do something like this:

    Code:
    SELECT 'Salesman", Worker FROM MyTable WHERE JobPosition = 'Saleman'
    UNION SELECT 'Manger', Worker FROM MyTable WHERE JobPosition = 'Manager'
    UNION SELECT 'Directory', Worker FROM MyTable WHERE JobPosition = 'Director'
    ....
    etc.


    But you probably want a more open-ended solution, in which case that won't work. I just thought I'd throw it in.

    Mike

  5. #5
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,023
    Mentioned
    211 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Mikl View Post
    If you know in advance what all the job names are, and if there is relatively few of them, I suppose you could do something like this:

    Code:
    SELECT 'Salesman", Worker FROM MyTable WHERE JobPosition = 'Saleman'
    UNION SELECT 'Manger', Worker FROM MyTable WHERE JobPosition = 'Manager'
    UNION SELECT 'Directory', Worker FROM MyTable WHERE JobPosition = 'Director'
    ....
    etc.


    But you probably want a more open-ended solution, in which case that won't work. I just thought I'd throw it in.

    Mike
    It is a nice idea... 905% of the times I know the job positions and there are not too many... but there's a 5% that doesn't fit... and thats the issue.

    @Imitica; Thanks. I'll look into that. And you're right. This is for a report... but it will not be seen as an access report but exported to Excel so other departments can manipulate it.

    @rcashell ; Thank you. That looks like a quite complete reference


Bookmarks

Posting Permissions

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