Percentage of negative values out to the total number of fields

[JessSmith]

Hi

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!

Jess

[Rabbit]

You don't do that kind of stuff in a query. You do it in a report.

[JessSmith]

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

Jess

[Rabbit]

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

=DCount("*", "tableName", "someValue < 0") / Count(*)

[JessSmith]

Thank you Rabbit - I will try to figure it out

[NeoPa]

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 :

=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.