Report grouping help

[rickmason]

Hi all,
I have a DB that has a report that currently counts the number of times a user accesses a certain area of our core system. The report looks like the example below:

Jones 13
Smith 26
Taylor 5
Hawks 1110 (I get these by the following control source of a text file in the report : =Count([tblEMR]![User Name])

I now need a report that will give me any user who has accessed the system 10 or less times in the time frame I select (Monthly) and a report that will show users who have accessed the system more than 1000 times in the defined timeframe (Normally I import a file that I get this data from once a month) I know this is probably simple, can someone help me out? Thanks in advance. Rick

 

[Remou]

Perhaps you could base a report on a query? For example:
[tt]SELECT tblEMR.UserName, Count(tblEMR.UserName) AS CountOfUserName, IIf([CountOfUserName]<=10,"A",IIf([CountOfUserName]>=1000,"B","C")) AS GroupBy
FROM tblEMR
GROUP BY tblEMR.UserName[/tt]

PS There is an Access Reports Forum:
Microsoft: Access Reports Forum
forum703

 

[rickmason]

Thanks Remou, I am a novice could you explain how and where I would begin to set this up? Is there a way to have the user select which report they wanted? 1 = All records
2 = Less than 5 hits
3 = Greater than 1000 hits Something like this?

Sorry for posting in the wrong forum.

 

[Remou]

That might be easier. First you will need to create a query (SQL view):

[tt]SELECT tblEMR.UserName, Count(tblEMR.UserName) AS AccessCount
FROM tblEMR
GROUP BY tblEMR.UserName
HAVING (((Count(tblEMR.UserName))>=[Please Enter Number: ]));[/tt]

You will then need to create a report based on this query. You could make it a little neater by using a form to get the number, in which case [Please Enter Number] would read Forms!frmForm!txtNumber, or such like.