Results 1 to 7 of 7
  1. #1
    MFlood7356 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    6

    Arrow Displaying multiple text values in crosstab query


    I've created a cross tab query using the query wizard. There are two row headings. Center and Team Description. As far as the headings they relate to different teams. Team 1, Team 2, etc. Now the field used calculated for each row column intersection is Person. I was wondering if there is a was to create a function to display all of the persons on the particular team for that center and team description. I was thinking about trying to do something along the lines of name1, name 2, etc. but all I can get the query to do is output the first or last name. I'm familiar with access but haven't dived into much SQL code at all. I've looked all over this forum and internet and have had no luck. Really would appreciate some help thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not following your description. Could you show sample of input data and the output you now get?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MFlood7356 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    6
    Input: Person ||| Team Description ||| Center ||| Team ||||||| John D ||| Communications ||| EXAMP ||| Team 1 And so on.

  4. #4
    MFlood7356 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    6
    Output: ||| Center ||| Team Description ||| Team 1 ||| Team 2 ||| Team 3|||||| EXAMP ||| Communications ||| John D ||| ||| ||| (Now say theres another person in the Center: EXAMP, Team Description: Communications, and on Team 1 they would be placed in the same field as John D)

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Crosstab can't do this. How many teams. Create a field for each team with an expression:
    SELECT Center, Team Description, IIf(Team="Team1", Person, Null) As Team1, IIf(Team="Team2, Person,Null), ...;

    How does this structure help you? Why not just a regular SELECT with sorting on the Center and Team Description fields?
    Last edited by June7; 06-28-2011 at 11:28 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    MFlood7356 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    6
    I'm using it to generate a report from a sharepoint site. I'm going about it this way because the format the report needs to be cannot be achieved from the table layout. I'm getting a syntax error reguarding the expression 'Team Description' when I try to build the query right now. Heres my code: SELECT Center, Team Description, IIf(Team="1", Person, Null) As Team1, IIf(Team="2, Person,Null), IIf(Team="3, Person,Null), IIf(Team="Pool, Person,Null), IIf(Team="Shift, Person,Null); Sorry I'm very new to coding in SQL

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are missing second " around the text values in each of the IIf expressions, except the first one.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to sum values in Crosstab query?
    By Buakaw in forum Queries
    Replies: 3
    Last Post: 03-22-2011, 01:21 AM
  2. Replies: 9
    Last Post: 12-13-2010, 09:25 PM
  3. Replies: 1
    Last Post: 02-05-2010, 08:33 AM
  4. query field with multiple values
    By mknowles in forum Queries
    Replies: 1
    Last Post: 11-24-2009, 11:31 AM
  5. Query multiple values in a field
    By JAYgarti in forum Access
    Replies: 0
    Last Post: 07-09-2009, 09:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums