Creating a total from a tricky report

[Mysterydave]

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.

[NeoPa]

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.

[Mysterydave]

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

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]
FROM JOINTHONAPPUNION
GROUP BY JOINTHONAPPUNION.APPS;
UNION
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]
FROM JOINTHONOFFUNION
GROUP BY JOINTHONOFFUNION.OFFERS;
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]
FROM JOINTHONFIRMUNION
GROUP BY JOINTHONFIRMUNION.[FIRM REP];
 

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.

[Mysterydave]

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

[NeoPa]

Yes. That's a relief. I hardly ever use CrossTabs. That would have been a struggle to work on.

Well done :)