Totals for two fields

J

Jk

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
 
D

Duane Hookom

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.
 
J

Jk

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.
 
D

Duane Hookom

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...
 
J

Jk

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...
 
D

Duane Hookom

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...
 
J

jk

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...
 
Top