Report HELP

[rmason]

Hi All,
I have a report that I created that shows the number of employees who have accessed our Meditech EMR module. An example of what the report looks like is: Tom Jones 125
Mickey Mouse 10034
George Bush 10
Bill Clinton 2

I created a text box with the following code attached to get the above totals: =Count([tblEMR]![User Name])

Here is where I am stuck. The user would like to be able to select all employees who have accessed the EMR more than 1000 times or less than 10 times. Can someone help with the criterial to be able to accomplish this? Currently the report will promt the user with a beginning and ending date (Report gets run monthly) I will still need this report to stay intact but I need the help with creating another report that I can select <10 or > 1000 or whatever number they may be interested in(??) Please break it down to a novice level when advising as I keep making attempts to this but keep hitting brick wall..............................Thanks in advance!!!!

 

[dheich4321]

Do you know what the underlying query is for the report ? If you do, copy it and make it a totaling query. Then copy the existing report and have the new report point to the new query.

 

[rmason]

Thanks, Can you take a look at the attached sample report? This shows how I am totaling the number of times each user access the EMR. I know need to show who has accessed the system < = 10 times or >= 1000 times. Thanks

 

[dheich4321]

Do you know what the underlying query is ? It looks like you are doing all the totaling on the report. You probably need to have a totaling query that sums the number of times a user accesses the data and then in that same query have criteria that says <=10 or >=1000.

On the report if you right click in the black box in the top left of the report in design mode and click properties you should be able to find the source for the report.

 

[rmason]

I tried adding the < 10 to the current criteria. The good news, I was able to tell who fell in the catagory. The bad news, They would show as a -1 as opposed to evryone is did not meet the requirements showed as 0 (??)
Tom Jones 0
BoB Smith 0
Steve Smith -1
ETC
The same applied when I added the criteria for the >= 1000 scnerio. It looks like what I need to figure out now is how to surpress the printing of enyone who now has a 0 after these criteria are met. Thanks for your help AND patience.

 

[dheich4321]

I don't understand what you mean by they show as a -1 or 0. That is a value that is used for a true/false or yes/no field.

 

[rmason]

Report Help

I know, It makes no sense to me either(??) Take a look at the attached report criteria and the report it produces. I hope this helps. Rick

 

[dheich4321]

I see what is going on now. You are doing the counting and summary on the report, not in a query. Basically, what you are seeing based on your calculation of =count([tblEMR].[Username])>=1000 is a true/false statement, so it is returning a 0 if true and a -1 of false.

I would look at the underlying query and do a count of the username in the query, then in the criteria of the query, put your >1000 or <10. That way only the records that qualify will be passed to the report.

Unless you sort the report by the true/false (I've never seen that done) I don't know of any way to be selective about what records appear on a report. It is usually done in a query.

 

[rmason]

Thanks, I will try that. Sorry about the confusion. Rick