Totals for two fields

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

Guest

Hello,

In my database i have two fields, one called CorrectionsRB and another
Corrections WO.These two fields contain a three letter id to identify on my
forms entry screen as either a rebill or write off.A correction amount is
entered on the forms and one of these fields is selected.How do i get the
report to sum a total for each one of these two fields so on the report it
shows the two fields on the top and a sum for each on the bottom?
Thanks
 
Could you provide some sample records and what you would expect for totals
on the report? It would be nice to have some field names also.
 
Hello Duane,

The field names are CorrectionsRb and CorrectionsWO.The data in query would
show up like this:

Invoice Number CorrectionsRb CorrectionsWO CorrectionAmt
900123220 RSD $95.25
900223224 SVC $125.00

As a user enters an invoice number, they will type a code into either one of
these fields on the data entry form.I am looking for a total for the
CorrectionsRb column and another total for the CorrectionWO column.Is there a
way to sum the amount according to the field name they were entered on such
as =sum([CorrectionRb]) and =sum([CorrectionsWO]).I dont know if this correct
....please advise on the correct way...thanks.
 
I think your table structure is off. I would have fields like:
InvoiceNum
Corrections (values of Rb or WO)
Code (values like RSD, SVC,...)
CorrectionAmt
If the codes are mutually exclusive then you might not need a corrections
field since the code would identify this value. With a structure like this,
your sum expression would be:
=Sum( Abs(Corrections="Rb") * CorrectionAmt)

As your table exists, you can sum Rbs with:
=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)

--
Duane Hookom
MS Access MVP


Jk said:
Hello Duane,

The field names are CorrectionsRb and CorrectionsWO.The data in query would
show up like this:

Invoice Number CorrectionsRb CorrectionsWO CorrectionAmt
900123220 RSD $95.25
900223224 SVC $125.00

As a user enters an invoice number, they will type a code into either one of
these fields on the data entry form.I am looking for a total for the
CorrectionsRb column and another total for the CorrectionWO column.Is there a
way to sum the amount according to the field name they were entered on such
as =sum([CorrectionRb]) and =sum([CorrectionsWO]).I dont know if this correct
...please advise on the correct way...thanks.
Jk said:
Hello,

In my database i have two fields, one called CorrectionsRB and another
Corrections WO.These two fields contain a three letter id to identify on my
forms entry screen as either a rebill or write off.A correction amount is
entered on the forms and one of these fields is selected.How do i get the
report to sum a total for each one of these two fields so on the report it
shows the two fields on the top and a sum for each on the bottom?
Thanks
Click to expand...
Click to expand...
 
Hello Duane,

=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)
This formula is not working since when i key it into a text field use it
under the control source...it responds with invalid expression.Any
suggestions of what i may be doing wrong?


Duane Hookom said:
I think your table structure is off. I would have fields like:
InvoiceNum
Corrections (values of Rb or WO)
Code (values like RSD, SVC,...)
CorrectionAmt
If the codes are mutually exclusive then you might not need a corrections
field since the code would identify this value. With a structure like this,
your sum expression would be:
=Sum( Abs(Corrections="Rb") * CorrectionAmt)

As your table exists, you can sum Rbs with:
=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)

--
Duane Hookom
MS Access MVP


Jk said:
Hello Duane,

The field names are CorrectionsRb and CorrectionsWO.The data in query would
show up like this:

Invoice Number CorrectionsRb CorrectionsWO CorrectionAmt
900123220 RSD $95.25
900223224 SVC $125.00

As a user enters an invoice number, they will type a code into either one of
these fields on the data entry form.I am looking for a total for the
CorrectionsRb column and another total for the CorrectionWO column.Is there a
way to sum the amount according to the field name they were entered on such
as =sum([CorrectionRb]) and =sum([CorrectionsWO]).I dont know if this correct
...please advise on the correct way...thanks.
Jk said:
Hello,

In my database i have two fields, one called CorrectionsRB and another
Corrections WO.These two fields contain a three letter id to identify on my
forms entry screen as either a rebill or write off.A correction amount is
entered on the forms and one of these fields is selected.How do i get the
report to sum a total for each one of these two fields so on the report it
shows the two fields on the top and a sum for each on the bottom?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Assuming the field names are correct, you shouldn't get an error message.
Are you getting dialog box with the error message? Is the expression in a
group or report header or footer?

--
Duane Hookom
MS Access MVP
--

Jk said:
Hello Duane,

=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)
This formula is not working since when i key it into a text field use it
under the control source...it responds with invalid expression.Any
suggestions of what i may be doing wrong?


Duane Hookom said:
I think your table structure is off. I would have fields like:
InvoiceNum
Corrections (values of Rb or WO)
Code (values like RSD, SVC,...)
CorrectionAmt
If the codes are mutually exclusive then you might not need a corrections
field since the code would identify this value. With a structure like this,
your sum expression would be:
=Sum( Abs(Corrections="Rb") * CorrectionAmt)

As your table exists, you can sum Rbs with:
=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)

--
Duane Hookom
MS Access MVP


Jk said:
Hello Duane,

The field names are CorrectionsRb and CorrectionsWO.The data in query would
show up like this:

Invoice Number CorrectionsRb CorrectionsWO CorrectionAmt
900123220 RSD $95.25
900223224 SVC $125.00

As a user enters an invoice number, they will type a code into either
Click to expand...
one
of
these fields on the data entry form.I am looking for a total for the
CorrectionsRb column and another total for the CorrectionWO column.Is there a
way to sum the amount according to the field name they were entered on such
as =sum([CorrectionRb]) and =sum([CorrectionsWO]).I dont know if this correct
...please advise on the correct way...thanks.
:

Hello,

In my database i have two fields, one called CorrectionsRB and another
Corrections WO.These two fields contain a three letter id to
Click to expand...
identify on
my
forms entry screen as either a rebill or write off.A correction
Click to expand...
amount
is
entered on the forms and one of these fields is selected.How do i
Click to expand...
get
the
report to sum a total for each one of these two fields so on the report it
shows the two fields on the top and a sum for each on the bottom?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
You were right Duane,

I went over it a couple of times and it must have been a typo on my
part......Thanks for the support....have a great one!

Duane Hookom said:
Assuming the field names are correct, you shouldn't get an error message.
Are you getting dialog box with the error message? Is the expression in a
group or report header or footer?

--
Duane Hookom
MS Access MVP
--

Jk said:
Hello Duane,

=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)
This formula is not working since when i key it into a text field use it
under the control source...it responds with invalid expression.Any
suggestions of what i may be doing wrong?


Duane Hookom said:
I think your table structure is off. I would have fields like:
InvoiceNum
Corrections (values of Rb or WO)
Code (values like RSD, SVC,...)
CorrectionAmt
If the codes are mutually exclusive then you might not need a corrections
field since the code would identify this value. With a structure like this,
your sum expression would be:
=Sum( Abs(Corrections="Rb") * CorrectionAmt)

As your table exists, you can sum Rbs with:
=Sum( (IsNull(CorrectionsRb)+1) * CorrectionAmt)

--
Duane Hookom
MS Access MVP


Hello Duane,

The field names are CorrectionsRb and CorrectionsWO.The data in query
would
show up like this:

Invoice Number CorrectionsRb CorrectionsWO CorrectionAmt
900123220 RSD $95.25
900223224 SVC $125.00

As a user enters an invoice number, they will type a code into either one
of
these fields on the data entry form.I am looking for a total for the
CorrectionsRb column and another total for the CorrectionWO column.Is
there a
way to sum the amount according to the field name they were entered on
such
as =sum([CorrectionRb]) and =sum([CorrectionsWO]).I dont know if this
correct
...please advise on the correct way...thanks.
:

Hello,

In my database i have two fields, one called CorrectionsRB and another
Corrections WO.These two fields contain a three letter id to identify on
my
forms entry screen as either a rebill or write off.A correction amount
is
entered on the forms and one of these fields is selected.How do i get
the
report to sum a total for each one of these two fields so on the
report it
shows the two fields on the top and a sum for each on the bottom?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top