gigifarrow
asked on
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.
I 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
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.
I 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
IrogSinta
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Maybe it is not possible because BUSK is not in the field?
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.
Maybe it is not possible because BUSK is not in the field?
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:
Here's a screen shot of the crosstab in the revision I gave you. I filtered it just for AFES, AOA, and BUSK.
M2 M3 M7
AFES 1 2 7
AFES 1 2 7
ASKER
Ron you are so correct I didnt see it . Thank you again for your help! YOU are awesome!!