Results 1 to 8 of 8
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Make a report out of a crosstab query

    I made a crosstab query. I put in the parameters because I'm using a form with the information I want to search for. The query works great. It does exactly what I need it to do. I tried to make a form out of the query. I cannot add fields to the form because it asks me for the parameters again. The same thing happens when I try to make a report out of it.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, Crosstab is the exception.
    in the crosstab query, click PARAMETERS button on the toolbar
    set the form items in each param row.
    forms!myForm!txtbox , string
    etc..


    Crosstab query MUST have the params again. For some reason it cannot read down the line of nested queries.

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I would have to enter the parameters each time I make a different report?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    parameters go in the crosstab query, or if they are in a query which is the source to your crosstab - in that query

    It is always worth specifying the parameters anyway when used in any query because they specify the datatype which reduces the risk of the query engine confusing data types (the initial 'assumption' would be that it is a string)

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIC, the problem with a cross tab as a report or form record source is that depending on the underlying records, you could have more or less fields in the resulting query each time you run it. There's no way to prevent the prompt for fields that can't be found because either there aren't enough being returned for the report, or there are too many.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    If you want to base a form or report on the crosstab query you need to do two things

    1. make the column heading field a generic one - e.g. instead of a date, use the day value - so you only need a maximum of 31 columns and every month will have a day1, day2 etc. Then you don't have to change to controlsources in your form/report controls

    2. to ensure all columns are reported, even if there are blanks complete the query column heading property might be (usuing the date example again) 1,2,3... or "Jan","Feb","Mar" etc

  7. #7
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    It looks like this isn't a good idea. I will find another solution. Thanks.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I have been using crosstab queries for both forms and reports for years and they work well

    I never use parameters.
    The 'trick' is to define the filters in the original query SQL on which the crosstab query is based

    If you wish, you can set the headers in one of two ways
    1. Specify the headers where you always want certain values to appear even if there is no data e.g Jan, Feb, Mar etc
    2. Use code to create a dynamic crosstab report where the headings depend on the data and unused columns are hidden

    Also use code to prevent an error if you have more data columns than exist on your form / report

    See this link for an example of a dynamic crosstab report which you can download to view the code

    https://www.access-programmers.co.uk...d.php?t=294548


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

Similar Threads

  1. How to make a proper crosstab query?
    By Michael.S90 in forum Queries
    Replies: 5
    Last Post: 05-24-2017, 04:38 PM
  2. Replies: 3
    Last Post: 10-24-2014, 12:15 PM
  3. Replies: 1
    Last Post: 07-10-2013, 12:43 PM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. make a temp table from crosstab query
    By stigmatized in forum Programming
    Replies: 0
    Last Post: 07-26-2010, 03:01 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