Calculating two values that are in different records

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a query called SalesBudget3 that has fields which include:
CustomerName, Type and then all of the months of the year (January,
February). The type field is either Actual or Budget. My query will return
two records per client. The first would have their actual sales for 2008 and
the second would have their budgeted sales. In my report, I have the field
names in the page header (Type, January etc) and CustomerName in the
CustomerName header. The Details sections has their monthly sales figures.
In the CustomerName footer, I want to calculate percent of Actual to Budget
per month but it looks like the only thing I can do is to sum them. Is there
a way to do a percentage in the form?

Also, I created a separate query called JanPercentage which has the Actual
and Budget figures for January in the same record. I have divided the two
and have a calculated field called JanPer which is actual to budget for each
client. This is the result that I would like to appear in CustomerName
footer. Is there a way through say a subreport that I can insert the results
needed for each client? Can someone help?

Thanks,
 
Assuming you have records in your report's record source like:

Customer Type January
========= ===== =====
Joe Customer Actual $100
Joe Customer Budget $120

You can add an expression in the Customer footer to calculate the Actual of:
=Sum(Abs([Type]="Actual") * [January])
The expression to calculate the Budget is
=Sum(Abs([Type]="Budget") * [January])
You can combine these expressions to "calculate percent of Actual to Budget".
 
Duane,

Thanks for your help. I actually don't need to sum anything. In my report,
the CustomerName is in the CustomerName header and the 2008 Actual and 2008
Budget appear in the details section. So my data looks like this:

January February March
John Smith
2008 Actual $500
2008 Budget $400 $400 $500

Jane Doe
2008 Actual $300
2008 Budget $250 $250 $300

What I want to do is on the line below the two records that have the Actual
and Budgets (CustomerName Footer?), I want to divide the actual into the
budget for each month. Is there a way to do this?

Thanks,
--
Chuck W


Duane Hookom said:
Assuming you have records in your report's record source like:

Customer Type January
========= ===== =====
Joe Customer Actual $100
Joe Customer Budget $120

You can add an expression in the Customer footer to calculate the Actual of:
=Sum(Abs([Type]="Actual") * [January])
The expression to calculate the Budget is
=Sum(Abs([Type]="Budget") * [January])
You can combine these expressions to "calculate percent of Actual to Budget".

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


ChuckW said:
Hi,

I have a query called SalesBudget3 that has fields which include:
CustomerName, Type and then all of the months of the year (January,
February). The type field is either Actual or Budget. My query will return
two records per client. The first would have their actual sales for 2008 and
the second would have their budgeted sales. In my report, I have the field
names in the page header (Type, January etc) and CustomerName in the
CustomerName header. The Details sections has their monthly sales figures.
In the CustomerName footer, I want to calculate percent of Actual to Budget
per month but it looks like the only thing I can do is to sum them. Is there
a way to do a percentage in the form?

Also, I created a separate query called JanPercentage which has the Actual
and Budget figures for January in the same record. I have divided the two
and have a calculated field called JanPer which is actual to budget for each
client. This is the result that I would like to appear in CustomerName
footer. Is there a way through say a subreport that I can insert the results
needed for each client? Can someone help?

Thanks,
Click to expand...
Click to expand...
 
I believe the answer is included in my previous reply. You confuse me with
"divide ... into...". My confused brain works better with statements like:
Divide Actual by Budget
or
Divide Budget by Actual
or even better
Budget/Actual ;-)

If you want Actual/Budget, try:
=Sum(Abs([Type]="Actual") * [January])/Sum(Abs([Type]="Budget") * [January])

If you want the other "divide into", try:
=Sum(Abs([Type]="Budget") * [January])/Sum(Abs([Type]="Actual") * [January])

The reason I use the Sum() is that you could use the same expression in a
footer that groups multiple customers. It would not need to be re-written.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


ChuckW said:
Duane,

Thanks for your help. I actually don't need to sum anything. In my report,
the CustomerName is in the CustomerName header and the 2008 Actual and 2008
Budget appear in the details section. So my data looks like this:

January February March
John Smith
2008 Actual $500
2008 Budget $400 $400 $500

Jane Doe
2008 Actual $300
2008 Budget $250 $250 $300

What I want to do is on the line below the two records that have the Actual
and Budgets (CustomerName Footer?), I want to divide the actual into the
budget for each month. Is there a way to do this?

Thanks,
--
Chuck W


Duane Hookom said:
Assuming you have records in your report's record source like:

Customer Type January
========= ===== =====
Joe Customer Actual $100
Joe Customer Budget $120

You can add an expression in the Customer footer to calculate the Actual of:
=Sum(Abs([Type]="Actual") * [January])
The expression to calculate the Budget is
=Sum(Abs([Type]="Budget") * [January])
You can combine these expressions to "calculate percent of Actual to Budget".

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


ChuckW said:
Hi,

I have a query called SalesBudget3 that has fields which include:
CustomerName, Type and then all of the months of the year (January,
February). The type field is either Actual or Budget. My query will return
two records per client. The first would have their actual sales for 2008 and
the second would have their budgeted sales. In my report, I have the field
names in the page header (Type, January etc) and CustomerName in the
CustomerName header. The Details sections has their monthly sales figures.
In the CustomerName footer, I want to calculate percent of Actual to Budget
per month but it looks like the only thing I can do is to sum them. Is there
a way to do a percentage in the form?

Also, I created a separate query called JanPercentage which has the Actual
and Budget figures for January in the same record. I have divided the two
and have a calculated field called JanPer which is actual to budget for each
client. This is the result that I would like to appear in CustomerName
footer. Is there a way through say a subreport that I can insert the results
needed for each client? Can someone help?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks. That worked. One last thing, I was to supress the #Num! when the
budget amount is zero. I look through some of the previous posts, many that
you had answered but could not find the right coding. Can you help?

--
Chuck W


Duane Hookom said:
I believe the answer is included in my previous reply. You confuse me with
"divide ... into...". My confused brain works better with statements like:
Divide Actual by Budget
or
Divide Budget by Actual
or even better
Budget/Actual ;-)

If you want Actual/Budget, try:
=Sum(Abs([Type]="Actual") * [January])/Sum(Abs([Type]="Budget") * [January])

If you want the other "divide into", try:
=Sum(Abs([Type]="Budget") * [January])/Sum(Abs([Type]="Actual") * [January])

The reason I use the Sum() is that you could use the same expression in a
footer that groups multiple customers. It would not need to be re-written.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


ChuckW said:
Duane,

Thanks for your help. I actually don't need to sum anything. In my report,
the CustomerName is in the CustomerName header and the 2008 Actual and 2008
Budget appear in the details section. So my data looks like this:

January February March
John Smith
2008 Actual $500
2008 Budget $400 $400 $500

Jane Doe
2008 Actual $300
2008 Budget $250 $250 $300

What I want to do is on the line below the two records that have the Actual
and Budgets (CustomerName Footer?), I want to divide the actual into the
budget for each month. Is there a way to do this?

Thanks,
--
Chuck W


Duane Hookom said:
Assuming you have records in your report's record source like:

Customer Type January
========= ===== =====
Joe Customer Actual $100
Joe Customer Budget $120

You can add an expression in the Customer footer to calculate the Actual of:
=Sum(Abs([Type]="Actual") * [January])
The expression to calculate the Budget is
=Sum(Abs([Type]="Budget") * [January])
You can combine these expressions to "calculate percent of Actual to Budget".

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi,

I have a query called SalesBudget3 that has fields which include:
CustomerName, Type and then all of the months of the year (January,
February). The type field is either Actual or Budget. My query will return
two records per client. The first would have their actual sales for 2008 and
the second would have their budgeted sales. In my report, I have the field
names in the page header (Type, January etc) and CustomerName in the
CustomerName header. The Details sections has their monthly sales figures.
In the CustomerName footer, I want to calculate percent of Actual to Budget
per month but it looks like the only thing I can do is to sum them. Is there
a way to do a percentage in the form?

Also, I created a separate query called JanPercentage which has the Actual
and Budget figures for January in the same record. I have divided the two
and have a calculated field called JanPer which is actual to budget for each
client. This is the result that I would like to appear in CustomerName
footer. Is there a way through say a subreport that I can insert the results
needed for each client? Can someone help?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
To avoid the divide by zero, change the expression to something like:
=IIf(Nz([Denominator],0) = 0 , 0, [Numerator]/[Denominator])

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


ChuckW said:
Duane,

Thanks. That worked. One last thing, I was to supress the #Num! when the
budget amount is zero. I look through some of the previous posts, many that
you had answered but could not find the right coding. Can you help?

--
Chuck W


Duane Hookom said:
I believe the answer is included in my previous reply. You confuse me with
"divide ... into...". My confused brain works better with statements like:
Divide Actual by Budget
or
Divide Budget by Actual
or even better
Budget/Actual ;-)

If you want Actual/Budget, try:
=Sum(Abs([Type]="Actual") * [January])/Sum(Abs([Type]="Budget") * [January])

If you want the other "divide into", try:
=Sum(Abs([Type]="Budget") * [January])/Sum(Abs([Type]="Actual") * [January])

The reason I use the Sum() is that you could use the same expression in a
footer that groups multiple customers. It would not need to be re-written.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


ChuckW said:
Duane,

Thanks for your help. I actually don't need to sum anything. In my report,
the CustomerName is in the CustomerName header and the 2008 Actual and 2008
Budget appear in the details section. So my data looks like this:

January February March
John Smith
2008 Actual $500
2008 Budget $400 $400 $500

Jane Doe
2008 Actual $300
2008 Budget $250 $250 $300

What I want to do is on the line below the two records that have the Actual
and Budgets (CustomerName Footer?), I want to divide the actual into the
budget for each month. Is there a way to do this?

Thanks,
--
Chuck W


:

Assuming you have records in your report's record source like:

Customer Type January
========= ===== =====
Joe Customer Actual $100
Joe Customer Budget $120

You can add an expression in the Customer footer to calculate the Actual of:
=Sum(Abs([Type]="Actual") * [January])
The expression to calculate the Budget is
=Sum(Abs([Type]="Budget") * [January])
You can combine these expressions to "calculate percent of Actual to Budget".

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi,

I have a query called SalesBudget3 that has fields which include:
CustomerName, Type and then all of the months of the year (January,
February). The type field is either Actual or Budget. My query will return
two records per client. The first would have their actual sales for 2008 and
the second would have their budgeted sales. In my report, I have the field
names in the page header (Type, January etc) and CustomerName in the
CustomerName header. The Details sections has their monthly sales figures.
In the CustomerName footer, I want to calculate percent of Actual to Budget
per month but it looks like the only thing I can do is to sum them. Is there
a way to do a percentage in the form?

Also, I created a separate query called JanPercentage which has the Actual
and Budget figures for January in the same record. I have divided the two
and have a calculated field called JanPer which is actual to budget for each
client. This is the result that I would like to appear in CustomerName
footer. Is there a way through say a subreport that I can insert the results
needed for each client? Can someone help?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top