Results 1 to 4 of 4
  1. #1
    jennifers is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    19

    Include all columns in a Query following crosstab query?


    I am wondering how to automatically include all columns in a Query that is following crosstab query? The crosstab will always pull different columns depending on the form date selections etc so my subsequent query that pulls in the columns from that query (along with data from other tables) will need to change automatically. Right now I have it with all possible columns but I do not want to see empty ones and I also want to be prepared for when users add new ones.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a union query, Q1 your data, Q2 have a table with all possible columns & zero values,
    union Q3:
    select * from Q1
    union
    select * from Q2

    run the crosstab on the union qry to get all the columns.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried "Select * From qryYourCrosstabQuery;"?

    Cheers,

  4. #4
    jennifers is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    19

    Include all following a Crosstab query

    I can't do a union because I have left joins and I have two sums I am doing in the subsequent query.

    PARAMETERS [Forms]![Report_Options]![txtFromDate] DateTime, [Forms]![Report_Options]![txtThruDate] DateTime;
    SELECT Table1.Field1, Table1.Field2, Crostab.AllFields, Sum([Crosstab].FieldZ) AS SumOfCrosstabFieldZ, Sum (Table1.Field1(Table2.Field1,0)) AS SumOfAdjustedBalance
    FROM Table1 LEFT JOIN Crosstab (on four fields) LEFT JOIN Table2 (on four fields)

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

Similar Threads

  1. Include null period in crosstab query
    By nggman in forum Queries
    Replies: 2
    Last Post: 10-12-2018, 11:24 AM
  2. Replies: 1
    Last Post: 11-18-2016, 08:41 AM
  3. Replies: 4
    Last Post: 03-29-2014, 01:29 AM
  4. Replies: 2
    Last Post: 08-16-2013, 01:36 AM
  5. Calculating columns in crosstab query
    By GraemeG in forum Queries
    Replies: 11
    Last Post: 06-05-2011, 06:43 PM

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