Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM 2 crosstab fields (Access2000-03)

    I have a query:
    SELECT DISTINCT
    Test.RECRUITER_ID,
    Test.DESCR,
    Test.DESCR1
    FROM
    WHERE (((Test.DESCR)="Good" Or (Test.DESCR)="Bad" Or (Test.DESCR)="Ugly"

    Then I had made a crosstab:
    TRANSFORM Count(Test.DESCR1) AS CountOfDESCR1
    SELECT Test.RECRUITER_ID, Count(Test.DESCR1) AS [Total Of DESCR1]
    FROM Test
    GROUP BY Test.RECRUITER_ID
    PIVOT Test.DESCR;

    Now I need to display TOTAL for 2 fields (((Test.DESCR)="Good" Or (Test.DESCR)="Bad" per Test.RECRUITER_ID

    I can have another crosstab made and insert SUB_Report but I wouldn't want IF there s another way.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUM 2 crosstab fields (Access2000-03)

    You can create a new query based on the crosstab query.
    Include all the fields you need (or use * from the field list), and add a calculated column:

    GoodRBad: [Good]+[Bad]

    Use this query instead of the crosstab query.

    Alternatively, since you mention a report, you can add a text box to the report with control source

    =[Good]+[Bad]

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM 2 crosstab fields (Access2000-03)

    HAHA!
    It would be too easy, Hans!
    I had tried to add these fields in every imaginable way!
    No luck!
    #Error! in place of where SUM should be.
    I was able to do the Total for the other report where there was no exclusion of the "Ugly" - Total is fine!
    Where I needed to exclude "Ugly" - the error!
    Can't see WHY!

    I keep trying while waiting for new ideas but I am pretty out of choises.
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUM 2 crosstab fields (Access2000-03)

    I'd like to see a stripped down copy of your database, but since that is not possible... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM 2 crosstab fields (Access2000-03)

    I am affraid to listen to your attachment! LOL
    Hans,
    I figured I have to insert subreport.
    More then one actually.

    See, crazinest of this Report is that it has 4 parts based on different filtering.
    It was done in PeopleSoft and everyone hate how it is. So I am re-doing it in Access.

    Make take on totals is that when I do [Val1]+[Val2] from a crosstab - it is actually NOT a values but the COUNT of the items in a field which's now names of the columns...so how can I say count value in columnA if it is actually NOT a column and the Value is actually just a COUNT of something else...

    Have I confused you enough? LOL Sorry! I wish I could zip it up!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUM 2 crosstab fields (Access2000-03)

    I'm afraid you've lost me. Sorry.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •