Selecting records and sum of specific text identifiers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello,

Is there a way in query to provide two separate sum totals for something
like a three letter text.....For example, i have an invoices which has codes
for types of credits given per transaction
RSD transaction 345567888 $33.00
PBS transaction 456687900 $44.50
WIN transaction 345576777 $37.99
--These three letter text codes identify writeoffs (money never to be
recovered)



RBG transaction 675666778 $54.65
FTW transaction 987667899 $43.25
.....These three letter text codes identify rebills (money that can be
recovered)

Each one of these codes would represent a transaction in a invoice in
dollars so that is why i would want a separate sum total for credits given
for writeoffs or rebills to determine what amounts in dollars has been
totally written off and what hasnt.

Thanks
 
First, I would create a table of codes with a field that identifies if the
code is a write-off or whatever. Then include this table in your query and
join the code fields so that every transaction can be identified by type.
Then in your report group footer or report footer, you can use an expression
like:
=Sum(Abs([Type]="Writeoff") * TransAmt)
 
Thanks,

I already have a code table that identifys each
transaction but not the difference between one and the
other.Do you think by adding a symbol like an asterisk or
another letter(making it four letters) would make it
easier to separate for sums?Your formula example is not
clear for me, would i not need two sums in the page
footer, one for writeoff and another for rebills?
Thanks
-----Original Message-----
First, I would create a table of codes with a field that identifies if the
code is a write-off or whatever. Then include this table in your query and
join the code fields so that every transaction can be identified by type.
Then in your report group footer or report footer, you can use an expression
like:
=Sum(Abs([Type]="Writeoff") * TransAmt)

--
Duane Hookom
MS Access MVP


joe said:
hello,

Is there a way in query to provide two separate sum totals for something
like a three letter text.....For example, i have an
Click to expand...
invoices which has
codes
for types of credits given per transaction
RSD transaction 345567888 $33.00
PBS transaction 456687900 $44.50
WIN transaction 345576777 $37.99
--These three letter text codes identify writeoffs (money never to be
recovered)



RBG transaction 675666778 $54.65
FTW transaction 987667899 $43.25
....These three letter text codes identify rebills (money that can be
recovered)

Each one of these codes would represent a transaction in a invoice in
dollars so that is why i would want a separate sum total for credits given
for writeoffs or rebills to determine what amounts in dollars has been
totally written off and what hasnt.

Thanks
Click to expand...


.
Click to expand...
 
My example was just for writeoffs. I thought you could figure out how to
modify it for other types.

--
Duane Hookom
MS Access MVP


Joe said:
Thanks,

I already have a code table that identifys each
transaction but not the difference between one and the
other.Do you think by adding a symbol like an asterisk or
another letter(making it four letters) would make it
easier to separate for sums?Your formula example is not
clear for me, would i not need two sums in the page
footer, one for writeoff and another for rebills?
Thanks
-----Original Message-----
First, I would create a table of codes with a field that identifies if the
code is a write-off or whatever. Then include this table in your query and
join the code fields so that every transaction can be identified by type.
Then in your report group footer or report footer, you can use an expression
like:
=Sum(Abs([Type]="Writeoff") * TransAmt)

--
Duane Hookom
MS Access MVP


joe said:
hello,

Is there a way in query to provide two separate sum totals for something
like a three letter text.....For example, i have an
Click to expand...
invoices which has
codes
for types of credits given per transaction
RSD transaction 345567888 $33.00
PBS transaction 456687900 $44.50
WIN transaction 345576777 $37.99
--These three letter text codes identify writeoffs (money never to be
recovered)



RBG transaction 675666778 $54.65
FTW transaction 987667899 $43.25
....These three letter text codes identify rebills (money that can be
recovered)

Each one of these codes would represent a transaction in a invoice in
dollars so that is why i would want a separate sum total for credits given
for writeoffs or rebills to determine what amounts in dollars has been
totally written off and what hasnt.

Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top