# 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.
HelpWithCrossTabsScope.accdb
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
can you post the formula, use the name of the fields...
0
MIS LiasonCommented:
...So what are the exact output values you are seeking based on your sample data?
0
MIS LiasonCommented:
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...
0
Author 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?

Example
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
0
MIS 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...?
0
Commented:
Do you have a table that holds the Scopes for the various Mod Kits?
0
Commented:
@gigifarrow

<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

.
0
Author 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
0
MIS LiasonCommented:
fyed, capricorn1...
I'll leave this to you guys...
0
Commented:
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.
0
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
0

Experts Exchange Solution brought to you by