Results 1 to 3 of 3
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Report using a Crosstab Query

    I know crosstab queries have some special rules, like declaring variables. It's been awhile since I've worked with them and I can't recall how to deal with this particular problem.

    I am using this crosstab as a rowsource for my report. The column heading field, SampleID, can fluxuate between 2 and 8 columns of data, so my report has 8 columns to receive it. This only works if the query generates all 8 column. Anything less will generate a Microsoft Access database engine error. I seem to recall there is a way to tell the query to generate extra predefined columns as needed, but I can't remember how.

    Does this ring a bell? I hope so.

  2. #2
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Found it in Access Help of all places.

    You can (I can) use the Columns Property to specify the column headings needed. It's all coming back to me now. Thanks for listening.

  3. #3
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Formula from hell

    I have another question so I set this back to "Unsolved". Here goes.

    I need to performs some calculations in the report such the average of these values in fields 001 through 008. I've come up with a god awful formula, but it does work. Tell me there's an easier way than this. Sorry to make you read this.

    =Round((Nz([001])+Nz([002])+Nz([003])+Nz([004])+Nz([005])+Nz([006])+Nz([007])+Nz([008]))/(IIf(IsNull([001]),0,1)+IIf(IsNull([002]),0,1)+IIf(IsNull([003]),0,1)+IIf(IsNull([004]),0,1)+IIf(IsNull([005]),0,1)+IIf(IsNull([007]),0,1)+IIf(IsNull([007]),0,1)+IIf(IsNull([008]),0,1)),2)

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

Similar Threads

  1. Report based on crosstab query
    By pbuecken in forum Reports
    Replies: 7
    Last Post: 01-16-2012, 09:59 PM
  2. Replies: 2
    Last Post: 08-17-2011, 03:02 AM
  3. Crosstab Report (unbound)
    By belebala in forum Reports
    Replies: 0
    Last Post: 07-06-2011, 03:56 PM
  4. Replies: 0
    Last Post: 05-09-2011, 01:51 PM
  5. Conditional formatting in crosstab report
    By squirrelmaster in forum Reports
    Replies: 0
    Last Post: 06-24-2010, 06:31 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