Report to calculate total spent by customers in a customer db

  • Thread starter Thread starter Caleb
  • Start date Start date
C

Caleb

I am having troubles designing a report to sort a customer database first by
email (because this is the only unique field) then by a total spent by the
customer (calculated by adding up all the total fields for that user and
subtracting to not adding in records that have been refunded, which is marked
by a seperate field). So far Ive gotten the report to sort by customer emails
and display the totals for each customer but I am having troubles getting it
to subtract or ignore records that have been refunded.
 
How did you do what you did so far? We can not tell you how to modify what
you have done to accomplish the next thing without knowing the thing to be
modified.

If it is a query then you can use criteria related to the 'refunded' field.
I can not see what to use for criteria as I do not know what kind of field it
is or what is in it.

Do you see my problem?
 
I knew Id have to explain further, what Ive done so far is create a report
that groups each email together and displays the sum of the "Total" fields
for each email. The "RefundStatus" field only has an entry (Entrys include
'Refund - Credit' or 'Manual Refund') if the customer wanted a refund,
otherwise the field is empty. What I need the report to do is if the
"RefundStatus" field has an entry, then it subtracts that records "Total"
from the sum thats displayed for the email... Does that help?
 
Im not sure if im doing this correctly but i made a new field "Refund" to
hold the value if "RefundStatus" Is Not Null, but im not getting anything
returned. Here is the code:
=IIf([RefundStatus] Is Not Null,[refund]=[refund]+[total])
Then to display it:
=[refund]
 
Add a control to the group footer and set its control source to
=Sum(IIF([RefundStatus] is not null,[Refund],0))

Or if you just want the total spent
=Sum(IIF(RefundStatus is Null,[Spent],0))

Replace Spent with the field name that records the customers' expenditures.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Im not sure if im doing this correctly but i made a new field "Refund" to
hold the value if "RefundStatus" Is Not Null, but im not getting anything
returned. Here is the code:
=IIf([RefundStatus] Is Not Null,[refund]=[refund]+[total])
Then to display it:
=[refund]


KARL DEWEY said:
How did you do what you did so far? We can not tell you how to modify what
you have done to accomplish the next thing without knowing the thing to be
modified.

If it is a query then you can use criteria related to the 'refunded' field.
I can not see what to use for criteria as I do not know what kind of field it
is or what is in it.

Do you see my problem?
Click to expand...
Click to expand...
 
That works! Thank you so much!

John Spencer MVP said:
Add a control to the group footer and set its control source to
=Sum(IIF([RefundStatus] is not null,[Refund],0))

Or if you just want the total spent
=Sum(IIF(RefundStatus is Null,[Spent],0))

Replace Spent with the field name that records the customers' expenditures.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Im not sure if im doing this correctly but i made a new field "Refund" to
hold the value if "RefundStatus" Is Not Null, but im not getting anything
returned. Here is the code:
=IIf([RefundStatus] Is Not Null,[refund]=[refund]+[total])
Then to display it:
=[refund]


KARL DEWEY said:
How did you do what you did so far? We can not tell you how to modify what
you have done to accomplish the next thing without knowing the thing to be
modified.

If it is a query then you can use criteria related to the 'refunded' field.
I can not see what to use for criteria as I do not know what kind of field it
is or what is in it.

Do you see my problem?

:

I am having troubles designing a report to sort a customer database first by
email (because this is the only unique field) then by a total spent by the
customer (calculated by adding up all the total fields for that user and
subtracting to not adding in records that have been refunded, which is marked
by a seperate field). So far Ive gotten the report to sort by customer emails
and display the totals for each customer but I am having troubles getting it
to subtract or ignore records that have been refunded.
Click to expand...
Click to expand...
Click to expand...
 
Now do you know how I can sort my results by the [Spent] field while still
keeping them grouped by email?

John Spencer MVP said:
Add a control to the group footer and set its control source to
=Sum(IIF([RefundStatus] is not null,[Refund],0))

Or if you just want the total spent
=Sum(IIF(RefundStatus is Null,[Spent],0))

Replace Spent with the field name that records the customers' expenditures.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Im not sure if im doing this correctly but i made a new field "Refund" to
hold the value if "RefundStatus" Is Not Null, but im not getting anything
returned. Here is the code:
=IIf([RefundStatus] Is Not Null,[refund]=[refund]+[total])
Then to display it:
=[refund]


KARL DEWEY said:
How did you do what you did so far? We can not tell you how to modify what
you have done to accomplish the next thing without knowing the thing to be
modified.

If it is a query then you can use criteria related to the 'refunded' field.
I can not see what to use for criteria as I do not know what kind of field it
is or what is in it.

Do you see my problem?

:

I am having troubles designing a report to sort a customer database first by
email (because this is the only unique field) then by a total spent by the
customer (calculated by adding up all the total fields for that user and
subtracting to not adding in records that have been refunded, which is marked
by a seperate field). So far Ive gotten the report to sort by customer emails
and display the totals for each customer but I am having troubles getting it
to subtract or ignore records that have been refunded.
Click to expand...
Click to expand...
Click to expand...
 
If you need to order by the total SPENT field for each individual you will
have to calculate that in the query so it will be available for sorting.
Without knowing your query I can't give much more advice than that.

You might want to look at using a subreport for the details and a main report
that just show the user information and the TOTAL spent.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Now do you know how I can sort my results by the [Spent] field while still
keeping them grouped by email?

John Spencer MVP said:
Add a control to the group footer and set its control source to
=Sum(IIF([RefundStatus] is not null,[Refund],0))

Or if you just want the total spent
=Sum(IIF(RefundStatus is Null,[Spent],0))

Replace Spent with the field name that records the customers' expenditures.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Im not sure if im doing this correctly but i made a new field "Refund" to
hold the value if "RefundStatus" Is Not Null, but im not getting anything
returned. Here is the code:
=IIf([RefundStatus] Is Not Null,[refund]=[refund]+[total])
Then to display it:
=[refund]


:

How did you do what you did so far? We can not tell you how to modify what
you have done to accomplish the next thing without knowing the thing to be
modified.

If it is a query then you can use criteria related to the 'refunded' field.
I can not see what to use for criteria as I do not know what kind of field it
is or what is in it.

Do you see my problem?

:

I am having troubles designing a report to sort a customer database first by
email (because this is the only unique field) then by a total spent by the
customer (calculated by adding up all the total fields for that user and
subtracting to not adding in records that have been refunded, which is marked
by a seperate field). So far Ive gotten the report to sort by customer emails
and display the totals for each customer but I am having troubles getting it
to subtract or ignore records that have been refunded.
Click to expand...
Click to expand...
Click to expand...
 
Okay so how can I apply =Sum(IIf([RefundStatus] Is Null,[Total],0)) into a
query so it saves to a new field i created

John Spencer MVP said:
If you need to order by the total SPENT field for each individual you will
have to calculate that in the query so it will be available for sorting.
Without knowing your query I can't give much more advice than that.

You might want to look at using a subreport for the details and a main report
that just show the user information and the TOTAL spent.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Now do you know how I can sort my results by the [Spent] field while still
keeping them grouped by email?

John Spencer MVP said:
Add a control to the group footer and set its control source to
=Sum(IIF([RefundStatus] is not null,[Refund],0))

Or if you just want the total spent
=Sum(IIF(RefundStatus is Null,[Spent],0))

Replace Spent with the field name that records the customers' expenditures.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Caleb wrote:
Im not sure if im doing this correctly but i made a new field "Refund" to
hold the value if "RefundStatus" Is Not Null, but im not getting anything
returned. Here is the code:
=IIf([RefundStatus] Is Not Null,[refund]=[refund]+[total])
Then to display it:
=[refund]


:

How did you do what you did so far? We can not tell you how to modify what
you have done to accomplish the next thing without knowing the thing to be
modified.

If it is a query then you can use criteria related to the 'refunded' field.
I can not see what to use for criteria as I do not know what kind of field it
is or what is in it.

Do you see my problem?

:

I am having troubles designing a report to sort a customer database first by
email (because this is the only unique field) then by a total spent by the
customer (calculated by adding up all the total fields for that user and
subtracting to not adding in records that have been refunded, which is marked
by a seperate field). So far Ive gotten the report to sort by customer emails
and display the totals for each customer but I am having troubles getting it
to subtract or ignore records that have been refunded.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top