Calulating Percentage problem...

I have got a small problem with the following and was wondering if anyone has got any ideas....

In a report I have textboxes showing: wether it is 'outstanding' or 'resolved', Count, Percentage and Type.
For example:
Status Count Percentage Type
Outstanding 94 22.54% type1
Resolved 305 73.14% type1
Resolved 8 1.92% type2
Resolved 10 2.40% type3
Total Count: 417

This all works fine. What I am having problems with is that I need to have another two textboxes, which separates and calulates the total percentage for 'resolved' and for 'outstanding'.

The resolved types are also further divided into subtypes. i.e. type1_blue, type1_yellow, type1_red etc (which makes up the count for each type). These also needs to be calulated into percentage of its appropriate type. I.e. type1_blue = 22% of type1.

Any suggestions of how to get this sorted????.......

From a simplistic standpoint, the two text boxes you wanted would simply be the following

Outstanding you already have Outstanding/Total Count = Percentage
Resolved would be (Total Count - Outstanding)/ Total Count = Percentage

As far as the type_blue ,type_yellow etc

type1_blue count/Resolved Type1 total count = Percentage
type1_yellow count/Resolved Type1 total count=Percentage and so forth.

Now I realize that you already know this part, so I guess you are looking at how to group and sort within a report. Not knowing what tables or queries are involved and how the data was collected originally to get what you have already shown above, it looks like you would need to group on each Resolved Type and set it as grouped together and then place your corresponding type_colors in the detail section below each group for the Resolved Types. I don't like this explaination very well, so if you can provide a little better detail in how you have your current report design set up i.e. the groups and detail sections and how the data was collected for each of the fields I can give a better explaination of how to set up the group, sort and count calculations and the related percentages properly.

Otherwise just add additional text boxes that will use the DCount and DLookup methods to get the specific information from your tables and place them on your report in the exact area and placement you want and then use that information to perform your calculations with.

lwells

Thanks for trying to solve my problem. I realise it is not easy for you to try and help without further details so I’ll try and explain myself a little better and together with the attached images it might provide a clearer picture for you what I am trying to do….



The record source for the report is a query called ‘16-3 Top 5 Channels Helpful data’.

The query ‘16-3 Top 5 Channels Helpful data’ retrieves data from the query ‘16-0 Top 5 Channels Helpful data’and from a table called ‘16-0_Top5_this_month’. (see attached file).

The query‘16-0Top 5 Channels Helpful data’ retrieves data from two tables; ‘Data_table’and ‘Dealer_Codes’. (see attached file)



Please also find attached images of the report output and the design of the report.



Hope this clarifies it a little, as I would still very much appreciate help with getting this sorted!



Thanks in advance!

/Kattis

Excellent Will go to work on it this evening and post a reply later on. Should be fairly simple to get it all worked out.

lwells

Hi Kat,

Okay I have had a chance to go over your design and have a better understanding of what you are trying to accomplish. Before we begin with getting the text boxes on your report to calculate what you wanted, lets take a look at the queries you built.

First, I am not sure what the query 16-0 Top 5 Channels Helpful data is suppose to do. It is a select query from Data_Table Inner Joined with the table Dealer_Codes on DealerCode. The results of this query are records where you have records in the Data_Table that match the Dealer_Codes. From looking at the results of this query, the only additional piece of information that was added was the DealerName which isn't used in your report anyway. The DealerName for your report is already taken from the table 16-0_Top5_this_month. So unless you are using this query somewhere else in your application, I would just get rid of it, because the data that your report needs is already in the Data_Table to begin with. And running two queries to get the results that you want just slows down the speed of your application....and we all know that users don't like to wait for anything...including if it is your boss.

Okay now the second query is just fine, although the table 16-0_Top5_this_month looks like it was from a make-table query. This is okay because it looks like it could be used elsewhere, however if you are only making this table, just for this report, then we probably can eliminate this table as well and use the main query to extract the top 5 dealers from.

Okay, now to your report. This also looks like you have something else in the recordset of the report. I noticed it in the text box =Sum([SumofNumber of records queried 1) Not sure where the 1 comes from. Do you have another duplicate table or query running in the recordset of the report? Generally it means you have the same table or query used twice for some reason. Not a problem if you are, but in the mockup .mdb of your design that I created, it wasn't necessary.

Anyway, back to your original questions. If I understand the first item;

"I need to have another two textboxes, which separates and calulates the total percentage for 'resolved' and for 'outstanding'"

In your report footer add two text boxes and name them whatever you like and is useful. Set the format to percentage. In the Control Source for the first one here is the formula

=Sum(IIf([Resolved/Outstanding]="Resolved",[SumOfNumber of records queried]))/Sum([SumOfNumber of records queried])

This will be the Resolved percentage. Note: I don't have the 1 after the queried, so you may need to add the 1 in your application.

For the Control Source of your second text box:

=Sum(IIf([Resolved/Outstanding]="Outstanding",[SumOfNumber of records queried]))/Sum([SumOfNumber of records queried])

This will be for the Outstanding percentage.

For the next item;

"The resolved types are also further divided into subtypes. i.e. type1_blue, type1_yellow, type1_red etc (which makes up the count for each type). These also needs to be calulated into percentage of its appropriate type. I.e. type1_blue = 22% of type1."

In the detail section of your report you will add three text boxes, one for the blue, one for the red and one for the green. Space the text boxes that count blue, red and green far enough apart so you can add these three new boxes along side. Set the format to percentage for each of them.

Here is the formula for these:

=[SumOfBlue]/Sum([SumOfNumber of records queried])
=[SumOfRed]/Sum([SumOfNumber of records queried])
=[SumOfGreen]/Sum([SumOfNumber of records queried])

Again you will probably have to add the 1 behind queried in your application.

Good luck and let me know if this was what you needed.

lwells

Hi Iwells!

HUGE thank you, for taking your time to sort my problem out! It all works great!! I only had to make a minor change to the last formulas: =[Blue]/Sum([SumOfNumber of records queried]) etc, to get the desired output.

/Kat

You are welcome. I did forget that you had used an expression for the SumofColor in your query, but you found it okay.

lwells

In the report footer I'm trying to add up the total % resolved and the total % outstanding.
For some reason it does not add up the correct percentage.

Any suggestions?...

Sure Kat,

Let me find the database when I get home, and will give you the calculations. I will post later this evening unless someone answers you first.

lwells

Thanks Iwells! You're a little diamond!

Just a quick question. You said sum of percentage...or do you mean to count the total number that were resolved and not resolved? I don't think you want to total the percentages do you? If you just wanted to get the count...change the code that was written to get the percentage to something like this:

=Count(IIf([Resolved/Outstanding]="Resolved",[SumOfNumber of records queried]))

Haven't actually tested this, so if it doesn't work, I will get the correct computation for you when I get home.
lwells

Yes, I'm trying to add upp the resolved and the outstanding percentages so that it will state the total % of both in the report footer. E.g. A total of 75% outstanding and 25% resolved (for the whole report). Does that make sense?...

Kat,

Did this formula posted earlier not work?

In your report footer add two text boxes and name them whatever you like and is useful. Set the format to percentage. In the Control Source for the first one here is the formula

=Sum(IIf([Resolved/Outstanding]="Resolved",[SumOfNumber of records queried]))/Sum([SumOfNumber of records queried])

This will be the Resolved percentage. Note: I don't have the 1 after the queried, so you may need to add the 1 in your application.

For the Control Source of your second text box:

=Sum(IIf([Resolved/Outstanding]="Outstanding",[SumOfNumber of records queried]))/Sum([SumOfNumber of records queried])

This will be for the Outstanding percentage.

lwells

Hi Iwells,

That is the exact formula that I have in the text boxes in the report footer, but for some reason it's not calucating correctly. (e.g. it's telling me it's 50% outstanding and 50% resolved when the actual figures are 54% and 46%). Don't understand why it's not showing the correct figures......

Hi Kat,

I don't have your database mockup here at work, so when I get home I will look at why it isn't calculating correctly. Doing a quick run through on some other databases here at work, seemed to function correctly with that formula, so let me see whats going on with your database when I get home. I will post back later this evening.
lwells

Kat,

I looked through the mockup and I don't see what is happening at your end. I changed the totals around and the percentages were always correct. So it is probably something really simple, but I can't seem to put my finger on it just looking at the mockup. Is it possible you can send me a copy of the database....you can strip out all the data if you need to for security, I will enter my own data to test with. But I would like to see where the error is at for both our benefits. If you want you can just send the report and I can import it into my database to see what is going on. Send it to this email
lwells1433@aol.com

Will wait until I see your report.
lwells