Subtracting two sum fields on a seperate field on query

  • Thread starter Thread starter instereo911 via AccessMonster.com
  • Start date Start date
I

instereo911 via AccessMonster.com

Good morning,

I have a table that looks like this:
Date | Unit| Call Recvd | Call CFC|

I have a query setup with the following fields:

Field: Date
Table: Large Group
Total: Group by
Show

Field: Unit
Table: Large Group
Total: Where
No Show
criteria: "CU" or "EA" or "EB"

Field: Sum of Call Recvd
Table : Large Group
Total: Sum
Show

Field: Sum of call CFC
Tabe: Large Group
Total: Sum
Show

What I want to do is take the [sum of call recvd] - [sum of call cfc] on a
seperate field but i do not know how to do this. I am hoping this can be done.


I am not too great on VBA..

Thank you everyone again.
 
A couple observations...

Microsoft Access treats the word "Date" as a reserved word (along with many
others). If you use "Date" as the name of a field in a table, when you tell
Access "Date", which one do you want it to use?! (change the name of the
field).

Some of your field names contain spaces ... this can cause additional
confusion (you and/or Access). Consider using the underscore ("_") instead
of a space, or using CamelCase.

One way (perhaps a kludge) to get the difference would be to create a second
query, based on the first one you described, that takes the difference.
Another possibility is to take the difference in your first query (e.g.
[Call Recvd] - [Call CFC]), then sum it.

By the way, I have no idea from your description what those two fields hold.
Are these Yes/No fields, Date/Time fields, text fields, or what? Why would
it make sense (remember, we aren't there, we can't see what you can) to take
the difference?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for the response jeff.

First of all I cleaned up all the names to not having a space.

The problem I am having is getting the difference from the two sums to show
up. Both the fields are numbers.

So - CallRecvd will be 123
CallCFC will be 100
I want to see the difference between those on one query (23)

I tried setting up a different query which is looking at the sum of both the
fields but I am not sure how to "do [Call Recvd] - [Call CFC]), then sum it"
on the query.

Do i set up a new field or should i go into the sql view and do it there? if
so, how or what should i type.

I am not sure how to get the difference on a query from two fields ?

Does that make sense.. probably not.. i ramble a lot.

Thanks again

Jeff said:
A couple observations...

Microsoft Access treats the word "Date" as a reserved word (along with many
others). If you use "Date" as the name of a field in a table, when you tell
Access "Date", which one do you want it to use?! (change the name of the
field).

Some of your field names contain spaces ... this can cause additional
confusion (you and/or Access). Consider using the underscore ("_") instead
of a space, or using CamelCase.

One way (perhaps a kludge) to get the difference would be to create a second
query, based on the first one you described, that takes the difference.
Another possibility is to take the difference in your first query (e.g.
[Call Recvd] - [Call CFC]), then sum it.

By the way, I have no idea from your description what those two fields hold.
Are these Yes/No fields, Date/Time fields, text fields, or what? Why would
it make sense (remember, we aren't there, we can't see what you can) to take
the difference?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Good morning,
Click to expand...
[quoted text clipped - 31 lines]
Thank you everyone again.
Click to expand...
Click to expand...
 
It sounds like you already have a query in which you are doing "Totals".

If you create a new field in that query, something like:

YourNewField: [CallRecvd]-[CallCF]

and then select Sum for the aggregation of this new value, I suspect you'll
get the same thing as if you had summed each field separately before taking
the difference.

Regards

Jeff Boyce
Microsoft Office/Access MVP


instereo911 via AccessMonster.com said:
Thanks for the response jeff.

First of all I cleaned up all the names to not having a space.

The problem I am having is getting the difference from the two sums to
show
up. Both the fields are numbers.

So - CallRecvd will be 123
CallCFC will be 100
I want to see the difference between those on one query (23)

I tried setting up a different query which is looking at the sum of both
the
fields but I am not sure how to "do [Call Recvd] - [Call CFC]), then sum
it"
on the query.

Do i set up a new field or should i go into the sql view and do it there?
if
so, how or what should i type.

I am not sure how to get the difference on a query from two fields ?

Does that make sense.. probably not.. i ramble a lot.

Thanks again

Jeff said:
A couple observations...

Microsoft Access treats the word "Date" as a reserved word (along with
many
others). If you use "Date" as the name of a field in a table, when you
tell
Access "Date", which one do you want it to use?! (change the name of the
field).

Some of your field names contain spaces ... this can cause additional
confusion (you and/or Access). Consider using the underscore ("_")
instead
of a space, or using CamelCase.

One way (perhaps a kludge) to get the difference would be to create a
second
query, based on the first one you described, that takes the difference.
Another possibility is to take the difference in your first query (e.g.
[Call Recvd] - [Call CFC]), then sum it.

By the way, I have no idea from your description what those two fields
hold.
Are these Yes/No fields, Date/Time fields, text fields, or what? Why
would
it make sense (remember, we aren't there, we can't see what you can) to
take
the difference?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Good morning,
Click to expand...
[quoted text clipped - 31 lines]
Thank you everyone again.
Click to expand...
Click to expand...
Click to expand...
 
I never new you could do that!! Jeff - thank you very much....

Wow thank you again!!

Jeff said:
It sounds like you already have a query in which you are doing "Totals".

If you create a new field in that query, something like:

YourNewField: [CallRecvd]-[CallCF]

and then select Sum for the aggregation of this new value, I suspect you'll
get the same thing as if you had summed each field separately before taking
the difference.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Thanks for the response jeff.
Click to expand...
[quoted text clipped - 62 lines]
Click to expand...
 
Back
Top