Results 1 to 11 of 11
  1. #1
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904

    Using Crosstab Queries for Data Lists?

    I openly admit, I seldom use Crosstab Queries, and the few times that I have, I am usually performing some mathematical computation on the data.


    However, what if I just want to list data going across? It seems like it should be some simple, but it is stumping me. I have tried Googling it, but haven't found it (everything I find is more complex).

    Here is a really simple example of a table with two bits of data:

    Code:
    Parent     Child
    Adam       Ben
    Adam       Chris
    Adam       Donna
    Mary       Bill
    Ron        Steve
    Ron        Tara
    And I would like the Crosstab Query to return results like this:
    Code:
    Parent    Child1    Child2    Child3
    Adam      Ben       Chris     Donna
    Mary      Bill
    Ron       Steve     Tara
    Seems like it should be so simple, yet it eludes me...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Unless you have a field in table that numbers the children for each parent (that numbering field would become the column header), there is no easy way to accomplish this. VBA code options:

    1. write data to a temp table in the non-normalized structure

    2. http://allenbrowne.com/func-concat.html
    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
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You can do it even without a "number" field, as long as the records have unique keys. You'd create a subquery against the same table that assigned each child an "order" based on their key - lowest is 1, next is 2, and so on.

    That would look roughly like one of these:
    Code:
    MyTable
    MyKey   PK
    Parent  Text
    Child   Text
     
    SELECT 
       T1.MyKey, 
       T1.Parent, 
       T1.Child,
       (SELECT Count(*) FROM MyTable AS T2
        WHERE T1.Parent = T2.Parent 
          AND T1.MyKey <= T2.MyKey) AS Childnumber
    FROM MyTable AS T1;  
    
    SELECT 
       T1.MyKey, 
       T1.Parent, 
       T1.Child, 
       Count(T2.Child) AS ChildNumber
    FROM 
       MyTable AS T1, 
       MyTable AS T2  
    WHERE T1.Parent = T2.Parent 
      AND T1.MyKey <= T2.MyKey
    GROUP BY T1.MyKey, T1.Parent, T1.Child;
    The second syntax looks better at the moment, but the first should work as well. Both are aircode, so they're worth precisely what you paid for them.

    I don't think a crosstab query is exactly what you need, though. You're not adding up the kid's names, so crosstab just isn't the right concept.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Very nice Dal. I should have presented that as another option.

    Joe, if table doesn't currently have a unique ID field, just add an autonumber type field to the table. If you need more info about subqueries, review: http://allenbrowne.com/subquery-01.html

    A crosstab could be used if there was a group numbering field because the aggregate function used would be First.
    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.

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thanks for the tips, guys. I am trying to help someone else do this, and wasn't sure how to accomplish it. In the back of my mind, I thought not having a numbering field could be problematic.
    Anyway, its nice to know I wasn't overlooking anything obvious.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Dal,

    I tested out your solutions, and they work well with preparing the data for a Crosstab Query, with one small caveat.
    In the case in which order matters (which is not always necessarily the case), the code as written lists the Children in reverse order.
    However, I was able to remedy that easily by changing these rows:
    Code:
    AND T1.MyKey <= T2.MyKey
    to this:
    Code:
    AND T1.MyKey >= T2.MyKey
    To be fair, I never mentioned whether or not order matters (and I have to check with the user to see if it does). But in any event, your code gave me what I need.

    Thanks!

  7. #7
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    It's not a crosstab, but it turned out to be not that hard, after sleeping on it.

    There's a standard method for spreading data sideways using IIF for each horizontal slot. It's normally used with SUM and GROUP BY, but for text data you could use subqueries and FIRST to make it happen.

    Here's the table structure:
    Code:
    tblParentChild
    pcPK      PK
    pcParent  Text
    pcChild   Text
    Here's the query, using the new field names, to assign child number:
    Code:
    Query1:
    SELECT 
       T1.pcPK, 
       T1.pcParent, 
       T1.pcChild, 
       Count(T2.pcChild) AS pcChildNo
    FROM 
       tblParentChild AS T1, 
       tblParentChild AS T2  
    WHERE T1.pcParent = T2.pcParent 
      AND T1.pcPK >= T2.pcPK
    GROUP BY T1.pcPK, T1.pcParent, T1.pcChild;
    
    Results of Query3:
    pcPK  pcParent pcChild
     1      Adam     Ben
     2      Adam     Chris
     3      Adam     Donna
     4      Mary     Bill
     5      Ron      Steve
     6      Ron      Tara
    Here's a second query just to get the total number of children for any particular parent, because it will simplify the final combination query:
    Code:
    Query2:
    SELECT 
       T3.pcParent, 
       Count(T3.pcChild) AS pcChildCt
    FROM 
       tblParentChild AS T3  
    GROUP BY T3.pcParent;
    
    Results of Query2:
    pcParent pcChildCt
    Adam          3   
    Mary          1   
    Ron           2
    And here's the query you were looking for, using the above two queries as components:
    Code:
    Query3:
    SELECT
       Q2.pcParent,   
       Q2.pcChildCt,
       (SELECT First(Q11.pcChild) 
       FROM Query1 AS Q11 
       WHERE Q11.pcParent = Q2.pcParent
       AND Q11.pcChildNo = 1)  AS pcChild1,
       IIF(pcChildCt>1,(SELECT First(Q12.pcChild) 
       FROM Query1 AS Q12 
       WHERE Q12.pcParent = Q2.pcParent
       AND Q12.pcChildNo = 2),"") AS pcChild2,
       IIF(pcChildCt>2,(SELECT First(Q13.pcChild) 
       FROM Query1 AS Q13 
       WHERE Q13.pcParent = Q2.pcParent
       AND Q13.pcChildNo = 3),"") AS pcChild3,
       IIF(pcChildCt>3,(SELECT First(Q14.pcChild) 
       FROM Query1 AS Q14 
       WHERE Q14.pcParent = Q2.pcParent
       AND Q14.pcChildNo = 4),"") AS pcChild4
    FROM 
       Query2 AS Q2;
    
    Results of Query3:
    pcParent pcChildCt  pcChild1  pcChild2  pcChild3  pcChild4
    Adam          3       Ben       Chris     Donna
    Mary          1       Bill
    Ron           2       Steve     Tara
    FYI, you could go as many slots wide as you felt was likely to happen with your data. If you needed, you could use a VBA routine to find DMAX of Query2.pcChildCt and build Query3 using VBA to accommodate that many children.

    Full Disclosure: The SQL copied in above from these queries was tested as coded, although the results shown above were typed in. Any errors in the table values and results are typos, not a problem with the code.

  8. #8
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    JoeM - ROFL. I found the same order issue so I reversed the sign when testing the above sample code.

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thanks Dal.

    I actually came up with a similar solution that worked.

    1. I created a "Counter" field to the table, and sorted the Table on "Parent" and used VBA to loop through the Recordset and assign a Counter value to each record (starting over at 1 with each new "parent")
    2. Created a query with calculated fields, i.e.
    Code:
    Child1: IIF([Counter]=1,[Child],"")
    3. Repeated step 2, adding more calculated fields, for maximum number of children
    4. Changed the query to an Aggregate query
    5. Changed the Totals Row value on the Calculated Fields to "Max"

    That seemed to return exactly what I wanted!

  10. #10
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Generally speaking, you shouldn't add calculated fields to the table itself. You'd put those in a temporary table that you can delete and recreate it at need.

    That being said, as long as you know what you did, and you know how it worked, then you're good to go. Take care.

  11. #11
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Generally speaking, you shouldn't add calculated fields to the table itself. You'd put those in a temporary table that you can delete and recreate it at need.
    Agreed. For this purpose, it works fine because basically it is a one-time process on a set of data (so no data is altered, edited, etc). If we did it again, it would be on a new data set, so we would delete all current data and start over.

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

Similar Threads

  1. CrossTab Queries
    By wanware in forum Access
    Replies: 1
    Last Post: 06-26-2012, 11:47 AM
  2. CrossTab Queries data source to Excel?
    By Shakenaw in forum Queries
    Replies: 0
    Last Post: 07-05-2011, 08:26 AM
  3. How Merging 3 lists with similar client data?
    By tdaccess in forum Queries
    Replies: 3
    Last Post: 04-13-2011, 09:57 AM
  4. Replies: 0
    Last Post: 03-31-2009, 02:05 AM
  5. Crosstab Queries
    By albst130 in forum Queries
    Replies: 0
    Last Post: 03-07-2007, 09:32 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