By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,078 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,078 IT Pros & Developers. It's quick & easy.

Creating a total from a tricky report

P: 28
Hi,

Here is my problem:

I have a report which lists Application numbers, Offer numbers and Firm Replies numbers for a subject i.e. Geography as 3 rows. My column headings also list subjects so you can see the Joint Honours combinations. As not all Subject combinations are possible there are many null fields. For example Geography with Geography. With a rather large IIf statement in a text box I can create a row total, but I want an overall total of just subject applications and that overall total divided by 2 to get the number of applications.

I know this might be tricky to understand I'll try to show you what I mean.

SUBJECT ___________|__Geography_|_History_|_Maths_|_TOTAL_
Geography Applications|____________|___20___|__15___|__[IIf..
Geography Offers_____|____________|____8___|__10___|__[IIf..
Geography Firm Replies|____________|____4___|__6____|__[IIf..
History Applications ___|_____20_____|________|__13___|__[IIf..
History Offers_________|______8_____|________|__12___|__[IIf..
History Firm Replies...
etc.


I would like:______________________________[Total Applications]
_______________________________________(Total Applications]/2)

Then presumably if this is possible I could create totals of offers and firm replies also?

You time and help is greatly appreciated.
May 1 '09 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,709
You would need to use the Sum() function in your Application Total calculation. To determine how to phrase the IIf() function call within that, we'd need a look at the Record Source (SQL) of the report. We'd be hoping for a flag of some sort on a line basis that differentiates the applications from the others.

I'm just hoping it's not a CrossTab query.
May 1 '09 #2

P: 28
It's based on 3 joint select queries as a union, which are each based on 2 crosstab queries as a union.

Expand|Select|Wrap|Line Numbers
  1. SELECT JOINTHONAPPUNION.APPS, Sum(JOINTHONAPPUNION.[ABUSE STUDIES]) AS [SumOfABUSE STUDIES], Sum(JOINTHONAPPUNION.BUSINESS) AS SumOfBUSINESS, Sum(JOINTHONAPPUNION.[CHILDHOOD AND YOUTH STUDIES]) AS [SumOfCHILDHOOD AND YOUTH STUDIES], Sum(JOINTHONAPPUNION.[COACHING STUDIES]) AS [SumOfCOACHING STUDIES], Sum(JOINTHONAPPUNION.[CREATIVE MUSIC PRODUCTIONS]) AS [SumOfCREATIVE MUSIC PRODUCTIONS], Sum(JOINTHONAPPUNION.[CREATIVE WRITING]) AS [SumOfCREATIVE WRITING], Sum(JOINTHONAPPUNION.[CRIME STUDIES]) AS [SumOfCRIME STUDIES], Sum(JOINTHONAPPUNION.[CULTURAL STUDIES]) AS [SumOfCULTURAL STUDIES], Sum(JOINTHONAPPUNION.DANCE) AS SumOfDANCE, Sum(JOINTHONAPPUNION.DRAMA) AS SumOfDRAMA, Sum(JOINTHONAPPUNION.[EDUCATION STUDIES]) AS [SumOfEDUCATION STUDIES], Sum(JOINTHONAPPUNION.ENGLISH) AS SumOfENGLISH, Sum(JOINTHONAPPUNION.[EXERCISE & PHYSICAL ACTIVITY]) AS [SumOfEXERCISE & PHYSICAL ACTIVITY], Sum(JOINTHONAPPUNION.[FILM AND TELEVISION STUDIES]) AS [SumOfFILM AND TELEVISION STUDIES], Sum(JOINTHONAPPUNION.[FINANCIAL MANAGEMENT]) AS [SumOfFINANCIAL MANAGEMENT], Sum(JOINTHONAPPUNION.GEOGRAPHY) AS SumOfGEOGRAPHY, Sum(JOINTHONAPPUNION.[HEALTH STUDIES]) AS [SumOfHEALTH STUDIES], Sum(JOINTHONAPPUNION.[HUMAN RESOURCE MANAGEMENT]) AS [SumOfHUMAN RESOURCE MANAGEMENT], Sum(JOINTHONAPPUNION.[INFORMATION TECHNOLOGY MANAGEMENT]) AS [SumOfINFORMATION TECHNOLOGY MANAGEMENT], Sum(JOINTHONAPPUNION.[JUSTICE AND THE ENVIRONMENT]) AS [SumOfJUSTICE AND THE ENVIRONMENT], Sum(JOINTHONAPPUNION.[LEGAL STUDIES]) AS [SumOfLEGAL STUDIES], Sum(JOINTHONAPPUNION.[LEISURE MANAGEMENT]) AS [SumOfLEISURE MANAGEMENT], Sum(JOINTHONAPPUNION.MARKETING) AS SumOfMARKETING, Sum(JOINTHONAPPUNION.MUSIC) AS SumOfMUSIC, Sum(JOINTHONAPPUNION.[OUTDOOR STUDIES]) AS [SumOfOUTDOOR STUDIES], Sum(JOINTHONAPPUNION.PHILOSOPHY) AS SumOfPHILOSOPHY, Sum(JOINTHONAPPUNION.[POPULAR MUSICS]) AS [SumOfPOPULAR MUSICS], Sum(JOINTHONAPPUNION.PSYCHOLOGY) AS SumOfPSYCHOLOGY, Sum(JOINTHONAPPUNION.SOCIOLOGY) AS SumOfSOCIOLOGY, Sum(JOINTHONAPPUNION.SPORT) AS SumOfSPORT, Sum(JOINTHONAPPUNION.[SPORT DEVELOPMENT]) AS [SumOfSPORT DEVELOPMENT]
  2. FROM JOINTHONAPPUNION
  3. GROUP BY JOINTHONAPPUNION.APPS;
  4. UNION
  5. SELECT JOINTHONOFFUNION.OFFERS, Sum(JOINTHONOFFUNION.[ABUSE STUDIES]) AS [SumOfABUSE STUDIES], Sum(JOINTHONOFFUNION.BUSINESS) AS SumOfBUSINESS, Sum(JOINTHONOFFUNION.[CHILDHOOD AND YOUTH STUDIES]) AS [SumOfCHILDHOOD AND YOUTH STUDIES], Sum(JOINTHONOFFUNION.[COACHING STUDIES]) AS [SumOfCOACHING STUDIES], Sum(JOINTHONOFFUNION.[CREATIVE MUSIC PRODUCTIONS]) AS [SumOfCREATIVE MUSIC PRODUCTIONS], Sum(JOINTHONOFFUNION.[CREATIVE WRITING]) AS [SumOfCREATIVE WRITING], Sum(JOINTHONOFFUNION.[CRIME STUDIES]) AS [SumOfCRIME STUDIES], Sum(JOINTHONOFFUNION.[CULTURAL STUDIES]) AS [SumOfCULTURAL STUDIES], Sum(JOINTHONOFFUNION.DANCE) AS SumOfDANCE, Sum(JOINTHONOFFUNION.DRAMA) AS SumOfDRAMA, Sum(JOINTHONOFFUNION.[EDUCATION STUDIES]) AS [SumOfEDUCATION STUDIES], Sum(JOINTHONOFFUNION.ENGLISH) AS SumOfENGLISH, Sum(JOINTHONOFFUNION.[EXERCISE & PHYSICAL ACTIVITY]) AS [SumOfEXERCISE & PHYSICAL ACTIVITY], Sum(JOINTHONOFFUNION.[FILM AND TELEVISION STUDIES]) AS [SumOfFILM AND TELEVISION STUDIES], Sum(JOINTHONOFFUNION.[FINANCIAL MANAGEMENT]) AS [SumOfFINANCIAL MANAGEMENT], Sum(JOINTHONOFFUNION.GEOGRAPHY) AS SumOfGEOGRAPHY, Sum(JOINTHONOFFUNION.[HEALTH STUDIES]) AS [SumOfHEALTH STUDIES], Sum(JOINTHONOFFUNION.[HUMAN RESOURCE MANAGEMENT]) AS [SumOfHUMAN RESOURCE MANAGEMENT], Sum(JOINTHONOFFUNION.[INFORMATION TECHNOLOGY MANAGEMENT]) AS [SumOfINFORMATION TECHNOLOGY MANAGEMENT], Sum(JOINTHONOFFUNION.[JUSTICE AND THE ENVIRONMENT]) AS [SumOfJUSTICE AND THE ENVIRONMENT], Sum(JOINTHONOFFUNION.[LEGAL STUDIES]) AS [SumOfLEGAL STUDIES], Sum(JOINTHONOFFUNION.[LEISURE MANAGEMENT]) AS [SumOfLEISURE MANAGEMENT], Sum(JOINTHONOFFUNION.MARKETING) AS SumOfMARKETING, Sum(JOINTHONOFFUNION.MUSIC) AS SumOfMUSIC, Sum(JOINTHONOFFUNION.[OUTDOOR STUDIES]) AS [SumOfOUTDOOR STUDIES], Sum(JOINTHONOFFUNION.PHILOSOPHY) AS SumOfPHILOSOPHY, Sum(JOINTHONOFFUNION.[POPULAR MUSICS]) AS [SumOfPOPULAR MUSICS], Sum(JOINTHONOFFUNION.PSYCHOLOGY) AS SumOfPSYCHOLOGY, Sum(JOINTHONOFFUNION.SOCIOLOGY) AS SumOfSOCIOLOGY, Sum(JOINTHONOFFUNION.SPORT) AS SumOfSPORT, Sum(JOINTHONOFFUNION.[SPORT DEVELOPMENT]) AS [SumOfSPORT DEVELOPMENT]
  6. FROM JOINTHONOFFUNION
  7. GROUP BY JOINTHONOFFUNION.OFFERS;
  8. UNION SELECT JOINTHONFIRMUNION.[FIRM REP], Sum(JOINTHONFIRMUNION.[ABUSE STUDIES]) AS [SumOfABUSE STUDIES], Sum(JOINTHONFIRMUNION.BUSINESS) AS SumOfBUSINESS, Sum(JOINTHONFIRMUNION.[CHILDHOOD AND YOUTH STUDIES]) AS [SumOfCHILDHOOD AND YOUTH STUDIES], Sum(JOINTHONFIRMUNION.[COACHING STUDIES]) AS [SumOfCOACHING STUDIES], Sum(JOINTHONFIRMUNION.[CREATIVE MUSIC PRODUCTIONS]) AS [SumOfCREATIVE MUSIC PRODUCTIONS], Sum(JOINTHONFIRMUNION.[CREATIVE WRITING]) AS [SumOfCREATIVE WRITING], Sum(JOINTHONFIRMUNION.[CRIME STUDIES]) AS [SumOfCRIME STUDIES], Sum(JOINTHONFIRMUNION.[CULTURAL STUDIES]) AS [SumOfCULTURAL STUDIES], Sum(JOINTHONFIRMUNION.DANCE) AS SumOfDANCE, Sum(JOINTHONFIRMUNION.DRAMA) AS SumOfDRAMA, Sum(JOINTHONFIRMUNION.[EDUCATION STUDIES]) AS [SumOfEDUCATION STUDIES], Sum(JOINTHONFIRMUNION.ENGLISH) AS SumOfENGLISH, Sum(JOINTHONFIRMUNION.[EXERCISE & PHYSICAL ACTIVITY]) AS [SumOfEXERCISE & PHYSICAL ACTIVITY], Sum(JOINTHONFIRMUNION.[FILM AND TELEVISION STUDIES]) AS [SumOfFILM AND TELEVISION STUDIES], Sum(JOINTHONFIRMUNION.[FINANCIAL MANAGEMENT]) AS [SumOfFINANCIAL MANAGEMENT], Sum(JOINTHONFIRMUNION.GEOGRAPHY) AS SumOfGEOGRAPHY, Sum(JOINTHONFIRMUNION.[HEALTH STUDIES]) AS [SumOfHEALTH STUDIES], Sum(JOINTHONFIRMUNION.[HUMAN RESOURCE MANAGEMENT]) AS [SumOfHUMAN RESOURCE MANAGEMENT], Sum(JOINTHONFIRMUNION.[INFORMATION TECHNOLOGY MANAGEMENT]) AS [SumOfINFORMATION TECHNOLOGY MANAGEMENT], Sum(JOINTHONFIRMUNION.[JUSTICE AND THE ENVIRONMENT]) AS [SumOfJUSTICE AND THE ENVIRONMENT], Sum(JOINTHONFIRMUNION.[LEGAL STUDIES]) AS [SumOfLEGAL STUDIES], Sum(JOINTHONFIRMUNION.[LEISURE MANAGEMENT]) AS [SumOfLEISURE MANAGEMENT], Sum(JOINTHONFIRMUNION.MARKETING) AS SumOfMARKETING, Sum(JOINTHONFIRMUNION.MUSIC) AS SumOfMUSIC, Sum(JOINTHONFIRMUNION.[OUTDOOR STUDIES]) AS [SumOfOUTDOOR STUDIES], Sum(JOINTHONFIRMUNION.PHILOSOPHY) AS SumOfPHILOSOPHY, Sum(JOINTHONFIRMUNION.[POPULAR MUSICS]) AS [SumOfPOPULAR MUSICS], Sum(JOINTHONFIRMUNION.PSYCHOLOGY) AS SumOfPSYCHOLOGY, Sum(JOINTHONFIRMUNION.SOCIOLOGY) AS SumOfSOCIOLOGY, Sum(JOINTHONFIRMUNION.SPORT) AS SumOfSPORT, Sum(JOINTHONFIRMUNION.[SPORT DEVELOPMENT]) AS [SumOfSPORT DEVELOPMENT]
  9. FROM JOINTHONFIRMUNION
  10. GROUP BY JOINTHONFIRMUNION.[FIRM REP];
  11.  
The way the applications, offers and firm replies differ is that I added the text to the field as an expression.

Sorry for all that.
May 1 '09 #3

P: 28
Ok I've fixed my problem, though slighlty fudged.

I created a select query off the big query above summing each subject and made an IIf expression in the row subject and grouped by so it returned Applications, Offers and Firm Replies. Then in a report I could sum these and divide by 2.

Then I added this as a subreport into the original in the report footer and lined up the appropriate fields.

Phew
May 1 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
Yes. That's a relief. I hardly ever use CrossTabs. That would have been a struggle to work on.

Well done :)
May 1 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.