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

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...

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

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
  4. UNION
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.

May 1 '09 #4

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.