469,343 Members | 5,478 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

Percentage of negative values out to the total number of fields


I have a query which has fields in showing how close a client is to their renewal date, or how overdue they are. I wanted to have a totals column at the bottom of the query showing what percentage of the total records had negative values in (therefore where the client is overdue) but the totals query option on tell me how many records there are in the query. How to I create a total row at the end of my query to give me the percentage of negative values in a certain field?

Please help!

Feb 22 '12 #1
5 1819
12,516 Expert Mod 8TB
You don't do that kind of stuff in a query. You do it in a report.
Feb 22 '12 #2
Thank you Rabbit. Would you be able to give me some help on how I would do that in a report? I am basically teaching myself to use Access and it is not always easy to do because examples never fully apply to my database!

Any help you could give me would be very appreciated!

Kind regards

Feb 23 '12 #3
12,516 Expert Mod 8TB
Examples rarely apply fully because they are examples. They are meant to impart the general concepts that can then be used to solve a specific problem. In this case, you would probably use the DCount function to count the number of negative values and divide that by the total record count. Something like
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "tableName", "someValue < 0") / Count(*)
Feb 23 '12 #4
Thank you Rabbit - I will try to figure it out
Feb 23 '12 #5
32,183 Expert Mod 16PB
Assuming your field (NOT any related control) is called [Overdue], and that field has a negative value when something is overdue, you could try a control in your footer section with the following as its ControlSource :
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf([Overdue]<0,1,0))/Count([Overdue])
NB. This doesn't handle the situation where no records are shown. That would cause a Division by Zero error. If that needs to be handled then make sure to do so.
Feb 24 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dirk Hagemann | last post: by
2 posts views Thread by Chris Leffer | last post: by
1 post views Thread by Badis | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.