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 ___________|__G eography_|_Hist ory_|_Maths_|_T OTAL_
Geography Applications|__ __________|___2 0___|__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.
4 1417 NeoPa 32,568
Recognized Expert Moderator MVP
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.
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.
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 32,568
Recognized Expert Moderator MVP
Yes. That's a relief. I hardly ever use CrossTabs. That would have been a struggle to work on.
Well done :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Rajani |
last post by:
Hello,
I have a table(msaccess) with the structure...
job_code text 6
style text 10
qty number
fabrication text 65
ship_date date/time
|
by: Greg |
last post by:
I am trying to figure out how to create a report that will return orders
current, over 30, over 60, over 90, and over 120. In one table I have
Cust_Code, INV_Date, INV_Due_Date, INV_Bal and in another table is the
customer information and there current balance. How do I display each
company with the appropriate current, over 30, over 60, over 90, and over
120 and if the value for a total is 0 then display a - (dash).
Thanks for any...
|
by: Warren |
last post by:
I am trying to create a report that does the following:
Access Data in Query:
NAME | DATE | SALE TYPE |
-------------------------
John DOE | 1282003 | TYPE A
Jane DOE | 1282003 | TYPE C
Jane DOE | 1282003 | TYPE D
Jane DOE | 1282003 | TYPE C
|
by: longtim |
last post by:
I have been having endless difficulty creating reports/queries that
set any relevent parameters from controls in forms.
I am creating an application under access 2003 but will target access
2000. The access file is in access 2000 format.
I have a form that will hold the relevent parameters for the
query/report that reports the statistics for all job records that
match a certain criteria. These are:
- A Customer Name.
|
by: Marie |
last post by:
How do you display a total from a subreport on the main report. If it makes
a difference, the total is a total of a calculated field (Qty * Price). Does
the total on the subreport go in the pagefooter or report footer? Say the
total is shown in a texrbox named MyTotal and the subreport control is named
MySubreportControl, what exactly do I put in the control source of a textbox
on the main report to show the total?
Thanks For All Help!
...
| |
by: Shelby |
last post by:
Problem: My company generates its own data export from a propietary
database. These (free) tables can be read in C#.NET using a Visual FoxPro
driver (vfpoledb). I can read each of the six tables into its own datatable,
modify them, and add them to a dataset. It take approximately 15 minutes to
pass that dataset to Crystal Reports (45 minutes if the report uses three
subreport datasets). Then it takes over 7 hours for Crystal to...
|
by: mariat101 |
last post by:
I am collecting patient information when they do not show for an apt. I've created 4 tables linked by autonum b/c I have 3 of them in a form as subforms b/c they want to be able to see everything on one screen. Each table has a date attached to it 1) data entry date 2) pt type date 3) intervention date 4) outcome date (all on diff tables). I need to create a report or multiply reports on for example: pt type = "no show" and intervention = total...
|
by: Jana |
last post by:
Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. There are also several other
subreports in the main report's footer. The rptTrustDetails subreport
has two grouping levels MasterClientID and ClientID, with headers and
footers for each grouping level. In the ClientID footer, I have an...
|
by: apartain |
last post by:
My database is based on Work Order Numbers. If an employee enters labor, they must include the WO Number for it. Equipment, subcontractors, per diem and materials all must also have a WO number.
I have the following tables:
Work Orders
Labor (includes per diem and time entries)
Equipment
Subcontractors
Materials
I have created a summary report using the Materials table which lists the WO number, total labor, total equipment, total...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |