How do you do seperate calculations to data in a Crosstab query?

I have a crosstab query that calculates a scope.
This will do the same calculation for all the data which looks like this.

Some of these totals have different scope percentages. How to I show these calculation in this query? The scope is based on which MODkit is used and dived by a number that is determind by the company AFES scope is 128 then you total how many have been completed and dived it by this number. Gives you the percentage complete.
Who is Participating?
gigifarrowConnect With a Mentor Author Commented:
I have figured it out on my own here is what I did
That gave me separate scopes then I did a join and put it in my crosstab query
Rey Obrero (Capricorn1)Commented:
can you post the formula, use the name of the fields...
Jeffrey CoachmanMIS LiasonCommented:
...So what are the exact output values you are seeking based on your sample data?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Jeffrey CoachmanMIS LiasonCommented:
Something to think about...
Whenever I need something in a crosstab, I try to isolate that data into a field in the source query.
This way, all I might have to do is pull this field into the crosstab...
gigifarrowAuthor Commented:
Here is a example of MOD kits have in differen scopes. Instead of all of them having the same scopes. How do I give every MOD kit a different Scope in query cross tab?

MOD KIT                        M2  M7  M3   Total    Scope      % Complete
 Insert                               2  10    20    35       36           88.9%
AFES CEP Retrofit            16   16     2     34      112         30.36%
AFES                                79   29    11   119    119         100%

 Percentage: Format(Sum([Quantity])/119,"Percent")
Scope: 118* 1
Jeffrey CoachmanMIS LiasonCommented:
I'm confused?...
<  Percentage: Format(Sum([Quantity])/119,"Percent")>
...So 119 is a constant used for all Percentage calculations?

<Scope: 118* 1 >
118* 118 will always be 118, so I am confused here as well (is 118 a constant too?)

Also confusing is that what you are posting here, does not match the data in your sample file.

In other words, in your sample DB
 "Bass M2" has: M2 =NULL, M3 =2, M7=7, for a total of 9
"AOA" has: M2 =0, M3 =2, M7=NULL, for a total of 2

What is the formula that will calculate "Scope" for any Mod Kit?
What are the exact "Scope" values you want for both of these?

...perhaps capricorn1 has a better grasp on this...?
Do you have a table that holds the Scopes for the various Mod Kits?
Rey Obrero (Capricorn1)Commented:
From your post above..

<The scope is based on which MODkit is used> WHERE do you get this scope?

<divided by a number that is determind by the company AFES scope > WHERE do you get this NUMBER

gigifarrowAuthor Commented:
Scope: determined by the company (means how many should be installed)
MOD Kit: Items to be intstalled
Percentage Complete: how many do the have complete based on the scope

Total AFES  Installed: 50    

Total needed to be installed according to the scope: 100  

Percentage complete: 50%           50 / 100 = 50 %

Do you have a tabel with all the scopes?
I dont have a tabel with all the scopes.

Scopes are given to certain units and what MOD KIt there doing.

Maybe that Is something that may make it easier to do.

I just have a query for each unit and then I put the scope in that query.

Different data put in database then in the example

I just did that for a example of what I was trying to do. It really doesnt matter the totals I just need to now how to  come up with the code to do the scope for indivdual totals of MOD kits then divide by the scope to get the Percentage complete all in one cross tab query
Jeffrey CoachmanMIS LiasonCommented:
fyed, capricorn1...
I'll leave this to you guys...
I would say this would be better if you created a table for Scopes rather than revising each query when you have changes or additions.
gigifarrowAuthor Commented:
I figure it out on my own
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.