How do I add a caluclation to calculate certain fields in a query?

I have a cross tab query.

The MOD Kit That doesnt appear  "BUSK "  YOu get that  calculation by adding the total of AOA and AFES then subtract each other to give your the answer. and it need to shows underneath the rest of the MOD Kit. It gives me  2 for M2 and 2 for M3 and 1 for M7 and 6 for total but it shows neagative for some reason.

I tried to do this by going to the orignal query and put the calcualtion in there and then pulled up the crosstab query and added to the query also added Busk to the MOD Kit field thinking that would make it show but it doesnt show under the MOD Kits.

THis is how I need this to show in the query.pI have a cross tab query.

BUSK         2  3      1    6 I need the caluclate field to be underneath the MODkits field,

The MOD Kit That doesnt appear  "BUSK "  YOu get that  calculation by adding the total of AOA and AFES then subtract each other to give your the answer. and it need to shows underneath the rest of the MOD Kit. It gives me  2 for M2 and 2 for M3 and 1 for M7 and 6 for total but it shows neagative for some reason.

I tried to do this by going to the orignal query and put the calcualtion in there and then pulled up the crosstab query and added to the query also added Busk to the MOD Kit field thinking that would make it show but it doesnt show under the MOD Kits.


for example
MOD KIT M2 M3 M7 total
AFES         4   9     3    16
AOA          2   6    2    10
E1 Insert   5   7    8    20
BUSK         2  3      1    6 THis is how I need this to show in the query.
HelpWithCrossTabs.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.

IrogSintaCommented:
This is rather confusing.  You said BUSK is AFES minus AOA yet AOA doesn't appear anywhere in your tblMODHistory.  Also, the calculation you have in your query shows AFES minus "AFES CEP Retrofit."  Can you clear this up?
0
gigifarrowAuthor Commented:
Sorry for the confusing.

 I gave you dummy data and I accidently deleted those fields.Here is a example with it in there. I also changed my calculation to reflect the question I asked. Sorry again.

I am  taking the toatals of each MOD kit  AFES and AOA then subtracting them to come up with the answer for the MOD kit  Named BUSK. then I need it show in the field of the MOD KIts.
HelpWithCrossTabs.accdb
0
IrogSintaCommented:
Here you go.  I changed your query name to qryInstalls, added a query called qryBUSK and a SQL UNION query called qryTotalInstalls that combines the other 2 queries together.  I also compacted your database so that its size is under a meg vs 14 meg.

Ron
HelpWithCrossTabs-rev.accdb
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

gigifarrowAuthor Commented:
Thank you for taking out the time to help me. I do have some questions for you.

This is what Im trying to do. I know that BUSK is not one of the MOD Kits. But It Makes up a MOD kit from the total of AFES and AOA an then subtracts them from each other.
If you look at this example it shows on the busk record that M2 and M3 and M7 are all subtracted from the two mods. then the total is shown at the end.

This is what I was trying to convey. Sorry for the confusing. I really have a hard time explaining myself sometimes.
b

Maybe it is not possible because BUSK is not in the field?
0
IrogSintaCommented:
The revision I uploaded looks similar to what you just posted.  I'm thinking that your tbleMODHistory in the image you just posted is different than the one in the database you uploaded.  If you look at the numbers in the image of your original question, the numbers of AFES look like these:
            M2     M3     M7
AFES      1         2        7
Here's a screen shot of the crosstab in the revision I gave you. I filtered it just for AFES, AOA, and BUSK.
crosstab
0
gigifarrowAuthor Commented:
Ron you are so correct I didnt see it . Thank you again for your help! YOU are awesome!!
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.