Total based on calculation in crosstab query report

I have a fairly complex crosstab query that is used in a report. I have dynamic column headings based on input from the user when the report is ran. Basically the report shows me the number of orders a customer had per month. I have the report setup so if the number of orders declined 1-4 from previous month it would turn that row yellow. If the cutomer's orders declined 5 or more, then it turns the row red. If the customer has not used us in over two months, it will turn the column orange. I need to be able to total up the number of yellow, red, and orange customers. I can't seem to get the counters to work for this. I had placed them in the same code that turns the record the appropriate color, but it doesn't work. It keeps reseting the counts when it gets done running through the code. I then discovered the wonderful feature regarding retreat event. How do I get around this problem and get my totals that I need? I want to place the totals in the footer of my report.

Microsoft Access 2003 interface with a SQL 2008 database.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You could try some adding some invisible text boxes for each row... one for each color.   Put your formula for what should be added up for that row in each box, then in the report footer, sum the values in your hidden text boxes.

The other option is to try to derive the colors in your underlying queries and then you could group by/sort on that as a field.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ree0082Author Commented:
That was what I was originally thinking. Using invisible boxes per row. I got each box to put a number in it depending on the color. I can't get the toatl to work in the footer. I am using =sum([txtOrange]) and get an error displayed. I am not sure why. txtOrange is one of the invisible columns.
ree0082Author Commented:
Ok I discovered that the problem is you can't use an aggregate function on a control. It needs to be done on a fiield in your bound table or query. Everything that I have found points to trying to put my calculation in my query so a column is created for it. the other method I saw was to use a running sum, but that still needed to reference one of my fields. Here are the calculations used to determine what color they should be.

ChangeInOrders = Column3 - Column2

If ChangeInOrders is less then 5 but greater then 0 then color needs to be yellow

If ChangeInOrders is greater then or equal to 5 then color needs to be red

If Column 3 and Column 2 are both 0 then color needs to be orange

Is it possilble to have these calculations in my query? If not , how can I get a sum of these records per color?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Yes, you should be able to put these in a query -- but if this coming from a cross tab query (in other words, if Column 2 and Column 3 are generated based on data) then you'll need to probably build another query on top of your Crosstab query and use that to pull in the cross tab data and your new calculated fields.

But -- you'll need to be confident that your crosstab query will always return the same rows in the same order otherwise your second query will fail/produce unexpected results.  For that you would use the "Column Headings" property of the crosstab query... this is not required but it could protect you in the future...

From MS Access Help : "By default, the column headings are sorted in alphabetic or numeric order. If you want them to appear in a different order, or if you want to limit which column headings to display, set the query's ColumnHeadings property."
ree0082Author Commented:
The crosstab query is dynamic and the column headings change depending on the date the uesr inputs. The calculations are always performed on Column 2 and 3 regardless of the date entered. You are actually the one who helped me create the crosstab query (Record ID 25790913). I had modified the query so it used Format([DateSubmitted], "yyyy-mm" for the columns and then I have code in the background that converts this to just the month name. The report goes back 1 year. I orignially had it so it would only show the months between dates. The users who will use this report wanted it to always show a years worth of data. I have the user enter in one date for the parameters.

Does that help?

I am not sure how to write the calculations into the query so I get the data I need.
ree0082Author Commented:
Ok, I got it. The tricky part was I didn't know how to reference my columns in my calculations. Thankfully since I was putting the calculations directly into the invisible textbox, I was able to reference the name of the unbound control where I was putting the column2 and column3. I put the following into the unbound invisislbe controls that I made.
=IIf(([Col3]-[Col2])<4 And ([Col3]-[Col2])>0,1,0)
I set the running sum property on each of the invisible controls to Group ALL.
In my report footer I just set the total control boxes equal to my invisible textboxes.

For reference I had used Microsoft KB328320 to make my column's dynamic. Like many others I experienced the problem when printing showing the last record over and over. I corrected this by adding the following.
Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
End Sub

Now, do you have any ideas on how to sort the list by color?
Under sorting and grouping you should be able to add a calculated field that replicates your red, yellow, orange calculations you referenced above and return those values to sort by
ree0082Author Commented:
When I put my calculations in, I get the error "The Microsoft Jet Database engine does not recognize '' as a valid field name or expression." I put the calculations in exactly as I had them above. It looks like it doesn't know how to find my columns in my calculations. Since they change each time the report is run, is there anyway to reference them by position or something like that? A genereic way to reference the column rather then by field name. The calculation is always performed on the second and third columns.

That "Sorting and Grouping" under the view menu was something I didn't even know existed. It will be cool if we can find a way to make it work.
If you just go into the Sorting and Grouping and instead of a formula you just hit the drop down... do you see the list of fields, with Col2 and Col3 listed there?
ree0082Author Commented:
No, I see a list of the fields that are for the current report being ran.
I have a form that allows the user to enter a date for the report. In order for me to work on the report, I have to have this form open otherwise it prompts me for the parameters constantly.
ree0082Author Commented:
I was not able to find a way to sort this. Since my original question got answered, closing question and awarding points.
ree0082Author Commented:
Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.