Hi everyone --
I'm having trouble producing some calculations in a report and hope you can help me. This is in Access 97, and the report is based on a query that contains two related tables - a customer name table and a service log table. The service log table has a column for dollar total per visit and a yes/no column to check off if the visit was in response to an ad.
The report is sorted/grouped on customer name, with each visit detailed under the customer. At the end I have a grand total of all dollar amounts.
What I would like, in addition to the grand total, are two additional subtotals - one for total dollar amount based on "response to ad" (yes/no field = true), and one for total "other" dollar amount (yes/no field = false).
And this is where I'm running into trouble. Of course, I had success doing it by creating additional queries and subreports, but I know there must be a simpler way. I tried experimenting with various IF statements in the report and was totally unsuccessful with that.
My current approach is adding two additional columns in the query - one for the yes/no = true figures and one for the yes/no = false figures. The query works fine, but when I try to run the report, I get an error message that I have an invalid data type. The two new "query-only" fields are called ad-y and ad-n. In the textbox (on the report footer) where I want the totals, I have =Sum([ad-y]) and =Sum([ad-n]) as the data control source. I have made sure that the properties in the query as well as the report subtotal field are set to currency (same as the original data).
If this is the correct way to do this, what am I doing wrong? If it's not the best approach, I welcome any help you can offer for doing it the right way!
Thanks in advance!
Carenne
I'm having trouble producing some calculations in a report and hope you can help me. This is in Access 97, and the report is based on a query that contains two related tables - a customer name table and a service log table. The service log table has a column for dollar total per visit and a yes/no column to check off if the visit was in response to an ad.
The report is sorted/grouped on customer name, with each visit detailed under the customer. At the end I have a grand total of all dollar amounts.
What I would like, in addition to the grand total, are two additional subtotals - one for total dollar amount based on "response to ad" (yes/no field = true), and one for total "other" dollar amount (yes/no field = false).
And this is where I'm running into trouble. Of course, I had success doing it by creating additional queries and subreports, but I know there must be a simpler way. I tried experimenting with various IF statements in the report and was totally unsuccessful with that.
My current approach is adding two additional columns in the query - one for the yes/no = true figures and one for the yes/no = false figures. The query works fine, but when I try to run the report, I get an error message that I have an invalid data type. The two new "query-only" fields are called ad-y and ad-n. In the textbox (on the report footer) where I want the totals, I have =Sum([ad-y]) and =Sum([ad-n]) as the data control source. I have made sure that the properties in the query as well as the report subtotal field are set to currency (same as the original data).
If this is the correct way to do this, what am I doing wrong? If it's not the best approach, I welcome any help you can offer for doing it the right way!
Thanks in advance!
Carenne
