Percent of total queried records from total database records

[msuguy71]

Hello, I have searched through the messages here and have tried various things. What I need is the percentage of licenses expiring in 30 days from the total number of database records. I am able to get the total number of expiring (and expired) licenses by doing a query and setting the criteria <date()+30. Then in the report footer I have a textbox named "TotalQueried" that reads =Count([Lastname]) as the record source. This is attached to data showed in the detail section that I grouped together by lastname (show header/footer). This gives the correct number of total epiring (or expired) licenses.

My problem is trying to get the next calculation to work. I placed another grouping on StuID (show header/footer) and in the footer I put a textbox named "Totalrecords" and put =Count([CPRQuery]![StuID] as the record source. I then made another textbox in the report footer that has a textbox with =[TotalQueried]/[Totalrecords] as the control source. This gives me a div by zero error. I have tried various different calculations and what not both trying to make a new query "CPRQuery" and using other types of things like Count in the Query instead of GroupBy without success. How do I get this percent thing to work? Thanks.

 

[scriverb]

I think your first problem is the Total Number of Licenses. You are taking a count of the Grouping by Last Name. Now many Smith's do you have in your database. They all will count as 1. You must expand this counter to a unique identifier for each person in your database. Something like Last name, First name, DOB combination. If you group on a combination of the above fields you will have a unique individual and can then count the records.

Is you [TotalRecords] control accurately counting the StuID field. Are you getting a value there? If the error is keeping you from seeing this value then remove the control that is causing the divide by zero. Now run it an verify that the [TotalRecords] control is giving you a valid count.

If it is then put back your control in the footer and put the following in the ControlSource property:

=[TotalQueried]/IIF(Count([CPRQuery]![StuID]=0,1,Count([CPRQuery]![StuID])

A Grouping section control may not use a calculated control (i.e. [TotalRecords] ) in an expression. Just restate it and also check for zero. If zero then use 1 as the divisor. I don't know if your data should really ever have zero records because if there is a detail record there should always be a value of 1 in the control [TotalRecords]. So, make sure that the control [TotalRecords] is working.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[msuguy71]

Ok, I tried your suggestion, and I keep getting a question box asking for CPRQuery!StuID. I am not sure why I keep getting this request as I have a this fieldname under the CPRQuery query. This is why I am getting a div by 0 error as I tried a textbox with =Count([CPRQuery!StuID]) and it is not showing any records but when I run the CRPQuery query I get all 7 records. Can I not pull another query under this section? I have the percentage calculation under the report header so it is not between the group header/footer. I guess I still do not understand, although I do understand what and how to use the IIf function. For some reason, my report is not seeing anything that the all records query (CPRQuery) is doing.

 

[scriverb]

Make a new database with just the report, queries used in the report and empty tables to my email address(see my profile) and let me see what you are doing. I will fix this up and send it back and then post an explanation.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]