Results 1 to 3 of 3
  1. #1
    rutica is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2009
    Posts
    5

    crosstab query calculation

    I'm trying to write an IIF statement for the Value of my Crosstab query.

    If you open the attached database, chose a Report and a Date and click the button, you'll see the query (qrySummary) shows 2 columns of percentages. Then there are '1's in the grid of the crosstab query.

    What i would like is:



    for the 4 columns titled: Dev Overrun, Dev Underrun, Maint Overrun, Maint Underrun, replace the '1's with the percentages in the grid.
    It seems like I can do one or the other, but not both.
    --I can either have '1's in the grid for everything (as shown in the qrySummary) or

    --I can have percentages for the Dev Overrun, Dev Underrun, Maint Overrun, Maint Underrun columns, but not have '1's for the other columns (as shown in qrySummary2).

    Can I have both the '1's and the percentages?

    I have spent about 4 hours on this so far, and no luck!
    Help!

  2. #2
    rutica is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2009
    Posts
    5
    Anyone have any ideas for me?

    Thanks,

  3. #3
    rutica is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2009
    Posts
    5
    I figured it out! Yeah.
    If anyone is interested, my Value SQL for my Crosstab query is:

    Expr2: IIf(First([CodeGrouping]) Is Null,Count(tblEscalationReportsDetail![Criteria Code]),IIf(First([CodeGrouping]) In ("Dev Underrun","Dev Overrun"),First(FormatPercent([dev cost Variance],1)),IIf(First([codeGrouping]) In ("Maint Underrun","Maint Overrun"),First(FormatPercent([Maint Cost Variance],1)),Null)))

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

Similar Threads

  1. crosstab query criteria
    By Sharron in forum Queries
    Replies: 3
    Last Post: 12-15-2011, 04:31 AM
  2. Crosstab: Sum of Columns over Row
    By tommaccoy in forum Queries
    Replies: 0
    Last Post: 12-03-2008, 05:26 AM
  3. Crosstab Queries
    By albst130 in forum Queries
    Replies: 0
    Last Post: 03-07-2007, 09:32 AM
  4. Criteria in Crosstab Query
    By wasim_sono in forum Queries
    Replies: 1
    Last Post: 12-12-2006, 05:14 AM
  5. Replies: 2
    Last Post: 11-09-2005, 02:51 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 - Senior Forums