Prev: Filtering for fields with character
Next: Flag a value in a group...
From: Biss on 15 May 2010 14:42
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



From: Bob Barrows on 15 May 2010 15:03
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"


From: Biss on 15 May 2010 16:42
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" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:#K3FYFG9KHA.4924(a)TK2MSFTNGP04.phx.gbl...
> 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"
>
 | 
Pages: 1
Prev: Filtering for fields with character
Next: Flag a value in a group...