A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sum using totals



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2010, 07:42 PM posted to microsoft.public.access.queries
Biss
external usenet poster
 
Posts: 24
Default Sum using totals

I have a query that I use to sum groups of data... Works great..

However I want to perform a calculaton on the SumOfPayout... All I want to
do is subtract 2 from the tolal..

IndexID IDStudio StudioID EpID ShortName Description Start End PaySumOfPayout12 8 LATA-010 Lata10 SF Period Payout 20-Mar-10 26-Mar-10 20-Apr-10 122.16
12 10 LATA-040 Lata40 SF Period Payout 20-Mar-10 26-Mar-10 20-Apr-10 60.24
12 43 LATA-OC-04 ds04031990 SF Period Payout 20-Mar-10 26-Mar-10 20-Apr-1087.4I am achieving the same restults by using this query in anototer query just
to subtract 2..

Lot of work for little result

There must be a way to perform this result in the totals query

thanks bob



  #2  
Old May 15th, 2010, 08:03 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Sum using totals

Biss wrote:
I have a query that I use to sum groups of data... Works great..

However I want to perform a calculaton on the SumOfPayout... All I
want to do is subtract 2 from the tolal..

IndexID IDStudio StudioID EpID ShortName Description Start End
PaySumOfPayout

12 8 LATA-010 Lata10 SF Period Payout 20-Mar-10
26-Mar-10 20-Apr-10 122.16

12 10 LATA-040 Lata40 SF Period Payout
20-Mar-10 26-Mar-10 20-Apr-10 60.24

12 43 LATA-OC-04 ds04031990 SF
Period Payout 20-Mar-10 26-Mar-10 20-Apr-1087.4


I am achieving the
same restults by using this query in anototer query just to subtract
2..
Lot of work for little result


I don't understand. Do you want to subtract two from the total in each
result record? If so, just put it into the column expression. You might not
be able to do it directly in the Design View, but if you switch to SQL View
you can simply change this:
Sum(fieldname) As PaySumOfPayout
to:
Sum(fieldname)-2 As PaySumOfPayout

IMO it is easier to do it in SQL View, but if you want to figure out how to
do it in Design View, make the change in SQL View and switch to Design View.

On the other hand, if you want to subtract 2 from the grand total (122.16 +
60.24 +1087.4 - 2) then there is no alternative but to use another query.




--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


  #3  
Old May 15th, 2010, 09:42 PM posted to microsoft.public.access.queries
Biss
external usenet poster
 
Posts: 24
Default Sum using totals

Worked like a charm... It simply changes the column to an expression and
not sum..

you can simply change this:
Sum(fieldname) As PaySumOfPayout
to:
Sum(fieldname)-2 As PaySumOfPayout

IMO it is easier to do it in SQL View, but if you want to figure out how
to do it in Design View, make the change in SQL View and switch to Design
View.



Thanks Bunches..

Bob




"Bob Barrows" wrote in message
...
Biss wrote:
I have a query that I use to sum groups of data... Works great..

However I want to perform a calculaton on the SumOfPayout... All I
want to do is subtract 2 from the tolal..

IndexID IDStudio StudioID EpID ShortName Description Start End
PaySumOfPayout

12 8 LATA-010 Lata10 SF Period Payout 20-Mar-10
26-Mar-10 20-Apr-10 122.16

12 10 LATA-040 Lata40 SF Period Payout
20-Mar-10 26-Mar-10 20-Apr-10 60.24

12 43 LATA-OC-04 ds04031990 SF
Period Payout 20-Mar-10 26-Mar-10 20-Apr-1087.4


I am achieving the
same restults by using this query in anototer query just to subtract
2..
Lot of work for little result


I don't understand. Do you want to subtract two from the total in each
result record? If so, just put it into the column expression. You might
not be able to do it directly in the Design View, but if you switch to SQL
View you can simply change this:
Sum(fieldname) As PaySumOfPayout
to:
Sum(fieldname)-2 As PaySumOfPayout

IMO it is easier to do it in SQL View, but if you want to figure out how
to do it in Design View, make the change in SQL View and switch to Design
View.

On the other hand, if you want to subtract 2 from the grand total (122.16
+ 60.24 +1087.4 - 2) then there is no alternative but to use another
query.




--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.