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

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


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

Rey Obrero (Capricorn1)Commented:
can you post the formula, use the name of the fields...
0
Jeffrey CoachmanMIS LiasonCommented:
...So what are the exact output values you are seeking based on your sample data?
0
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...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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?


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
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...?
0
IrogSintaCommented:
Do you have a table that holds the Scopes for the various Mod Kits?
0
Rey Obrero (Capricorn1)Commented:
@gigifarrow
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


.
0
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
0
Jeffrey CoachmanMIS LiasonCommented:
fyed, capricorn1...
I'll leave this to you guys...
0
IrogSintaCommented:
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
gigifarrowAuthor Commented:
I have figured it out on my own here is what I did
a
b
That gave me separate scopes then I did a join and put it in my crosstab query
c
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gigifarrowAuthor Commented:
I figure it out on my own
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.